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:

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.

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

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

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

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.

Restart the instance of SQL Server.

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

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.

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.

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

Open SQL Server Configuration Manager.
In the SQL Server Services node, right-click the instance of SQL Server (for example, ) and choose Properties.
Open Startup Parameters dialog.

In the SQL Server (instance_name) Properties dialog box, click the Startup Parameters tab.

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

Stop the instance of SQL Server.

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

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.

Restart the instance of SQL Server.

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

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

On the Start menu, point to All Programs | Microsoft SQL Server 2008 R2 | Configuration Tools | SQL Server Configuration Manager.
In the SQL Server Services node, right-click the instance of SQL Server (for example, ) and choose Properties.
In the SQL Server (instance_name) Properties dialog box, click the Advanced tab.

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.

-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

Stop the instance of SQL Server.

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

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.

Restart the instance of SQL Server.

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

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.

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

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

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

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

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.

SQLSkills blogspot

Search This Blog

GUNNU

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

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

Introduction

Moving the msdb and model Database

Moving the master Database

SQL Server 2012

SQL Server 2008 R2

Moving the tempdb Database

Moving the Resource Database

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

Translate

My Blog List

Search This Blog

GUNNU

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

Introduction

Moving the msdb and model Database

Moving the master Database

SQL Server 2012

SQL Server 2008 R2

Moving the tempdb Database

Moving the Resource Database

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

Translate

My Blog List

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

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