Techie Pie

On several occasions I have been asked to move SQL 2005 system databases at some point in the server’s lifecycle. Moving master, msdb, model and tempdb isn’t difficult, but it does require some attention to detail (and server downtime).

Fortunately, the SQL 2005 Books Online include instructions on how to move system databases in SQL 2005. What you might want to know, however, is that when following these instructions there is an error under the section titled Moving the Master and Resource Databases. Specifically, some details are missing at lines 14-16. If you follow these instructions to the letter (like I do), you need make the following changes (the bolded lines are my additions):

14. MODIFY FILE (NAME=log, FILENAME-’new_path_of_master\mssqlsystemresource.ldf’) ;

GO

14a. Stop SQL

15. Move the mssqlsystemresource.mdf and mssqlsystemresource.ldf files to the new location.

15a. Restart SQL in single user mode

16. Set the Resource database to read-only by running the following statement:

ALTER DATABASE mssqlsystemresource SET READ_ONLY ;

The change is necessary because in order to move mssqlsystemresource.mdf and mssqlsystemresource.ldf the SQL services need be stopped, but in order to set Resource to read only, the service needs to be in single user mode.

It’s a simple modification that keeps following the Microsoft instructions clean and error-free.