How to move the system databases

Step 1) Open Notepad,copy the below script there and save the file as "MoveSql2005SysDbs.cmd".

Script:

@echo off
REM /////////////////////////////////////////////////////////////////////
REM // MoveSql2005SysDbs.cmd - Execute commands to move system databases
REM /////////////////////////////////////////////////////////////////////
if "%2"=="" goto syntax

set InstName=%1
set NewPath=%2
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 /////////////////////////////////////////////////////////////////////
REM // Ensure we can find our SQL script file
REM /////////////////////////////////////////////////////////////////////
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 /////////////////////////////////////////////////////////////////////
REM // Check viability of parameters before any changes happen...
REM /////////////////////////////////////////////////////////////////////
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
)

REM /////////////////////////////////////////////////////////////////////
REM // Now start modifying the system database locations
REM /////////////////////////////////////////////////////////////////////
sqlcmd -E -S%SQLName% -Q"ALTER DATABASE model MODIFY FILE (NAME = 'modeldev', FILENAME = '%NewPath%\model.mdf')"
sqlcmd -E -S%SQLName% -Q"ALTER DATABASE model MODIFY FILE (NAME = 'modellog', FILENAME = '%NewPath%\modellog.ldf')"
sqlcmd -E -S%SQLName% -Q"ALTER DATABASE msdb MODIFY FILE (NAME = 'MSDBData', FILENAME = '%NewPath%\MSDBData.mdf')"
sqlcmd -E -S%SQLName% -Q"ALTER DATABASE msdb MODIFY FILE (NAME = 'MSDBLog', FILENAME = '%NewPath%\MSDBLog.ldf')"
sqlcmd -E -S%SQLName% -Q"ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', FILENAME = '%NewPath%\tempdb.mdf')"
sqlcmd -E -S%SQLName% -Q"ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', FILENAME = '%NewPath%\templog.ldf')"

REM /////////////////////////////////////////////////////////////////////
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.
REM /////////////////////////////////////////////////////////////////////
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 %NewPath%
move %OldPath%\MSDBData.mdf %NewPath%
move %OldPath%\MSDBLog.ldf %NewPath%
move %OldPath%\tempdb.mdf %NewPath%
move %OldPath%\templog.ldf %NewPath%
move %OldPath%\master.mdf %NewPath%
move %OldPath%\mastlog.ldf %NewPath%

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;"
exit 0

:syntax
echo MoveSql2005SysDbs - Moves sql system databases to a different directory
echo MoveSql2005SysDbs [InstanceName] [NewPath]
echo InstanceName should be MSSQLSERVER if using the default instance
echo NewPath should contain a full path but no trailing backslash

:end



Step 2) Open notepad, copy the sql script below and save it as "MoveSql2005SysDbs.sql".
Script:

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'
select @OldPath = substring(@Arg, 3, charindex('\master.mdf', @Arg) - 3)
ELSE IF left(@Arg,2) = '-l'
select @OldPath = substring(@Arg, 3, charindex('\mastlog.ldf', @Arg) - 3)

IF left(@Arg,2) IN ('-d','-l')
BEGIN
select @Arg = replace(@Arg, @OldPath, '$(NewPath)')
exec master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE', @key, @ArgName, 'REG_SZ', @Arg
END
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) + ';'


Step 3) Open notepad, copy the script below. Change the location where you want to move the databases to and save the file as "MoveSql2005SysDbs__run.bat".

Script:

MoveSql2005SysDbs.cmd MSSQLSERVER d:\MSSQL\SystemDB

Step 4) Save all 3 files into same folders.

Step 5) Double click the bat file and once it finish the processing please verify that files are moved to specified location.

In case files are not moved the you need to run the bat file from command prompt.