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.
|
-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:
|
-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 MSSQLSERVER |
Step 6: Move the master.mdf and mastlog.ldf files to the new location.
|
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 SQLSERVERAGENT |
Step 8: Verify the file change for the
master database by running the following query.
|
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:
|
<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.