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.

 

 

http://3.bp.blogspot.com/-LTA8_rA0OQw/VgiTbTHoD4I/AAAAAAAAAVE/CmYuUyWxWSk/s640/errorlog.jpg

Hence when we are moving the resource database we will make use of trace flag 3608.I have also used trace flag 3607 however its 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 = dataFILENAME = 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.