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

Moving MSDB

By Steve Jones,

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


Return to Steve Jones Home

 
Total article views: 14102 | Views in the last 30 days: 7
 
Related Articles
FORUM

detach

detach

FORUM

Detach a remote database from a server

detaching databases remotely

BLOG

SQL Server – How to Detach a Database

To move a database from one location to other you fist need to detach the database from server. In t...

BLOG

Detaching corrupt databases

Question – Can you detach a corrupt database? Answer – IT DEPENDS! More specifically it depends on t...

ARTICLE

Detaching and Attaching a Database

Detaching and attaching a database is an advanced trick that can be useful in anything from transpor...

Tags
 
Contribute