Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase «««123

Moving System Databases Expand / Collapse
Posted Tuesday, March 25, 2008 4:05 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 5:40 AM
Points: 3,052, Visits: 3,771
I took a pot-shot at the answer, because all of them looked to be wrong. Needless to say the answer I chose was marked wrong...

It is not necessary to detach msdb in order to move it, but if you go down this route than SQL Agent must be stopped before you try to detach msdb.

If you have detached msdb, remove the trace flags as specified in the 'correct' answer, and restart SQL you will have a fine mess to deal with. I would always leave the -T3608 flag on until msdb is safely attached again. The other flags are simply not needed to get the job done.

The best approach is given in BOL - do a ALTER giving the new locations for the database files, stop SQL, move the files, and restart.

Personally, I always keep mssqlsystemresource files in the same folder as master, and leave them in the vanilla install location. The SP2 apply puts a new version of mssqlsystemresource on to your system, and it only puts it in the vanilla install location. If you have previously moved master or mssqlsystemresource you can have big problems in applying SP2. Because of this, I do not assume that future SPs or CUs will be able to cope with anything other than vanilla locations for these critical files. The last thing I need is for a future upgrade to fail because I have moved master or mssqlsystemresource to different locations so they give a zero % performance improvement but comply with standards for user databases. I also take a copy of master and mssqlsystemresource .mdf and .ldf files, and keep them handy in case I need to restore these DBs. This backup copy should be refreshed after any SP or CU install. IMHO is is a severe design fault that SQL needs a working mssqlsystemresource DB, but we have no way in SQL of taking a backup or applying a restore.

Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2016, 2014, 2012, 2008 R2, 2008 and 2005. 29 Aug 2016: now over 38,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Quote: "When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist." - Archbishop Hélder Câmara
Post #473913
Posted Tuesday, March 25, 2008 9:46 AM



Group: Administrators
Last Login: Yesterday @ 8:26 PM
Points: 34,363, Visits: 18,575
ALTER might be preferred, but if it's not an answer, you don't get to suggest it ;)

The reference given, shows that SQL 2005 requires trace flag 3608. Perhaps that's not the case, but please comment on the reference. I'm not sure I want to put out an answer I can't reference. If it's not needed, then does it hurt?

The question says SQL Server 2005.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #474189
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse