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




Array

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 |