SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Move Your Master

By Steve Jones,

Move Your Master

Introduction

Do you ever need to move your master database? Should you?
You might. Especially if you have standards in your company and you find a server that doesn't conform. It can happen, even to me, an experienced DBA. I've been known to not pay complete attention to an install and accidentally put things in the wrong place.

Like master.

Recently I installed a server and wasn't paying attention. Our standard is to place the master data files on the m: drive and the master log on the l: drive. However, I clicked too fast and installed the server on the c: drive. Not wanting to uninstall, I decided to move the master database.

This turns out to be simpler than one would think. The first step is to connect to your SQL Server using Enterprise Manager and select the "Properties" item after right-clicking on the server. You should see something like this:



Once you do this, you should see:



Select the "Startup Parameters" option. This will bring you to a dialog that contains all the startup parameters that are used by SQL Server when the service starts. If you are more daring, these parameters will also be stored in the registry under Local Machine\Software\Microsoft\MSSQLServer\MSSQLServer\Parameters. However, most of you will work with this dialog:



From here, there are usually 3 parameters that you can work with. These parameters are described below:

  • -d - The path and name to the master database file.
  • -e - The path to the error log file and it's name. Archived files are the base name + a number.
  • -l - The path and name to the master database transaction log.

Unfortunately you cannot edit these parameters. So you must remove and then add back the parameters. I'd suggest writing them down or getting a screen shot before beginning.

To move my master database, I first selected the "-d" parameter and clicked "remove". I then typed this in the edit box and clicked "add"

-d m:\MSSQL\data\master.MD

I repeated this for the log file, using this entry.

-l l:\MSSQL\log\mast log.MD

Once this is complete, hit OK and close this dialog and then the server properties dialog. Now you're ready to move the files.

First shut down the SQL Server. I usually right click the server in Enterprise Manager and select "stop". Next open explorer and physically copy the files to the new location.
That's it.

Now start the server and you should be good to go. If you have any issues, they are probably due to incorrectly typing the path and filenames. Double check these. Also check the Event Log (application) for clues if you experience issues.

Read Part II - Moving MSDB

Conclusions

Not a complicated process, but one that I've seen questions posted. In future articles I'll look at how to move MSDB, model, and temp.

Steve Jones
©dkRanch.net November 2002


Return to Steve Jones Home

 
Total article views: 9991 | Views in the last 30 days: 4
 
Related Articles
BLOG

How to restore Master database – SQL Server System Database?

Steps to restore SQL Server Master Database :- 1) Stop SQL Server Services 2) Add [-m] startup p...

FORUM

How to restore master database.?

How to restore master database.?

ARTICLE

Using Database Master Keys in SQL Server

Learn the basics of Database Master Keys and how you can use them in SQL Server.

FORUM

move Master database from the 2000 to 2005 SQL server

Move Master database from the 2000 to 2005 SQL server

FORUM

Restore Master Database

Master Database

Tags
administration    
configuring    
sql server 7    
 
Contribute