Moving the System Databases

This document applies to moving the following databases in SQL Server 2005:

Model
MSDB
Master
Tempdb

Moving these databases requires downtime and must not be done while there are connections to the SQL Server

Move the Model and MSDB Databases

To move the model database, you must start SQL Server together with the -c option, the –m option, and trace flag 3608. Trace flag 3608 prevents SQL Server from recovering any database except the master database.

Note You will not be able to access any user databases after you do this. You must not perform any operations, other than the following steps, while you use this trace flag. To add the startup parameters follow the steps below:

1. Use SQL Server Configuration Manager to change the startup parameters of the SQL Server service

a. Open SQL Server Configuration manager
b. Right Click the SQL Server Service and select properties

clip_image002

c. Select the Advanced Tab

The Startup parameters variable should look something like this:

-dC:\SYSTEMDBMove\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\LOG\ERRORLOG;-lC:\SYSTEMDBMove\mastlog.ldf

clip_image002[4]

Edit the Startup parameters row by adding the following to the end of the variable:

;-c;-m;–T3608
(the first semi-colon is to close off the previous startup parameter – no spaces)

Or run via command prompt…

Net Stop MSSQL$SQL1

Net Start MSSQL$SQL1 /c /m /T3608

Startup Parameter

Details

-c

Shortens startup time when starting SQL Server from the command prompt. Typically, the SQL Server Database Engine starts as a service by calling the Service Control Manager. Because the SQL Server Database Engine does not start as a service when starting from the command prompt, use -c to skip this step.

-m

Starts an instance of SQL Server in single-user mode. When you start an instance of SQL Server in single-user mode, only a single user can connect, and the CHECKPOINT process is not started.

T trace#

Indicates that an instance of SQL Server should be started with a specified trace flag (trace#) in effect. Trace flags are used to start the server with nonstandard behavior

3608

Prevents SQL Server from automatically starting and recovering any database except the master database. Databases will be started and recovered when accessed.

2. Stop and Restart the SQL Server instance. Ensure that the SQL Server Agent is not running

3. Detach the Model database

use master
go
sp_detach_db ‘model’
go

4. Move the Model.mdf and Modellog.ldf files from the <Current Location> folder to the <New Location> folder.

5. Reattach the Model database using the new location details

use master
go
sp_attach_db
‘model’,<New Location>\model.mdf’,<New Location>\modellog.ldf’
go

6. Detach the MSDB Database

use master
go
sp_detach_db
‘msdb’
go

7. Move the Msdbdata.mdf and Msdblog.ldf files from the <Current Location> folder to the <New Location> folder.

8. Remove -c -m -T3608 from the startup parameters box by using the SQL Server Configuration manager

9. Stop and Restart SQL Server

10. Reattach the MSDB database using the new location details

use master
go
sp_attach_db
‘msdb’,<New Location>\msdbdata.mdf’,<New Location>\msdblog.ldf’
go

Check that databases have successfully moved to the new location

use model
go
sp_helpfile
go

use msdb
go
sp_helpfile
go

Move the Master and Resource Databases

The Resource database depends on the location of the master database. The Resource data and log files must reside together and must be in the same location as the master data file (master.mdf). Therefore, if you move the master database, you must also move theResource database to the same location as the master data file. Do not put the Resource database in either compressed or encrypted NTFS file system folders. Doing so will hinder performance and prevent upgrades.

1. Open The SQL Server Configuration Manager and go to the startup parameters variable for the SQL Server Service

2. Edit the Startup Parameters values to point to the planned location for the master database data and log files, and click OK. Moving the error log file is optional.
The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter. The following example shows the parameter values for the default location of the master data and log files.

-dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf; 
-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG; 
-lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf

If the planned relocation for the master data and log files is E:\SQLData, the parameter values would be changed as follows:

-dE:\SQLData\master.mdf; -eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG; -lE:\SQLData\mastlog.ldf

3. Stop the instance of SQL Server by right-clicking the instance name and choosing Stop

4. Move the master.mdf and mastlog.ldf physical files to the new location.

5. Start the instance of SQL Server in master-only recovery mode by entering one of the following commands at the command prompt. The parameters specified in these commands are case sensitive. The commands fail when the parameters are not specified as shown.

  • For the default (MSSQLSERVER) instance, run the following command.
NET START MSSQLSERVER /f /T3608
  • For a named instance, run the following command.
NET START MSSQL$instancename /f /T3608

6. Using SQL Server Management Studio, run the following statements. Change the FILENAME path to match the new location of the master data file. Do not change the name of the database or the file names.

ALTER DATABASE mssqlsystemresource
   MODIFY FILE (NAME=data, FILENAME= ‘new_path_of_master\mssqlsystemresource.mdf’);
GO

ALTER DATABASE mssqlsystemresource
   MODIFY FILE (NAME=log, FILENAME= ‘new_path_of_master\mssqlsystemresource.ldf’);
GO

7. Move the mssqlsystemresource.mdf and mssqlsystemresource.ldf files to the new location.

8. Set the Resource database to read-only by running the following statement.

ALTER DATABASE mssqlsystemresource SET READ_ONLY;

9. Stop and Restart the instance of SQL Server

10. Verify the file change for the master database by running the following query. The Resource database metadata cannot be viewed by using the system catalog views or system tables.

SELECT name, physical_name AS CurrentLocation, state_desc

Move the TempDB Database

Note: Because tempdb is re-created each time the instance of SQL Server is started, you do not have to physically move the data and log files. The files are created in the new location when the service is restarted in step 3. Until the service is restarted, tempdb continues to use the data and log files in existing location.

1.      Determine the logical file names of the tempdb database and their current location on the disk. 


SELECT
name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N‘tempdb’);
GO

2.       Change the location of each file by using ALTER DATABASE.

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = ‘E:\SQLData\tempdb.mdf’);
GO

ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = ‘F:\SQLLog\templog.ldf’);
GO

3.       Stop and restart the instance of SQL Server. 

4.       Verify the file change.

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N‘tempdb’);
GO

5.       Delete the tempdb.mdf and templog.ldf files from the original location.


Useful Links

Main KB
Article – http://support.microsoft.com/kb/224071

Move the
Master and Resource
databases – http://msdn.microsoft.com/en-us/library/ms345408(SQL.90).aspx

Startup
parameters – http://msdn.microsoft.com/en-us/library/ms190737.aspx

Advertisements

One Response to Moving the System Databases

  1. Pingback: Moving system databases in SQL Server 2008… « Mohsin's DBA Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: