Moving System Databases with SQL 2008 R2

Issue

Sometimes after installation, the SQL System databases need to be moved to a different location. Use the steps below to accomplish this task. The steps in this article apply to SQL 2008 R2. See the link under the sources column for information on other versions of SQL.

Resolution

Step 1 - alter the database location in SQL (see sample move script below)

Step 2 - stop the SQL service

Step 3 -move the physical files to their new location

MAKE SURE THE FOLDER AT THE NEW LOCATION HAS THE SAME PERMISSIONS

Step 4 -update the location of the MASTER database MDF and LDF files in the Registry (See Registry File below)

Step 4 - restart the SQL service

Step 5 - verify the file change by running the Check File Location script below

Step 6 - If DBMail is already setup and MSDB database is moved, verify that Service Broker is enabled by running the Check Service Broker script below

 

Sample Move Script

alter database MASTER MODIFY FILE ( NAME = master, FILENAME = 'F:\SQLData\SystemDB\master.mdf' );
alter database MASTER MODIFY FILE ( NAME = mastlog, FILENAME = 'F:\SQLData\SystemDB\mastlog.ldf' );
alter database TEMPDB MODIFY FILE ( NAME = tempdev, FILENAME = 'F:\SQLData\SystemDB\tempdb.mdf' );
alter database TEMPDB MODIFY FILE ( NAME = templog, FILENAME = 'F:\SQLData\SystemDB\templog.ldf' );
alter database MODEL MODIFY FILE ( NAME = modeldev, FILENAME = 'F:\SQLData\SystemDB\model.mdf' );
alter database MODEL MODIFY FILE ( NAME = modellog, FILENAME = 'F:\SQLData\SystemDB\modellog.ldf' );
alter database MSDB MODIFY FILE ( NAME = MSDBData, FILENAME = 'F:\SQLData\SystemDB\MSDBData.mdf' );
alter database MSDB MODIFY FILE ( NAME = MSDBLog, FILENAME = 'F:\SQLData\SystemDB\MSDBLog.ldf' );

Check File Location Script

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'<database_name>');

 

Check Service Broker Status Script

SELECT is_broker_enabled
FROM sys.databases
WHERE name = N'msdb';

 

Before and After Registry Settings for SQL 2008 R2

Before Registry File

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer\Parameters]

"SQLArg0"="-dC:\\Program Files\\Microsoft SQL Server\\MSSQL10_50.MSSQLSERVER\\MSSQL\\DATA\\master.mdf"

"SQLArg1"="-eC:\\Program Files\\Microsoft SQL Server\\MSSQL10_50.MSSQLSERVER\\MSSQL\\Log\\ERRORLOG"

"SQLArg2"="-lC:\\Program Files\\Microsoft SQL Server\\MSSQL10_50.MSSQLSERVER\\MSSQL\\DATA\\mastlog.ldf"

After Registry File

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer\Parameters]

"SQLArg0"="-dF:\SQLData\SystemDB\master.mdf"

"SQLArg1"="-eC:\\Program Files\\Microsoft SQL Server\\MSSQL10_50.MSSQLSERVER\\MSSQL\\Log\\ERRORLOG"

"SQLArg2"="-lF:\SQLData\SystemDB\mastlog.ldf"

 

Sources

This article applies to SQL 2008 R2. For more details or other versions and for troubleshooting information, visit

http://msdn.microsoft.com/en-us/library/ms345408(v=sql.105).aspx