Moving the SQL 2005 System Databases

  • 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!

  • 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

  • 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.

  • 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

  • Neteng,

    Take a look at this msdn article although the script can be used it might be easier for you this way.

    As always make sure you have a recent backup.

    Regards,

    Mark

    P.S Nothing to worry about it's very straightforward.

  • 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.

  • 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!

  • 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.

  • 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.

  • 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

  • What a great time saver! Thank you.

  • Thanks, Kevin Hards and t berry. Knowing I've helped you guys feels good.

    Vince

  • 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?

  • 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!

  • 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