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.

  1. 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:

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

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

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

  1. 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:

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

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

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

  1. Verify that the changes have taken place successfully by executing the below query against the master database:
    select * from sysaltfiles where dbid=1

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

Pages: 1 2




Related Articles :

  • No Related Articles Found

One Response to “Moving SQL Server System Databases”

  1. Very well documented. Thanks for sharing.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |