MOVING SYSTEM DATABASE TO A DIFFERENT LOCATION IN SQL SERVER 2008 R2


Scenario:

A couple of days back, I was examining the configuration of one of our production DB servers. This server was involved in Merge Replication. All the database files on this server were dumped on one SAN drive of a subsequently small size. The replication cleanup job for some reasons was not able to delete the historical records from the system tables present in the distribution database due to which all of a sudden the drive which contained the data files as well as the log files got filled up hardly leaving any space for the SQL Server to start. After a detailed examination of the system, I came up with the following recommendations:

  1. Allocate a separate drive to hold the distribution.mdf file.
  2. Allocate a separate drive to hold the distribution.ldf file.
  3. Allocate a separate drive to hold the tempdb database. Since tempdb is a very high write intensive database therefore RAID 10 is the best option.
  4. Allocate a separate drive to hold the system databases master,model and msdb.

Let’s first discuss in detail on the approach to move the system database in detail. For demonstration purpose, I am using SQL Server 2008 R2 and the instance name is INTEL/abc.

Moving the tempdb database

At present, my tempdb is present under the following path F:\TempDB and I want to move it’s mdf and ldf file to the paths named E:\Microsoft SQL Server\MSQL\Data and E:\Microsoft SQL Server\MSQL\Log respectively.

Connect to the SQL Server Management Studio, open a new query window and select the tempb database, please refer the screen capture below:

Execute the below T-SQL query against the tempdb database to get the logical file name of the data and log files of the tempdb database.

Select * from sysfiles

Please refer the screen capture below:

As seen from the screen capture above, the logical name of the data file of the tempdb database is tempdev whereas the logical name of the log file of the tempdb database is templog.

Execute the below set of T-SQL statements against the tempdb database.

ALTER DATABASE tempdb

MODIFY FILE ( NAME =’tempdev’,

FILENAME = ‘E:\Microsoft SQL Server\MSQL\Data\tempdev.mdf’)

ALTER DATABASE tempdb

MODIFY FILE ( NAME =’templog’,

FILENAME = ‘E:\Microsoft SQL Server\MSQL\Log\templog.ldf’)

In the first query, tempdev is the logical name of the data file of tempdb database and E:\Microsoft SQL Server\MSQL\Data is the new path to hold its data file.

In the first query, templog is the logical name of the log file of tempdb database and E:\Microsoft SQL Server\MSQL\log is the new path to hold its log file.

Please refer the screen capture below:

Re-start the SQL Server for changes to take place successfully. I always have a habit of doing this using command prompt; please refer the screen capture below.

  1. Stop the SQL Server Services.

On the command prompt, type the following command

NET STOP MSSQLSERVER

After executing the command, the SQL Server Services is stopped.

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

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, 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. GoTo Start, Select All Programs, Select Microsoft SQL Server, Select Configuration Tools, Select 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, please refer the screen capture below:

  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 as shown in the screen capture below:

 

  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 path:

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

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

  1. In the startup parameters, just 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:

  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.

That’s the part 1 of the article. In the next series, we will discuss how to move model, msdb and distribution database. I hope your’l have enjoyed it, any suggestions are most welcome.

Advertisements

4 Responses

  1. Hi, I’ve read your article, and I want to ask you if is convinient that master database be located in a SAN. I have two SQL Servers 2008, one for production and another for replication, For replication we are going to use SRDF, this replication copy the information from one device in SAN to another in the other server, but I don’t know if I need to move master database to SAN for replication and the disadvantages of doing this, because I had have bad experiencies with SAN disconection with user databases and I don’t know the convinience of move master database. thanks

  2. Great article! Is there a step/procedure which system database to move first? I did a test on my local. I first moved the tempdb and all OK. Next I move the model. I added the parameters on the MSSQLServer service then restarted. Next I executed detach – OK. if I attach again there is an error because the service MSSQLServer service has stopped. I did follow the steps on one of the articles I’ve searched on the web. Thanks!

  3. Simply wish to say your article is as surprising.

    The clearness in your post is just cool and i could
    assume you are an expert on this subject. Well with your permission allow me to grab your RSS feed to keep updated with forthcoming
    post. Thanks a million and please keep up the gratifying work.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: