System Databases

Question

Post reply

System Databases

TravisDBA

SSCoach

Points: 15780
More actions
September 8, 2010 at 8:26 pm

#227457

Comments posted to this topic are about the item System Databases
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"

Viewing 15 posts - 1 through 15 (of 34 total)

You must be logged in to reply to this topic. Login to reply

UMG Developer SSChampion Points: 13482 More actions · Answer 1

Nice question thanks!

I think it would have been more challenging if it has been multiple choice...

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 2

Good question. Too bad you did not include a reference in the explanation.

For those who want to double check (and to see HOW those system databases can be moved), here is a reference: http://msdn.microsoft.com/en-us/library/ms345408.aspx

UMG Developer (9/8/2010)
I think it would have been more challenging if it has been multiple choice...

True. But it is, unfortunately, not possible to submit a question with only one correct answer as a multiple choice question. The software automatically decides on single choice vs multiple choice based on the number of correct answers.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

vk-kirov SSCertifiable Points: 7686 More actions · Answer 3

The Resource database is a read-only database that contains all the system objects that are included with SQL Server ... So, the Resource db cannot be moved.

Sorry, but this conclusion is wrong. In SQL Server 2005, the Resource database can be moved along with the master database: http://msdn.microsoft.com/en-us/library/ms345408(v=SQL.90).aspx. In SQL Server 2008, the Resource database cannot be moved (as I think) just by design.

Good question, anyway.

Sequelsurfer SSCrazy Points: 2562 More actions · Answer 4

Got caught out by this question because I knew I had moved the Resource database at some point in the past. Had to have been SQL 2005 though as has been correctly pointed out it is fixed in SQL 2008.

Chris Houghton SSCrazy Points: 2557 More actions · Answer 5

vk-kirov (9/9/2010)
The Resource database is a read-only database that contains all the system objects that are included with SQL Server ... So, the Resource db cannot be moved.
Sorry, but this conclusion is wrong. In SQL Server 2005, the Resource database can be moved along with the master database: http://msdn.microsoft.com/en-us/library/ms345408(v=SQL.90).aspx. In SQL Server 2008, the Resource database cannot be moved (as I think) just by design.
Good question, anyway.

Agreed. I have moved the resource database when relocating the master database in SQL Server 2005. I suspected that I'd be marked wrong on this question for selecting the "NONE" option

chris.reynolds SSC Enthusiast Points: 186 More actions · Answer 6

Interesting question:

SQL 2008 help says you can:

In SQL Server 2008, the default location of the Resource database is <drive>:\Program Files\Microsoft SQL Server\MSSQL10.<instance_name>\Binn\. The database can be moved; however, we recommend against moving it for two reasons:
Applying SQL Server service packs and hotfixes restores the database to the \Binn location.
Moving the Resource database in a failover cluster environment to a nonclustered location will cause failover cluster failure.
To move the Resource database, follow these steps.

which, I guess is superceded by the KB mentioned above.

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

Michael Riemer SSCertifiable Points: 5190 More actions · Answer 7

Great question, and interesting feedback as always- Thanks all

cengland0 SSCertifiable Points: 6102 More actions · Answer 8

Hugo Kornelis (9/9/2010)
Good question. Too bad you did not include a reference in the explanation.
For those who want to double check (and to see HOW those system databases can be moved), here is a reference: http://msdn.microsoft.com/en-us/library/ms345408.aspx
UMG Developer (9/8/2010)
I think it would have been more challenging if it has been multiple choice...
True. But it is, unfortunately, not possible to submit a question with only one correct answer as a multiple choice question. The software automatically decides on single choice vs multiple choice based on the number of correct answers.

Agreed too. I believe the correct answer should have been "None of these" because they can all be moved. I got it wrong but I still believe I selected the correct answer.

According to Microsoft:

For information about how to move the master data and log files, see Moving System Databases. The Resource database depends on the location of the master database. If you move the master database, you must also move the Resource database to the same location.

This is found on the following page: http://msdn.microsoft.com/en-us/library/ms187837.aspx

coltuszyk SSC Veteran Points: 257 More actions · Answer 9

http://decipherinfosys.wordpress.com/2009/02/12/moving-the-resource-database-in-sql-server-2005/

A QUICK HOW-TO GUIDE if you want move resource database in SQL 2005 :hehe:

-------------------------------
www: 3CityDBA.pl

Eoin The Forge SSCrazy Points: 2932 More actions · Answer 10

As other posters have mentioned, It can be moved, I have done it.

DugyC Hall of Fame Points: 3804 More actions · Answer 11

Just adding another voice to the "I selected the correct answer yet was still marked wrong".

I'm sorry but, as pointed out above, the Resource database can be moved in SQL2005... however, even if you were to keep the answer to the latest version, you should still have stipulated SQL2008 in the question.

_____________________________________________________________________
[font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 12

After reading all the other comments, we can conclude that the question is flawed 🙂

You cannot move the resource DB in 2008, but apparently you can in 2005, so the question should have stated the version of SQL Server.

I got it wrong, as I was reading the KB of 2008, I saw the title "Moving the Resource Database" and I assumed it could be moved. Unfortunately I didn't read the fineprint that stated: "The location of the Resource database is <drive>:\Program Files\Microsoft SQL Server\MSSQL10_50.<instance_name>\MSSQL\Binn\. The database cannot be moved."

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

N33R@J Old Hand Points: 380 More actions · Answer 13

I also agree that the correct option should be "None of Above" as many times I moved the Resource database while moving the system databases in SQL 2005 or it should have been specified in the question which version we are talking about 🙂

jpertell SSC-Addicted Points: 459 More actions · Answer 14

I believe the answer should be "None". Others have already pointed out that it is possible to move the resource db and how. I've successfully moved it on all our production servers.