MOVING SYSTEM DATABASES

Below document provides information that can be used for moving system database. However this is not recommended for database installation its ideal for troubleshooting & disaster recovery purposes alone.

A: Moving System Database Files (Model, msdb, Tempdb) 

Step 1: Run the following in a new query window

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

ALTER DATABASE tempdb

               MODIFY FILE ( NAME = 'tempdev' , FILENAME = 'F:\MSSQL\Data\tempdb.ldf' );

Go

ALTER DATABASE tempdb

               MODIFY FILE ( NAME = 'templog' , FILENAME = 'F:\MSSQL\Data\templog.ldf' );

Go

Step 2: Stop the SQL server instance. Now move the files to the location specified

Step 3: Restart the SQL server instance

B: Moving master database 

Step 1:    On the Start menu, point to All Programs | Microsoft SQL Server 2008 R2 | Configuration Tools | SQL Server Configuration Manager.

Step 2 :  In the SQL Server Services node, right-click the instance of SQL Server (for example, ) and choose Properties.

Step 3 : In the SQL Server (instance_name) Properties dialog box, click the Advanced tab.
Step 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

Step 5: Stop the instance of SQL Server.

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

Step 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.

Step 7: Restart the instance of SQL Server.

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

Step 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

C: Moving Resource Database 

Starting SQL server 2005 Resource Database was instroduced as a read-only database that contained all the system objects that are included within SQL Server. It does not contain user data or user meta data.

Ideally it is found in the below directory for default installation of SQL server on C:\ drive
C:\Program Files (x86)\Microsoft SQL Server\MSSQL10.<instance name>\MSSQL\Binn

The filenames would be mssqlsystemresource.mdf and mssqlsystemresource.ldf respectively.
In SQL Server 2008 and higher, the Resource database resides under the following directory and cannot be changed:

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

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 will have its own resource database mdf and ldf files.

Msdb, tempdb, model & master are moved. Now we shall move the resource database. 
For this we will need to know a few trace flags

The resource database is used by the engine to collect information of system objects during an upgrade.

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.

Option	Impact
Trace Flag 3607	Starts SQL Server without recovering any databases. Skips automatic recovery (at startup) for all databases.
Trace Flag 3608	Starts SQL Server, recovering master only. Skips automatic recovery (at startup) for all databases except the master database.
Trace Flag 3609	Skips the creation of the tempdb database at startup. Use this trace flag if the tempdb database is problematic or problems exist in the model database.
User Database Status -32768	Starts SQL Server without recovering the user database

When SQL server starts it goes through the recovery phase for each of its database. Reads the error logs and perform the redo and undo actions based on the LSN. First the master database is opened and recovered. Next the model database. Depending on model the tempdb database is created and then the rest of the databases are started and recovered.

 

Hence when we are moving the resource database we will make use of trace flag 3608.I have also used trace flag 3607 however it’s not mandate to do the same.Put the SQL server in single user mode & connect with the sysadmin account with the startup options -c, -m

Step 1:

Command: SQLCMD –U <username> -P <password> -c –m –t3607 –t3608.

Step 2:

Then we issue the below command to move resource database

USE master

GO

ALTER DATABASE  mssqlsystemresource

MODIFY FILE  (NAME = data, FILENAME = N' F:\MSSQL\Log\mssqlsystemresource.mdf' );

GO

ALTER DATABASE  mssqlsystemresource

MODIFY FILE  (NAME = log, FILENAME  = N' F:\MSSQL\Data\ mssqlsystemresource.ldf' );

GO

Step 3: Stop the SQL server instance. Now move the files to the location specified

Step 4: Restart the SQL server instance.

D: The database information mapping between SQL Server 2000 and 2005

ID	Information	SQL Server 2000	SQL Server 2005
1	Database system table\view - Source for all databases on the SQL Server to include the name, owner, creation date, etc.	SELECT * FROM master.dbo.sysdatabasesGO	SELECT * FROM sys.databases;GO
2	Database files system table\view - Source for the currently connected database's file names, size, location, type (database or log), etc.	SELECT * FROM dbo.sysfilesGO	SELECT * FROM sys.database_files;GO
3	Database files system table\view - Source for all database's file related information	SELECT * FROM master.dbo.sysaltfilesGO	SELECT * FROM sys.master_files;GO
4	IO statistics on database files - Returns the usage statistics on a per file basis	-- Single database file SELECT *FROM :: fn_virtualfilestats(1, 1)GO	-- All database files SELECT *FROM sys.dm_io_virtual_file_stats(NULL, NULL);GO
5	Database meta data - Returns the pertinent database name, size and remarks	EXEC master.dbo.sp_databases GO	EXEC master.dbo.sp_databases; GO
6	Database meta data - Fairly complete set of pertinent database information that can return data for all databases or 1 database	-- All databases EXEC master.dbo.sp_helpdbGO -- Single databaseEXEC master.dbo.sp_helpdb 'Northwind'GO	-- All databases EXEC master.dbo.sp_helpdb;GO EXEC master.dbo.sp_helpdb 'AdventureWorks'; GO -- Single database
7	Change database ownership - System stored procedure to change the database owner	EXEC sp_changedbowner sa GO	EXEC sp_changedbowner sa; GO
8	Database ID to name translation - System function that will provide the database name when passed the database ID from the database system table	-- Returns the master database SELECT DB_NAME(1)GO	-- Returns the master database SELECT DB_NAME(1);GO
9	Database name to ID translation - System function that will provide the database ID when passed the database name from the database system table	-- Returns 1 SELECT DB_ID('master')GO	-- Returns 1 SELECT DB_ID('master');GO
10	Database status - System function that will return the value for 1 of ~25 database specific values	SELECT DATABASEPROPERTYEX('master', 'Status') GO	SELECT DATABASEPROPERTYEX('master', 'Status'); GO

References were taken from below sources

https://msdn.microsoft.com/en-us/library/ms345408(v=sql.105).aspx
http://www.madeiradata.com/moving-system-databases/
http://www.sqlservercentral.com/articles/Administration/movingsystemdatabasesachecklist/1608/