Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Moving System Databases


Moving System Databases

Author
Message
EdVassie
EdVassie
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3144 Visits: 3816
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 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 1 Dec 2016: now over 39,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
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36118 Visits: 18744
ALTER might be preferred, but if it's not an answer, you don't get to suggest it Wink

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
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search