Moving MSDB
Introduction
Recently I had to move a bunch of system databases on a server to have it conform to our standards. I last wrote about moving master to another drive and this time decided to write about how one goes about moving two other system databases, msdb and model.
Why would you move the databases? Well, I mentioned that your standards may need these databases on certain drives, as mine do. Or you may want to move them to a larger drive, or a faster drive. Or even, and hopefully it doesn't happen, a drive fails and they are suspect. You restore them from tape, but now you need to tell SQL they are on another drive.
The process turns out to be fairly simple. And it's similar to the process for moving master. Let's walk through this simple procedure. The first step is to open Enterprise Manager and select the server by right clicking it's name. Select the "Properties" option.
At this point, you should see the "General" tab for the server which shows the basic information that describes the server. At this point, you need to select the "Startup Parameters" button.
From here you should get a dialog similar to the following.
In order to move msdb and model, you need to tell SQL Server that you really want to do this. Once you do this, you can follow the normal detach and attach sequence to move the databases. The way you tell SQL Server that you want to move these databases (to prevent accidental mistakes) is to add a trace flag when the server starts up. Specifically trace flag 3608. This is detailed in Q224071. This is accomplished by typing (without quotes) "-T3608" in the edit box at the top of the dialog. After pressing "add" you should see:
For this to take effect, you need to stop and restart SQL Server. Once SQL Server has restarted, you can perform the detach using the GUI, copy the file over, and attach using the GUI. Alternatively, you can use the following T-SQL to detach msdb.
use master go sp_detach_db 'msdb' go
Once the database is detached, copy it (or restore it) to the new location. Once you have verified the .mdf and .ldf files are in the desired location, run this script to attach the database. You may need to change the paths to match your system.
use master go sp_attach_db 'msdb','z:\Mssql\Data\msdbdata.mdf','z:\Mssql\log\msdblog.ldf' go
That's it. You can repeat the same steps for model.
Read Part I - Move Your Master or part III - Moving tempdb
Conclusions
Once again, an easy process. Simple, but something that would require some research. Fortuneately, I've done that for you and hopefully this resource will be handy when you need it. If you have any comments or questions, please feel free to note them below using the "Your Opinion" button.
Steve Jones
©dkRanch.net November 2002