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 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.
[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
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:
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/
