July 2, 2008 at 4:09 am
I just used the script on my freshly installed SQL Server 2005 Express edition on Vista.
It did not like being in a folder with spaces, as soon as I moved it to a non-space folder, it worked perfectly well.
Thanks!
July 2, 2008 at 6:43 am
Michael.varriale (6/25/2008)
Hey Mark, Any update on your script to install 8 instances of SQL 2005 and sp2? What about the maintenance plans?Vince great scripts
Hi Michael,
Well we have now developed a bespoke C# installer based around Vince's script. The installer installs & configures SQL according to the environment the servers being put into (development, Pre-Production & production) and then installs SP2 and another SQL hotfix.
As for the maintenance plans we eventually decided it was easier to write bespoke t-sql to create the maintenance jobs for each instance, rather than copy jobs between instances using SSIS. It's dramatically reduced the time it takes to create the plans!
Cheers,
Mark
July 21, 2008 at 12:12 pm
All,
I just made a new post. But any help from you guys would be appreciated:
I am fairly new to SQL Server 2005 Administration and don't feel comfortable running the script. I am running low on disk space on my C: drive where all the system databases recide. Can you please help me with the script so I may move the files to an alternate drive? What changes do i need to make?
All my system database files (primary data file as well as log files) are located under the default location:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
I need to move the data files under D:\program Files\Microsoft SQL Server\Data folder and the log files under D:\program files\Microsoft SQL Server\Logs folder.
All the databases that I created already locate on a separate drive (on the SAN).
Directory of C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
07/21/2008 01:37 PM .
07/21/2008 01:37 PM ..
04/24/2008 10:44 AM 517 AACDB15D-DE28-45AC-ACE8-0A004AA399D7.cer
05/20/2008 03:31 PM 517 CEFF3CF4-C74A-43CF-897F-1064D228A6A0.cer
02/10/2007 12:49 AM 2,883,584 distmdl.ldf
02/10/2007 12:49 AM 5,242,880 distmdl.mdf
07/21/2008 01:37 PM 0 list.txt
07/17/2008 09:04 PM 4,194,304 master$4IDR
07/19/2008 09:46 AM 4,194,304 master.mdf
05/20/2008 03:46 PM 786,432 mastlog$4IDR
05/20/2008 03:46 PM 786,432 mastlog.ldf
07/17/2008 09:04 PM 1,245,184 model$4IDR
07/19/2008 09:46 AM 1,245,184 model.mdf
07/17/2008 02:00 AM 3,538,944 modellog$4IDR
07/21/2008 01:09 PM 3,932,160 modellog.ldf
07/19/2008 10:00 PM 9,043,968 msdbdata.mdf
05/20/2008 03:46 PM 786,432 msdblog.ldf
02/10/2007 12:39 AM 524,288 mssqlsystemresource.ldf
02/10/2007 12:39 AM 40,173,568 mssqlsystemresource.mdf
07/19/2008 09:46 AM 3,342,336 ReportServer.mdf
07/19/2008 09:46 AM 2,293,760 ReportServerTempDB.mdf
05/20/2008 03:46 PM 786,432 ReportServerTempDB_log.LDF
05/20/2008 03:46 PM 786,432 ReportServer_log.LDF
07/20/2008 12:02 AM 831,062,016 tempdb.mdf
07/20/2008 12:03 AM 36,569,088 templog.ldf
23 File(s) 953,418,762 bytes
2 Dir(s) 13,688,832 bytes free
I am not very confident in the process of making this chance. Any help would be appreciated.
Thanks all.
July 21, 2008 at 12:50 pm
I'd like to say you can run the script with no worries, but I can't. It usually works just fine, but if it doesn't work you might not be able to start your server.
Can you test it on a test setup first? Take a good backup before you begin.
Vince
July 22, 2008 at 2:14 am
July 25, 2008 at 1:10 pm
I am not sure , I did anything wrong or here.
I have 5 instances on the box and we have problem with one drive where all the system and user databases, and I am planning to use your script to move the system databases and I am getting the following problem. Could you please guide , what was the issue.
New data and log path are same, hence i am not giving 3rd parameter.
E:\EDWP>MoveSql2005SysDbs "EDWP" "M:\EDWP"
E:\EDWP>{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\froman\fcharset0
Times New Roman;}{\f1\fswiss\fcharset0 Arial;}}
The system cannot find the path specified.
E:\EDWP>{\*\generator Msftedit 5.41.15.1507;}\viewkind4\uc1\pard\sb100\sa100\f0fs24 [font=Courier New]@echo off\line rem MoveSql2005SysDbs.cmd - Execute comman
ds to move system databases\line if ""M:\EDWP""=="" goto syntax\line\line set In
stName="EDWP"\line set NewPath="M:\EDWP"\line if ! equ ! (\line set NewLogPath="
M:\EDWP"\line ) else (\line set NewLogPath=\line )\line\line set ScriptPath="E:EDWP\"\line\line if /I equ MSSQLSERVER (\line set ServiceName=MSSQLSERVER\line
set AgentServiceName=SQLSERVERAGENT\line set SQLName=.\line ) else (\line set Se
rviceName=MSSQL$ \line set AgentServiceName=SQLAgent$\line set SQLName=.\\\line
)\line\line rem Ensure we can find our SQL script file\line if not exist MoveSql
2005SysDbs.sql (\line echo This command MUST be run from the SQL Server and the
script file MoveSql2005SysDbs.sql \line echo must be in the same directory as th
e MoveSql2005SysDbs.cmd file.\line exit 1\line )\line\line rem Check viability o
f parameters before any changes happen...\line sqlcmd -E -S -Q"print 'Instance n
ame $(InstName) verified.'"\line if errorlevel 1 (\line echo Sql Server not fou
nd, please check instance name parameter.\line exit 1\line )\line\line dir if
errorlevel 1 (\line echo New Path not found, please check path parameter.\line
exit 1\line )\line\line dir if errorlevel 1 (\line echo New Log Path not foun
d, please check logpath parameter.\line exit 1\line )\line\line rem Now start mo
difying the system database locations\line sqlcmd -E -S -Q"ALTER DATABASE model
MODIFY FILE (NAME = 'modeldev', FILENAME = '\\model.mdf')"\line if errorlevel 1
goto AlterErr\line sqlcmd -E -S -Q"ALTER DATABASE model MODIFY FILE (NAME = 'mod
ellog', FILENAME = '\\modellog.ldf')"\line if errorlevel 1 goto AlterErr\line sq
lcmd -E -S -Q"ALTER DATABASE msdb MODIFY FILE (NAME = 'MSDBData', FILENAME = '\MSDBData.mdf')"\line if errorlevel 1 goto AlterErr\line sqlcmd -E -S -Q"ALTER DA
TABASE msdb MODIFY FILE (NAME = 'MSDBLog', FILENAME = '\\MSDBLog.ldf')"\line if
errorlevel 1 goto AlterErr\line sqlcmd -E -S -Q"ALTER DATABASE tempdb MODIFY FIL
E (NAME = 'tempdev', FILENAME = '\\tempdb.mdf')"\line if errorlevel 1 goto Alter
Err\line sqlcmd -E -S -Q"ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', FI
LENAME = '\\templog.ldf')"\line if errorlevel 1 goto AlterErr\line\line rem This
script file finds the correct spot in the registry for the startup parameters,
replaces the path, and returns\line rem the old path name back to the calling sc
ript. \line for /f "delims=;" %s in ('sqlcmd -E -S -h-1 -iMoveSql2005SysDbs.sql'
) do set OldPath="%s"\line\line echo Shutting down SQL Server. Answer Y if promp
ted to shut down dependent services.\line net stop \line net stop \line\line ech
o Moving files...\line move \\model.mdf \line move \\modellog.ldf \line move \\M
SDBData.mdf \line move \\MSDBLog.ldf \line move \\tempdb.mdf \line move \\templo
g.ldf \line move \\master.mdf \line move \\mastlog.ldf \line\line echo Restartin
g service with /f and trace flag 3608 \line net start /f /T3608\line\line sqlcm
d -E -S -Q"ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME = 'data', FILENA
ME = '\\mssqlsystemresource.mdf')"\line sqlcmd -E -S -Q"ALTER DATABASE mssqlsyst
emresource MODIFY FILE (NAME = 'log', FILENAME = '\\mssqlsystemresource.ldf')"\l
ine\line move \\mssqlsystemresource.mdf \line move \\mssqlsystemresource.ldf \li
ne\line sqlcmd -E -S -Q"ALTER DATABASE mssqlsystemresource SET READ_ONLY"\line\l
ine net stop \line\line echo Restarting service in normal mode\line net start
\line net start \line\line echo Verifying new location of system databases...\li
ne sqlcmd -E -S -Q"SELECT name, physical_name AS CurrentLocation, state_desc FRO
M sys.master_files ORDER BY database_id;"\line goto end\line\line :AlterErr\line
echo Error in ALTER DATABASE statement, files not moved.\line set errorlevel=2line goto end\line\line :syntax\line echo MoveSql2005SysDbs - Moves sql system d
atabases to a different directory\line echo MoveSql2005SysDbs [InstanceName] [Ne
wPath][NewLogPath]\line echo InstanceName should be MSSQLSERVER if using the def
ault instance\line echo NewPath should contain a full path but no trailing backs
lash\line echo NewLogPath defaults to NewPath if not present\line\line :end\line
\line [font=Courier New][/font][/font]\line\pard\f1\fs20\par 1>nul\line
The system cannot find the path specified.
E:\EDWP>}
'}' is not recognized as an internal or external command,
operable program or batch file.
November 27, 2008 at 9:06 am
Used the script on a fresh 2005 install - did just what it said on the box
Very useful script for me as I'm about to install 20 instances. This will save a lot of time.
Thanks Bud!
February 4, 2009 at 6:11 pm
thank you, Vince!
==============
I used the "new version" embedded in the article even though I am planning on keeping the system log files in the same folder to be on the safe side.
It did not work initially on a brand new installation and I could not see the output as the DOS window was closing (yes, I started a RUN->CMD manually so it should not have ...) so I do not know what stopped it.
I eventually used START /Wait and not only it displayed the output in a separate cmd window, but also worked successfully.
Meanwhile, I also stopped manually Reporting Services, Integration Services, Analysis Services and FullTextSearch service ...
So:
START /WAIT MoveSql2005SysDbs.CMD MSSQLSERVER, D:\MSSQL.1\MSSQL\Data, D:\NewLOGfolderLocation
Thanks to guys like you sharing their expertise, we can all move ahead. It is hugely appreciated.
February 5, 2009 at 7:24 am
I believe your stopping the other services manually is what made the difference. I should follow through by adding a check to stop all those services to the batch file.
If I had time I'd love to make a Powershell version as I'm learning that...
Ol'SureHand (2/4/2009)
thank you, Vince!==============
I used the "new version" embedded in the article even though I am planning on keeping the system log files in the same folder to be on the safe side.
It did not work initially on a brand new installation and I could not see the output as the DOS window was closing (yes, I started a RUN->CMD manually so it should not have ...) so I do not know what stopped it.
I eventually used START /Wait and not only it displayed the output in a separate cmd window, but also worked successfully.
Meanwhile, I also stopped manually Reporting Services, Integration Services, Analysis Services and FullTextSearch service ...
So:
START /WAIT MoveSql2005SysDbs.CMD MSSQLSERVER, D:\MSSQL.1\MSSQL\Data, D:\NewLOGfolderLocation
Thanks to guys like you sharing their expertise, we can all move ahead. It is hugely appreciated.
March 17, 2009 at 4:16 am
Fantastic bit of code. I was looking at the MS SQl Server instruction and thinking this was going to take a while and then I found your script.
Cheers
April 23, 2009 at 7:58 am
What a great time saver! Thank you.
April 23, 2009 at 8:46 am
Thanks, Kevin Hards and t berry. Knowing I've helped you guys feels good.
Vince
May 4, 2009 at 9:11 am
I've seen other people ask this question, but didn't see an answer. Has anyone applied service packs or CU's since moving the system data files to another file location?
May 4, 2009 at 10:56 pm
steve.coleman (5/4/2009)
I've seen other people ask this question, but didn't see an answer. Has anyone applied service packs or CU's since moving the system data files to another file location?
Yes, no worries.
Once moved successfully - the script works a treat once all related SQL Services are stopped - they stay put!
July 2, 2009 at 2:03 pm
This mostly worked though I had to read through many of the comments to get there.
1. The .cmd file cannot be run from a directory with spaces, place the movedb dir at the root of the C:\ to be sure.
2. Be sure to Stop any associated services before running or it will fail, Full Text Indexing, Reporting Services, Analytic Services, Integration Services, etc.
3. There is no sample for the .cmd syntax, it is:
c:\movedb\MoveSql2005SysDbs.cmd SQLINSTANCENAME NEWFILEPATH
replace SQLINSTANCENAME with your SQL Server Instance name and NEWFILEPATH with the path to where you want the files to be sent to, for example mine was:
MoveSql2005SysDbs.cmd DEV_SERVER1 W:\SQLSYS_FILES
4. It failed to move the mssqlsystemresource.mdf and mssqlsystemresource.log for some reason. I do not know why since the command prompt window closes at any error in the script. It DID make the change in the registry though since my error in the event log indicated it could not find the mssqlsystemresource.mdf in the new location. Manually moving the files to the new location worked great. No idea why the script didn't/couldn't.
So, other than that, worked fine, even having to read many of the comments to get working this still saved me quite a bit of time. The script itself took all of about 1 minute to run.
Thanks!
Viewing 15 posts - 46 through 60 (of 75 total)
You must be logged in to reply to this topic. Login to reply