Moving SQL Server System Databases

2. Start
the SQL Server Services. On the command prompt, type the following
command

NET START MSSQLSERVER

Once done, check whether the tempdb data and log files are present at the newly
allocated location:

Execute the below T-SQL query against the tempdb database to
double check whether the operation has been successfully performed.

select * from sysfiles

From the above screen capture, we can conclude that we have
successfully moved the tempdb to the newly allocated location successfully.

Moving the master database

At present, the master database is
present under the following path D:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data and I want to move its mdf and ldf
file to the paths named E:\Microsoft SQL Server\MSQL\Data and E:\Microsoft
SQL Server\MSQL\Log respectively.

1. Go to Start >All Programs > Microsoft
   SQL Server, >  Configuration Tools > SQL Server
   Configuration Manager.

2. In the SQL Server
   Services node, right-click the instance of SQL Server, in our case
   the name of the SQL
   Server instance is MSSQLSERVER and choose Properties:

3. Click on the Advanced Tab as shown in the screen
   capture below:

4. Select the text in the Startup parameters and copy it into
   a text file:

5. As shown in the above screen capture, at present the data
   and log file of the master database resides under the following path:
   D:\Program Files\Microsoft SQL
   Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data
   And we want it to reside under the following new paths:

   Data File:E:\Microsoft SQL
   Server\MSQL\Data
   Log File:E:\Microsoft SQL
   Server\MSQL\Log

6. In the startup parameters, change the location of the
   data and log file as per the new path as shown in the screen capture
   below:

7. Select Apply and then Select OK.
8. Stop the SQL Server instance by Right Clicking on it and
   Selecting Stop.

9. Move the data and log file of the master database to the
   new location, please refer the screen capture below:

10. Restart the SQL Server instance by right clicking on it
    and Select Start, please refer the screen capture below.

11. Verify that the changes have taken place successfully by
    executing the below query against the master database:

    select * from sysaltfiles where dbid=1

12. From the above screen capture, we can conclude that the
    data and log file of the master database have moved properly to the
    respective new location.

In the next article I will discuss how to move the model, msdb and distribution
databases. Please let me know if you have any comments or suggestions.

]]>