Moving the SQL 2005 System Databases

  • Okay having just run SP2 again on a clean SQL 2005 installation with the mdf's & ldf's moved to their respective homes.  When SP2 was upgrading the 1st named instance I saw several new files get created in the 'original' data folder.

    These were distmdl.mdf & .ldf (I delete these as part of your script as they are not needed) & mssqlsystemresource1.ldf further into the upgrade process the file gets renamed to mssqlsystemresource.ldf.  Shortly after this I see the hotfix.exe error box appear.

    Does this mean I just need to change in the script when the service pack runs and move the resource db after the sp upgrade?  But surely I will then have the same problem when another sp get's released?

    help...

  • Mark,

    PM me at vince.iacoboni location db.com (replace location with the symbol that is above 2 on the keyboard ). I will include my latest script that checks and sets the registry for SQL 2005 instances. It might help you see if you have registry entries pointing to the original file locations that SP2 is reading.

    Vince

  • Thanks for the reply Vince.  I have sent you a pm hopefully this will help pinpoint the problem.

    Have you successfully used your script to split the locations of the system db's & mssqlsystemresource.mdf & .ldf files and then tried to install SP2?  Was everything ok?

    Thanks again for all your help.

  • I should point something out that has caused me grief.

    Doing a test run on my desktop worked like a charm, but when it came time to run it on our production box, I discovered that x64 machines (ha ha!) have TWO copies of CMD.exe - one that recognises the 'NET' command and one that doesn't.  As a result, the script half executed and gave me a major problem.

    Eventually I got everything back up, but am now faced with:

    1) I cannot "ALTER DATABASE mssqlsystemresource" because that database "does not exist"; and

    2) my master and mssqlsystemresource files are in different directories (which should not be the case.)

    So, at the moment everything "works", but I'm just dreading the issues further down the track.

    I would like some advise on how to fix the above two problems, if anyone can help.

    Thanks,

    S.

  • This is a issue that has been submitted to Microsoft connect.

    The documentation states the resource database and the master database must be in the same folder.

    What it does not say is the resource database log file must be in the master database folder.

    So if you put log files on one disk and database files on another disk the install will fail unless you put a copy of the resource database log where the master database data file is. After it is updated you can just copy it back to the log file location and all is well.

  • Great article. This is a tedious chore and any help is appreciated.

    Two minor items.

    1. A commented sample in the code showing how to run the procedure. I noticed someone used quotes.

    2. The ability to put log files and data files on different paths. Some of us separate the log and data files and put them on different drives.

     

     

  • Hi vince,

    I think this is a great script. I wish I had seen it when is was first posted 🙂

    I wonder if you have updated or modified it in the past year? One suggestion I have is to provide for a separate T-Log file path. I guess the only requirement would be to add an additional command line parameter and a new path variable.

    I don't do a lot of admin but I do a lot of build/deploy/install and these techniques will be very useful to me.

    Ray

  • Great suggestions. If I get time to make the update I'll try to let you know. Work's picked up so not sure if/when that will happen...

    Vince

  • Hi Ray, as you can see it is much requested. If I get the time I'll let post an update...

    Vince

  • Interesting article, Vince. I was thinking that if one did not have a test/development machine available, you could always look at installing a test instance on one of your main boxes and working from there, assuming higher ups would let you get away with it. 😛

    I definitely appreciate your talking about that system database. We will be bringing up 2K5 in the near future, we're exclusively a 2000 shop right now, and I've never heard of that database before. I think you've just saved me a world of grief!

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

  • Here is something else about that system database. It has to be located in the same folder as the Master database.

    We like to have the database and log files on different drives.

    When we tried to install a SP for the first time we could not start the server. It had tried to update the resource database log file on the same drive the master database data file was. We just copied the log over to the other drive and things ran fine.

  • Hi Vince,

    Wow, nice script. I recently had a hosting company setup a VPS with SQL Server 2005. In there email to me they strongly recommend putting the data files and log files on a separate drive than the C drive, however, they didn't themselves setup the system databases this way. Very frustrating. Anyway, I am looking to use your wonderful script and would like to second the request for the ability to specify a separate location for the ldf files. I thank you so much, in advance for anything you can do.

    Take care,

    Tom

  • OK, you guys got your wish. Please note that this is untested. I hope someone will test it and post the results! [/i]

    Here is the MoveSql2005SysDbs.CMD file:

    *****************************************

    [font=Courier New]@echo off

    rem MoveSql2005SysDbs.cmd - Execute commands to move system databases

    if "%2"=="" goto syntax

    set InstName=%1

    set NewPath=%2

    if !%3 equ ! (

    set NewLogPath=%2

    ) else (

    set NewLogPath=%3

    )

    set ScriptPath="%~dp0"

    if /I %InstName% equ MSSQLSERVER (

    set ServiceName=MSSQLSERVER

    set AgentServiceName=SQLSERVERAGENT

    set SQLName=.

    ) else (

    set ServiceName=MSSQL$%InstName%

    set AgentServiceName=SQLAgent$%InstName%

    set SQLName=.\%InstName%

    )

    rem Ensure we can find our SQL script file

    if not exist %ScriptPath%MoveSql2005SysDbs.sql (

    echo This command MUST be run from the SQL Server and the script file MoveSql2005SysDbs.sql

    echo must be in the same directory as the MoveSql2005SysDbs.cmd file.

    exit 1

    )

    rem Check viability of parameters before any changes happen...

    sqlcmd -E -S%SQLName% -Q"print 'Instance name $(InstName) verified.'"

    if errorlevel 1 (

    echo Sql Server %SQLName% not found, please check instance name parameter.

    exit 1

    )

    dir %NewPath% > nul

    if errorlevel 1 (

    echo New Path %NewPath% not found, please check path parameter.

    exit 1

    )

    dir %NewLogPath% > nul

    if errorlevel 1 (

    echo New Log Path %NewLogPath% not found, please check logpath parameter.

    exit 1

    )

    rem Now start modifying the system database locations

    sqlcmd -E -S%SQLName% -Q"ALTER DATABASE model MODIFY FILE (NAME = 'modeldev', FILENAME = '%NewPath%\model.mdf')"

    if errorlevel 1 goto AlterErr

    sqlcmd -E -S%SQLName% -Q"ALTER DATABASE model MODIFY FILE (NAME = 'modellog', FILENAME = '%NewLogPath%\modellog.ldf')"

    if errorlevel 1 goto AlterErr

    sqlcmd -E -S%SQLName% -Q"ALTER DATABASE msdb MODIFY FILE (NAME = 'MSDBData', FILENAME = '%NewPath%\MSDBData.mdf')"

    if errorlevel 1 goto AlterErr

    sqlcmd -E -S%SQLName% -Q"ALTER DATABASE msdb MODIFY FILE (NAME = 'MSDBLog', FILENAME = '%NewLogPath%\MSDBLog.ldf')"

    if errorlevel 1 goto AlterErr

    sqlcmd -E -S%SQLName% -Q"ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', FILENAME = '%NewPath%\tempdb.mdf')"

    if errorlevel 1 goto AlterErr

    sqlcmd -E -S%SQLName% -Q"ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', FILENAME = '%NewLogPath%\templog.ldf')"

    if errorlevel 1 goto AlterErr

    rem This script file finds the correct spot in the registry for the startup parameters, replaces the path, and returns

    rem the old path name back to the calling script.

    for /f "delims=;" %%s in ('sqlcmd -E -S%SQLName% -h-1 -i%ScriptPath%MoveSql2005SysDbs.sql') do set OldPath="%%s"

    echo Shutting down SQL Server. Answer Y if prompted to shut down dependent services.

    net stop %AgentServiceName%

    net stop %ServiceName%

    echo Moving files...

    move %OldPath%\model.mdf %NewPath%

    move %OldPath%\modellog.ldf %NewLogPath%

    move %OldPath%\MSDBData.mdf %NewPath%

    move %OldPath%\MSDBLog.ldf %NewLogPath%

    move %OldPath%\tempdb.mdf %NewPath%

    move %OldPath%\templog.ldf %NewLogPath%

    move %OldPath%\master.mdf %NewPath%

    move %OldPath%\mastlog.ldf %NewLogPath%

    echo Restarting service with /f and trace flag 3608

    net start %ServiceName% /f /T3608

    sqlcmd -E -S%SQLName% -Q"ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME = 'data', FILENAME = '%NewPath%\mssqlsystemresource.mdf')"

    sqlcmd -E -S%SQLName% -Q"ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME = 'log', FILENAME = '%NewPath%\mssqlsystemresource.ldf')"

    move %OldPath%\mssqlsystemresource.mdf %NewPath%

    move %OldPath%\mssqlsystemresource.ldf %NewPath%

    sqlcmd -E -S%SQLName% -Q"ALTER DATABASE mssqlsystemresource SET READ_ONLY"

    net stop %ServiceName%

    echo Restarting service %ServiceName% in normal mode

    net start %ServiceName%

    net start %AgentServiceName%

    echo Verifying new location of system databases...

    sqlcmd -E -S%SQLName% -Q"SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files ORDER BY database_id;"

    goto end

    :AlterErr

    echo Error in ALTER DATABASE statement, files not moved.

    set errorlevel=2

    goto end

    :syntax

    echo MoveSql2005SysDbs - Moves sql system databases to a different directory

    echo MoveSql2005SysDbs [InstanceName] [NewPath][NewLogPath]

    echo InstanceName should be MSSQLSERVER if using the default instance

    echo NewPath should contain a full path but no trailing backslash

    echo NewLogPath defaults to NewPath if not present

    :end

    [font=Courier New][/font][/font]

    *****************************************

    Here is the MoveSql2005SysDbs.sql file:

    *****************************************

    [font=Courier New]

    SET NOCOUNT ON

    DECLARE @MssqlDotNum varchar(100), @key varchar(255), @ArgName varchar(255), @arg varchar(255), @i int, @OldPath varchar(255)

    EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', '$(InstName)', @MssqlDotNum output

    SELECT @i = 0

    SELECT @key = 'Software\Microsoft\Microsoft SQL Server\' + @MssqlDotNum + '\MSSQLSERVER\Parameters',

    @ArgName = 'SQLArg' + convert(char(1), @i)

    WHILE @i <= 2

    BEGIN

    SELECT @key = 'Software\Microsoft\Microsoft SQL Server\' + @MssqlDotNum + '\MSSQLSERVER\Parameters',

    @ArgName = 'SQLArg' + convert(char(1), @i)

    EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', @key, @ArgName, @arg output

    IF left(@Arg,2) = '-d'

    BEGIN

    select @OldPath = substring(@Arg, 3, charindex('\master.mdf', @arg) - 3)

    select @arg = replace(@Arg, @OldPath, '$(NewPath)')

    END

    ELSE IF left(@Arg,2) = '-l'

    BEGIN

    select @OldPath = substring(@Arg, 3, charindex('\mastlog.ldf', @arg) - 3)

    select @arg = replace(@Arg, @OldPath, '$(NewLogPath)')

    END

    IF left(@Arg,2) IN ('-d','-l')

    exec master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE', @key, @ArgName, 'REG_SZ', @arg

    SELECT @i = @i + 1

    END

    -- Return the path to the calling .CMD file so it knows the old path. ; is the delimiter

    select OldPath = rtrim(@OldPath) + ';'

    [/font]

    *****************************************

    Tom Krueger (9/28/2007)


    Hi Vince,

    Wow, nice script. I recently had a hosting company setup a VPS with SQL Server 2005. In there email to me they strongly recommend putting the data files and log files on a separate drive than the C drive, however, they didn't themselves setup the system databases this way. Very frustrating. Anyway, I am looking to use your wonderful script and would like to second the request for the ability to specify a separate location for the ldf files. I thank you so much, in advance for anything you can do.

    Take care,

    Tom

  • Vince,

    Thanks a lot for the script using the two different file locations. It works well!

    The only thing I noticed is that the "ldf" for the mssqlsystemresource is moved to the same path as the "mdf". In the command file, the "ldf" should probably be set to %NewLogPath% instead of %NewPath%.

    Thanks again! This saved a lot of time.

    Mike

  • Michael,

    Thanks, but I did that with the mssqlsystemresource ldf file intensionally. I believe MS expects both the MDF and LDF files for the Resource database to be in the same directory as the master MDF file. I haven't tested that though. I thought it was better to err on the side of caution by placing it there.

    Did I understand you correctly that you tested out the new version and it worked OK?

    Vince

Viewing 15 posts - 16 through 30 (of 75 total)

You must be logged in to reply to this topic. Login to reply