How to Move the System Databases(2005/2008/2012)

Click4:How to Move User Databases(2005/2008/2012)

Contents

• Introduction
• Moving the msdb and model Database
• Moving the master Database
• Moving the tempdb Database
• Moving the Resource Database

Introduction

Moving a user database in SQL Server is a fairly common and straightforward DBA task. This process typically involves detaching the database from the server, moving the physical database and log files to a new location, and then attaching the database back to the server while specifying the new file locations. Moving one of the system databases, on the other hand, involves a bit more work.

This guide presents the steps necessary to move the system databases using Transact-SQL as part of a planned relocation or scheduled maintenance operation. The steps in this guide have been successfully tested with SQL Server 2008 R2 and SQL Server 2012. For more information on how to move user databases SQL Server, see the following articles:

• How to Move User Databases
• How to Move FILESTREAM-Enabled Databases

Moving the msdb and model Database

The following example moves the msdb and model databases (data and log files) to a new location as part of a planned relocation. Note that this procedure does not apply to moving the master and Resource databases.

1. Determine the logical file names of the msdb and model database and their current physical location on the disk.

   USE master Go SELECT DB_NAME(database_id) AS "Database Name" , name AS "Logical File Name" , physical_name AS "Physical File Location" , state_desc AS "State" FROM sys.master_files WHERE database_id IN (DB_ID(N'msdb'), DB_ID(N'model')) ORDER BY DB_NAME(database_id); Go Database Name Logical File Name Physical File Location State -------------- ------------------ -------------------------------------------------------- -------- model modeldev F:\MSSQL\MSSQL11.MSSQLSERVER\MSSQL\DATA\model.mdf ONLINE model modellog F:\MSSQL\MSSQL11.MSSQLSERVER\MSSQL\DATA\modellog.ldf ONLINE msdb MSDBData F:\MSSQL\MSSQL11.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf ONLINE msdb MSDBLog F:\MSSQL\MSSQL11.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf ONLINE

2. For each file to be moved, run the following statement by providing the logical file name and the new physical location of where the file will be moved to.

   ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' )

   For example, if the planned relocation for the msdb and model data and log files is F:\MSSQL\Data, run the following:

   USE master Go ALTER DATABASE model MODIFY FILE ( NAME = 'modeldev' , FILENAME = 'F:\MSSQL\Data\model.mdf' ); Go ALTER DATABASE model MODIFY FILE ( NAME = 'modellog' , FILENAME = 'F:\MSSQL\Data\modellog.ldf' ); Go ALTER DATABASE msdb MODIFY FILE ( NAME = 'MSDBData' , FILENAME = 'F:\MSSQL\Data\MSDBData.mdf' ); Go ALTER DATABASE msdb MODIFY FILE ( NAME = 'MSDBLog' , FILENAME = 'F:\MSSQL\Data\MSDBLog.ldf' ); Go

3. Stop the instance of SQL Server. Remember that if you are using a Command Prompt to open an elevated Command Prompt using the Run as administrator option.

   [SQLPROD1] C:\> net stop SQLSERVERAGENT [SQLPROD1] C:\> net stop MSSQLSERVER

4. Move the physical files to the new location.

   [SQLPROD1] C:\> move F:\MSSQL\MSSQL11.MSSQLSERVER\MSSQL\DATA\model.mdf F:\MSSQL\Data\ 1 file(s) moved. [SQLPROD1] C:\> move F:\MSSQL\MSSQL11.MSSQLSERVER\MSSQL\DATA\modellog.ldf F:\MSSQL\Data\ 1 file(s) moved. [SQLPROD1] C:\> move F:\MSSQL\MSSQL11.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf F:\MSSQL\Data\ 1 file(s) moved. [SQLPROD1] C:\> move F:\MSSQL\MSSQL11.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf F:\MSSQL\Data\ 1 file(s) moved.

5. Restart the instance of SQL Server.

   [SQLPROD1] C:\> net start MSSQLSERVER [SQLPROD1] C:\> net start SQLSERVERAGENT

6. Verify the file change for the msdb and model databases by running the following query.

   USE master Go SELECT DB_NAME(database_id) AS "Database Name" , name AS "Logical File Name" , physical_name AS "Physical File Location" , state_desc AS "State" FROM sys.master_files WHERE database_id IN (DB_ID(N'msdb'), DB_ID(N'model')) ORDER BY DB_NAME(database_id); Go Database Name Logical File Name Physical File Location State -------------- ------------------ ----------------------------- -------- model modeldev F:\MSSQL\Data\model.mdf ONLINE model modellog F:\MSSQL\Data\modellog.ldf ONLINE msdb MSDBData F:\MSSQL\Data\MSDBData.mdf ONLINE msdb MSDBLog F:\MSSQL\Data\MSDBLog.ldf ONLINE

If the msdb database is moved and the instance of SQL Server is configured for Database Mail, complete these additional steps.

1. Verify that Service Broker is enabled for the msdb database by running the following query.

   USE master Go SELECT is_broker_enabled FROM sys.databases WHERE database_id = DB_ID(N'msdb'); Go is_broker_enabled ----------------- 1

   is_broker_enabled will be 1 if Service Broker is enabled for the given database, otherwise it will be 0.
   If the Service Broker is disabled (is_broker_enabled = 0), run the following T-SQL to enable Service Broker for the msdb database:

   USE master Go ALTER DATABASE msdb SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE; Go

   The above ALTER DATABASE command requires an exclusive lock on the database. If there are open connections and the WITH ROLLBACK IMMEDIATE termination clause is not specified, the command will wait (hang) indefinitely until those connections are closed. TheWITH ROLLBACK IMMEDIATE tells the SQL Server that if it can't complete the command right away, then the other pending transactions should be rolled back.

2. Verify that Database Mail is working by sending a test mail.

   USE msdb Go EXEC sp_send_dbmail @profile_name = 'iDevelopmentProfile' , @recipients = 'dba@idevelopment.info' , @subject = 'MSDB Database Moved' , @body = 'This is a test message to verify that Database Mail is still functioning after moving the MSDB system database.' Go Mail queued.

   If the test message fails, you can review the contents of msdb.dbo.sysmail_event_log to troubleshoot the problem.

   SELECT * FROM msdb.dbo.sysmail_event_log; Go

Moving the master Database

The following example moves the master database (data and log files) to a new location as part of a planned relocation.

SQL Server 2012

1. Open SQL Server Configuration Manager.
2. In the SQL Server Services node, right-click the instance of SQL Server (for example, ) and choose Properties.
3. Open Startup Parameters dialog.
   In the SQL Server (instance_name) Properties dialog box, click the Startup Parameters tab.
4. 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\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf -eC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\LOG\ERRORLOG -lC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

   If the planned relocation for the master data and log file is F:\MSSQL\Data, the parameter values would be changed as follows:

   -dF:\MSSQL\Data\master.mdf -eC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\LOG\ERRORLOG -lF:\MSSQL\Data\mastlog.ldf

5. Stop the instance of SQL Server.

   [SQLPROD1] C:\> net stop SQLSERVERAGENT [SQLPROD1] C:\> net stop MSSQLSERVER

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

   [SQLPROD1] C:\> move "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf" F:\MSSQL\Data\ 1 file(s) moved. [SQLPROD1] C:\> move "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.ldf" F:\MSSQL\Data\ 1 file(s) moved.

7. Restart the instance of SQL Server.

   [SQLPROD1] C:\> net start MSSQLSERVER [SQLPROD1] C:\> net start SQLSERVERAGENT

8. Verify the file change for the master database by running the following query.

   USE master Go SELECT name AS "Logical File Name" , physical_name AS "Physical File Location" , state_desc AS "State" FROM sys.master_files WHERE database_id = DB_ID(N'master'); Go Logical File Name Physical File Location State ------------------- --------------------------------- -------- master F:\MSSQL\Data\master.mdf ONLINE mastlog F:\MSSQL\Data\mastlog.ldf ONLINE

SQL Server 2008 R2

1. On the Start menu, point to All Programs | Microsoft SQL Server 2008 R2 | Configuration Tools | SQL Server Configuration Manager.
2. In the SQL Server Services node, right-click the instance of SQL Server (for example, ) and choose Properties.
3. In the SQL Server (instance_name) Properties dialog box, click the Advanced tab.
4. 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\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf; -eC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG\ERRORLOG; -lC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

   If the planned relocation for the master data and log file is F:\MSSQL\Data, the parameter values would be changed as follows:

   -dF:\MSSQL\Data\master.mdf; -eC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG\ERRORLOG; -lF:\MSSQL\Data\mastlog.ldf

5. Stop the instance of SQL Server.

   [SQLPROD1] C:\> net stop SQLSERVERAGENT [SQLPROD1] C:\> net stop MSSQLSERVER

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

   [SQLPROD1] C:\> move "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf" F:\MSSQL\Data\ 1 file(s) moved. [SQLPROD1] C:\> move "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf" F:\MSSQL\Data\ 1 file(s) moved.

7. Restart the instance of SQL Server.

   [SQLPROD1] C:\> net start MSSQLSERVER [SQLPROD1] C:\> net start SQLSERVERAGENT

8. Verify the file change for the master database by running the following query.

   USE master Go SELECT name AS "Logical File Name" , physical_name AS "Physical File Location" , state_desc AS "State" FROM sys.master_files WHERE database_id = DB_ID(N'master'); Go Logical File Name Physical File Location State ------------------- --------------------------------- -------- master F:\MSSQL\Data\master.mdf ONLINE mastlog F:\MSSQL\Data\mastlog.ldf ONLINE

Moving the tempdb Database

The following example moves the tempdb data and log files to a new location as part of a planned relocation.

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 (see below). Until the service is restarted, tempdb continues to use the data and log files in the existing location.

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

   USE master Go SELECT name AS "Logical File Name" , physical_name AS "Physical File Location" FROM sys.master_files WHERE database_id = DB_ID(N'tempdb'); Go Logical File Name Physical File Location ------------------- --------------------------------- tempdev T:\MSSQL\TempDB\Data\tempdb.mdf templog T:\MSSQL\TempDB\Log\templog.ldf

2. Change the location of each file in the system catalog by using the ALTER DATABASE command. The new path will be used the next time the database is started.

   USE master Go ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', FILENAME = 'F:\MSSQL\Data\tempdb.mdf'); Go ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', FILENAME = 'L:\MSSQL\Log\templog.ldf'); Go

3. Stop and restart the instance of SQL Server.

   [SQLPROD1] C:\> net stop SQLSERVERAGENT [SQLPROD1] C:\> net stop MSSQLSERVER [SQLPROD1] C:\> net start MSSQLSERVER [SQLPROD1] C:\> net start SQLSERVERAGENT

4. Verify the new physical location(s).

   USE master Go SELECT name AS "Logical File Name" , physical_name AS "Physical File Location" FROM sys.master_files WHERE database_id = DB_ID(N'tempdb'); Go Logical File Name Physical File Location ------------------- --------------------------------- tempdev F:\MSSQL\Data\tempdb.mdf templog L:\MSSQL\Log\templog.ldf

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

   [SQLPROD1] C:\> del T:\MSSQL\TempDB\Data\tempdb.mdf [SQLPROD1] C:\> del T:\MSSQL\TempDB\Log\templog.ldf

Moving the Resource Database

A new system database was introduced in SQL Server 2005 called the Resource Database. The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user meta data.

The Resource database makes upgrading to a new version of SQL Server an easier and faster procedure. In earlier versions of SQL Server, upgrading required dropping and creating system objects. Because the Resource database file contains all system objects, an upgrade is now accomplished simply by copying the single Resource database file to the local server.

The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf. The location of these files are dependent on which version of SQL Server you are running (as explained in this section). Each instance of SQL Server has one and only one associated mssqlsystemresource.mdf file, and instances do not share this file.

In SQL Server 2008 and higher, the Resource database resides under the following directory and cannot be changed:

SQL Server 2008 <drive>:\Program Files\Microsoft SQL Server\MSSQL10.<instance_name>\MSSQL\Binn\ SQL Server 2008 R2 <drive>:\Program Files\Microsoft SQL Server\MSSQL10_50.<instance_name>\MSSQL\Binn\ SQL Server 2012 <drive>:\Program Files\Microsoft SQL Server\MSSQL11.<instance_name>\MSSQL\Binn\

For example, in SQL Server 2012, the Resource database is located under the directory:

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn   mssqlsystemresource.mdf  mssqlsystemresource.ldf

Although it was possible to move the Resource database in SQL Server 2005, things changed in SQL Server 2008 and higher where the location of the Resource database can no longer be relocated from its default directory. In SQL Server 2005, the Resource database depended on the location of the master database. The Resource data and log files had to reside together and had to be in the same location as the master data file (master.mdf). Therefore, in SQL Server 2005, if you moved the master database, you had to also move the Resource database to the same location as the master data file. In SQL Server 2008 and higher, the location of the Resource database cannot be moved from its default location.

Click4:How to Move User Databases(2005/2008/2012)