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


Moving System Databases


Moving System Databases

Author
Message
John Flannery
John Flannery
SSC Veteran
SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)

Group: General Forum Members
Points: 245 Visits: 192
Comments posted to this topic are about the item Moving System Databases
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18947 Visits: 12426
The explanation offered doesn't make much sense to me.

"tempdb is reallocated every time the instance starts. This reallocation requires the existence of model to be successful."

But who has suggested dropping and recreating model? It was about moving. And whether tempdb is moved before or after model is moved, in both casees it exists. Just in a different location.

"msdb is used by the SQL Server agent and has no bearing on the stability of the database engine."

And how does it follow from this that it can only be moved after tempdb?


Can someone please provide a pointer to some resource (BOL, MSKB, maybe even some non-MS web site) that confirms this?

As far as I know, and as far as I read from Books Online, system databases can be moved in any order you want. Also, you don't have to move all or none, you can just move one or two and leave the rest in the default location.

The only thing I found that is vaguely related is that **IF** you move system databases by detach and attach (not the method I'd prefer - BOL describes a much easier method), and **IF* you also do this for model and msdb at the same time, **THEN** you need to reatrtach model before you can reattach msdb. This is documented here: http://support.microsoft.com/kb/224071.

But unless someone can point me to some documentation I overlooked, I'll have to conclude that this QotD is wrong. It should be removed from the site, as it can only create confusion.

(But seeing that the erroneous March 6 question hasn't been removed yet either, I don't expect this to happen... )


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Jamie-2229
Jamie-2229
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3870 Visits: 857
As I don't truly make any adjustments to the model, the important one to me is the msdb containing all the scripts for jobs. For me, the important one of the three to relocate is the msdb. In the article Hugo has given, I'm not sure the model is needed for relocation of the msdb. http://support.microsoft.com/kb/224071

Curious - can we move MAPI functions from 2000 to 2005 [which uses SMTP]? Wouldn't detach and attach of model and msdb pose a problem for database mail?

Jamie
GSquared
GSquared
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58639 Visits: 9730
This question makes no sense, and the answer doesn't clarify anything.

When moving system databases, I can't find any documentation that says it has to be in a particular order. If it were required that it be in a specific sequence, I would think that Microsoft would have said so in either BOL or MSDN.

Here's the MSDN article on the subject:

http://msdn2.microsoft.com/en-us/library/ms345408.aspx

I can't find anything in it that says it has to be in a certain sequence.

It looks to me like the author of the question was confused by something he read and didn't understand. I'd like to see some documentation otherwise.

This web page is considered a very reputable source for data about SQL Server. This question (like the age calculation one a little while back), could actually result in spreading confusion. I really do think there needs to be some vetting on these.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
DavidSimpson
DavidSimpson
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1550 Visits: 1088
Despite the complete vagueness of the questions, plus the fact it does not matter which order you move the system databases, I chose tempdb as needing addressed next mainly because the proper placement of this database will have the highest performance impact compared to the other system databases.

Personally, I would not recommend moving the master database in SQL Server 2005 because if you do you need to move the mssqlsystemresource hidden system database and I've seen where this does not work and SQL Server will not start up. If you install SQL Server to the proper folders, you will not need to move the master database.

Also the statement about the placement of model impacting tempdb is completely wrong... model has nothing to do with moving the tempdb. See Moving System Databases in BOL.

The statement about MSDB having no bearing on the stability of the database engine is a bit misleading as well. Between model and MSDB, MSDB is used a lot more and generally it's location is much more important then the model database. Model does not completely impact the location of new databases since I can define the default folder locations independently of where model is located. Out of all the system database, the placement of model would be my least concern.

Sorry, but this was not a good question and even worse answer. I really do not care about the points but rather someone new to SQL Server reading the question and answer and being mislead. Blink

David



Adam Bean
Adam Bean
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6700 Visits: 2222
The real answer should be mssqlsystemresource ...

---
SQLSlayer
Making SQL do what we want it to do.
DavidSimpson
DavidSimpson
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1550 Visits: 1088
The real answer should be mssqlsystemresource ...


Adam is correct and using that answer would have made the question make sense... and even be a good question.

David



Steve Jones
Steve Jones
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: Administrators
Points: 148218 Visits: 19444
not sure how this question go through. I don't even remember seeing it, but I must have approved it for some reason.

I'll reword and change it around. Points will be awarded when I get back from my kid getting a tooth removed.

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
Joshua M Perry
Joshua M Perry
SSC Eights!
SSC Eights! (810 reputation)SSC Eights! (810 reputation)SSC Eights! (810 reputation)SSC Eights! (810 reputation)SSC Eights! (810 reputation)SSC Eights! (810 reputation)SSC Eights! (810 reputation)SSC Eights! (810 reputation)

Group: General Forum Members
Points: 810 Visits: 551
Still wrong. That's the procedure for moving between servers. This is the procedure for moving the location:

http://technet.microsoft.com/en-us/library/ms345408.aspx

Also, setting trace flag 3608 at startup and running in single user mode are the same thing, so two of the answers are identical.

Joshua Perry
http://www.greenarrow.net
Adam Bean
Adam Bean
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6700 Visits: 2222
Joshua Perry (3/21/2008)
Still wrong. That's the procedure for moving between servers. This is the procedure for moving the location:

http://technet.microsoft.com/en-us/library/ms345408.aspx

Also, setting trace flag 3608 at startup and running in single user mode are the same thing, so two of the answers are identical.


If you move the master, the mssqlsystemresource has to reside in the same location ... doesn't matter if you're moving servers or to a new drive.

---
SQLSlayer
Making SQL do what we want it to do.
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