The Multifunctioning DBA

Aug 27 2009   3:30PM GMT

Moving System Databases in MSSQL2005

Colin Smith Colin Smith Profile: Colin Smith

Recently I had to move all system databases for one server to another physical drive. This is pretty simple for all but the Master and SystemRecource databases. For all others you can just do the following and then copy the mdf and ldf files to the new location.

alter database tempdb

Modify File (Name = tempdev, Filename = ‘c:\tempdb\tempdb.mdf’);

go

alter database tempdb

Modify File (Name = templog, Filename = ‘c:\tempdb\templog.ldf’);

go

Of course with tempdb you do not need to move the mdf and ldf files and it will be recreated in the new location when the server restarts.

So for Master it is a bit different. First I would double check the current location of the master files by doing the following.

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(‘master’)

GO

This will give you the current location of the Master Database. Now that we know that lets move it. For master you will have to open up the SQL Server Configuration Manager utility. Highlight the SQL server 2005 Services node on the left and then find your server service on the right. Right click on it and go to Properties. Go to the Advcanced Tab and look for the Startup Options parameter. In here you will see something like the following.

-dC:\tempdb\master.mdf;-eC:\tempdb\LOG\ERRORLOG;-lC:\tempdb\mastlog.ldf

You will notice the -d -e and -l, you must leave all of those switches in the line. They are telling SQL Server the location of the Data, ErrorLog, and Log files. Then modify the path to the path that you would like it to be now. Then click apply and ok to close out of the configuration Manager. Now stop the SQL Server Service and copy the MDF, LDF files and make sure that the folder structure to the ErrorLog file is available as well as check the security and make sure that the account that runs your SQL Server has full access to this new locataion. Once that is all done pull up a command prompt so we can start the SQL Server in Single User mode.

Net Start MSSQL$Instancename /f /T3608 (for named instance)

Net Start MSSQLSERVER /f /T3608 (for default instance)

Once that starts up then do the following to move the systemresource database as well.

ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME= ‘D:\SqlData\mssqlsystemresource.mdf’);
GO
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=log, FILENAME= ‘ D:\SqlData\mssqlsystemresource.ldf’);

Go

now go back to the command prompt and stop the sql server by doing:

Net stop MSSQL$InstanceName (for named instance)

Net Stop MSSQLSERVER (for default instance)

Now mive the systemresource mdf and ldf files to the new location and lets start it up in single user mode again like we did above.

Now do the following:

ALTER DATABASE mssqlsystemresource SET READ_ONLY;

and now stop the service again. Now we are ready to start it up normally and if all went and goes well you should be all set. Not to big of a deal but be very careful when doing this. Make sure you have a backup of Master and make sure that you backup the original startup options just in case you need to go back. Safety first here. You do not want what should be a simple task to be a horrific event.

Thanks and let me know if you have any questions or comments.

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: