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
