SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by TravisDBA / System Databases / Latest Posts

RE: System Databases

Ninja's_RGR'us — Thu, 16 Sep 2010 13:17:25 GMT

Ok guys, don't make me call your mother on this!

RE: System Databases

Paul White — Thu, 16 Sep 2010 13:06:58 GMT

[quote][b]TravisDBA (9/16/2010)[/b][hr]That was way out of line Paul. I was not being any such thing at all. I just said that I had already stated that previously and for the person to simply read it. Who is really being the d*** here? :-)[/quote]Out of line?[url]http://meta.wikimedia.org/wiki/Don't_be_a_dick[/url]

RE: System Databases

Koen Verbeeck — Thu, 16 Sep 2010 12:55:00 GMT

To conclude: I really was pointing out the obvious irony :-)

RE: System Databases

TravisDBA — Thu, 16 Sep 2010 12:47:47 GMT

That was way out of line Paul. I was not being any such thing at all. I just said that I had already stated that previously and for the person to simply read it. Who is really being the d*** here? :-)

RE: System Databases

Paul White — Thu, 16 Sep 2010 05:14:49 GMT

[quote][b]TravisDBA (9/9/2010)[/b][hr][quote][b]da-zero (9/9/2010)[/b][hr][quote][b]TravisDBA (9/9/2010)[/b][hr][quote][b]free_mascot (9/9/2010)[/b][hr]Yes, me too worng....The correct answer is none. As though resource database is readonly we can move it along with master.http://msdn.microsoft.com/en-us/library/ms345408.aspx[/quote]In SQL Server 2008, the Resource database cannot be moved, Your provided link clearly states that.:-D[/quote]Unfortunately, your question didn't ;-)[/quote]Reading is fundamental. I already stated in a previous post in this thread that I had omitted the version, and that was my bad, but the answer is correct for SQL 2008. :-D[/quote]da-zero was simply pointing out the obvious irony. There's no need to be a dick about it ":-D"

RE: System Databases

TravisDBA — Thu, 09 Sep 2010 14:00:12 GMT

Thanks Steve. :-D

RE: System Databases

SQLRNNR — Thu, 09 Sep 2010 10:26:25 GMT

[quote][b]Steve Jones - Editor (9/9/2010)[/b][hr]I have corrected the question to say that it is for SQL Server 2008 R2 (using the latest reference), noted that it can be moved in 2005, and then awarded back points for everyone.[/quote]How kind. :cool:

RE: System Databases

Steve Jones - SSC Editor — Thu, 09 Sep 2010 09:19:46 GMT

I have corrected the question to say that it is for SQL Server 2008 R2 (using the latest reference), noted that it can be moved in 2005, and then awarded back points for everyone.

RE: System Databases

TravisDBA — Thu, 09 Sep 2010 08:55:16 GMT

[quote][b]da-zero (9/9/2010)[/b][hr][quote][b]TravisDBA (9/9/2010)[/b][hr][quote][b]free_mascot (9/9/2010)[/b][hr]Yes, me too worng....The correct answer is none. As though resource database is readonly we can move it along with master.http://msdn.microsoft.com/en-us/library/ms345408.aspx[/quote]In SQL Server 2008, the Resource database cannot be moved, Your provided link clearly states that.:-D[/quote]Unfortunately, your question didn't ;-)[/quote]Reading is fundamental. I already stated in a previous post in this thread that I had omitted the version, and that was my bad, but the answer is correct for SQL 2008. :-D

RE: System Databases

SQLRNNR — Thu, 09 Sep 2010 08:39:03 GMT

Thanks for the Question. I was debating internally whether it was 2005 or 2008. I decided to go with 2008.

RE: System Databases

Bradley Deem — Thu, 09 Sep 2010 08:33:09 GMT

Funny, I initially chose the Resource database cannot be moved, but then after checking my [b]local[/b] BOL I changed to all can be moved. If you check the [b]online[/b] BOL it shows it cannot be moved in SQL 2008 and 2008 R2. Although, I wonder if it really can be moved in 2008 (non R2) considering the inconsistent documentation.From my local BOL [url]ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/72bb62ee-9602-4f71-be51-c466c1670878.htm[/url][quote]Moving the Resource DatabaseIn 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.[/quote]

RE: System Databases

Koen Verbeeck — Thu, 09 Sep 2010 08:28:27 GMT

[quote][b]TravisDBA (9/9/2010)[/b][hr][quote][b]free_mascot (9/9/2010)[/b][hr]Yes, me too worng....The correct answer is none. As though resource database is readonly we can move it along with master.http://msdn.microsoft.com/en-us/library/ms345408.aspx[/quote]In SQL Server 2008, the Resource database cannot be moved, Your provided link clearly states that.:-D[/quote]Unfortunately, your question didn't ;-)

RE: System Databases

TravisDBA — Thu, 09 Sep 2010 07:49:21 GMT

[quote][b]free_mascot (9/9/2010)[/b][hr]Yes, me too worng....The correct answer is none. As though resource database is readonly we can move it along with master.http://msdn.microsoft.com/en-us/library/ms345408.aspx[/quote]In SQL Server 2008, the Resource database cannot be moved, Your provided link clearly states that.:-D

RE: System Databases

free_mascot — Thu, 09 Sep 2010 07:05:11 GMT

Yes, me too worng....The correct answer is none. As though resource database is readonly we can move it along with master.http://msdn.microsoft.com/en-us/library/ms345408.aspx

RE: System Databases

Jack Corbett — Thu, 09 Sep 2010 06:04:54 GMT

I also got this wrong by selecting none, but only because I am using SQL Server 2005 and was aware that the resource database location was/is dependent on the location of the master database. Since others have mentioned the change in 2008 I accept that I was wrong for the latest release of SQL Server.

RE: System Databases

Daniel Bowlin — Thu, 09 Sep 2010 06:01:41 GMT

I had to do a little reading for this one. Thanks.

RE: System Databases

Hugo Kornelis — Thu, 09 Sep 2010 05:52:25 GMT

[quote][b]bitbucket-25253 (9/9/2010)[/b][hr]Hmm seems like we have a disagreement on moving the Resource DB.Now which reference is Correct can it be moved or not? ? ?[url]http://technet.microsoft.com/en-us/library/ms345408(SQL.90).aspx[/url]From the above reference (Bold emphasis added by this poster):(snip)[/quote]Ron, this reference is information for SQL Server 2005. That can be seen by the "(SQL.90)" in the link, or by the "This page is specific to [b]SQL Server 2005[/b]" box at the top of the page.The corresponding page for SQL Server 2008 is at [url=http://msdn.microsoft.com/en-us/library/ms345408.aspx]http://msdn.microsoft.com/en-us/library/ms345408.aspx[/url]. Here, the text reads:[quote][b]Moving the Resource Database[/b]The location of the Resource database is [i]<drive>[/i]:\Program Files\Microsoft SQL Server\MSSQL10_50.[i]<instance_name>[/i]\MSSQL\Binn\. The database cannot be moved.[/quote]

RE: System Databases

TravisDBA — Thu, 09 Sep 2010 05:50:57 GMT

Sorry all, I should have stated the version. As far as the reference in SQL 2008 this is where the reference ishttp://msdn.microsoft.com/en-us/library/ms345408.aspx :-D

RE: System Databases

bitbucket-25253 — Thu, 09 Sep 2010 05:42:21 GMT

Hmm seems like we have a disagreement on moving the Resource DB.Now which reference is Correct can it be moved or not? ? ?[url]http://technet.microsoft.com/en-us/library/ms345408(SQL.90).aspx[/url]From the above reference (Bold emphasis added by this poster):[quote]Moving the master and Resource Databases [B]The Resource database depends on the location of the master database. The Resource data and log files must reside together and must be in the same location as the master data file (master.mdf). Therefore, if you move the master database, you must also move the Resource database to the same location as the master data file. [/B] Do not put the Resource database in either compressed or encrypted NTFS file system folders. Doing so will hinder performance and prevent upgrades.To move the master and Resource databases, follow these steps. From the Start menu, point to All Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Configuration Manager.In the SQL Server 2005 Services node, right-click the instance of SQL Server (for example, SQL Server (MSSQLSERVER)) and choose Properties.In the SQL Server (instance_name) Properties dialog box, click the Advanced tab.Edit the Startup Parameters values to point to the planned location for the master database data and log files, and click OK. Moving the error log file is optional.The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter. The following example shows the parameter values for the default location of the master data and log files.Copy Code -dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldfIf the planned relocation for the master data and log files is E:\SQLData, the parameter values would be changed as follows:Copy Code -dE:\SQLData\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lE:\SQLData\mastlog.ldfStop the instance of SQL Server by right-clicking the instance name and choosing Stop.Move the master.mdf and mastlog.ldf files to the new location.Start the instance of SQL Server in master-only recovery mode by entering one of the following commands at the command prompt. The parameters specified in these commands are case sensitive. The commands fail when the parameters are not specified as shown.For the default (MSSQLSERVER) instance, run the following command.Copy Code NET START MSSQLSERVER /f /T3608For a named instance, run the following command.Copy Code NET START MSSQL$instancename /f /T3608For more information, see How to: Start an Instance of SQL Server (net Commands).Using sqlcmd commands or SQL Server Management Studio, run the following statements. Change the FILENAME path to match the new location of the master data file. Do not change the name of the database or the file names.Copy Code [B]ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME= 'new_path_of_master\mssqlsystemresource.mdf');GOALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME= 'new_path_of_master\mssqlsystemresource.ldf');GOMove the mssqlsystemresource.mdf and mssqlsystemresource.ldf files to the new location.[/B]Set the Resource database to read-only by running the following statement.Copy Code [/quote]

RE: System Databases

tommyh — Thu, 09 Sep 2010 05:24:29 GMT

As for the resource database in SQL2008. Wouldnt you be able to reinstall SQL on a different drive and then copy the old to the new location. [url]http://msdn.microsoft.com/en-us/library/ms190940.aspx[/url]Im guessing "<drive>:\Program Files\Microsoft SQL Server\MSSQL10_50.<instance_name>\MSSQL\Binn\." is the folder where SQL is installed to (and not some hardcoded resourcedb location).It also says that you cant backup the resourcedatabase. But that you can copy it and "restore" it as a file. So that would be like a limited "move" of the resourcedatabase. You cant put it into a folder of your choice. But atleast you can chose which drive you want it on.

RE: System Databases

jpertell — Thu, 09 Sep 2010 03:50:50 GMT

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.

RE: System Databases

N33R@J — Thu, 09 Sep 2010 03:34:28 GMT

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 :-)

RE: System Databases

Koen Verbeeck — Thu, 09 Sep 2010 03:18:07 GMT

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."

RE: System Databases

DugyC — Thu, 09 Sep 2010 02:31:44 GMT

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.

RE: System Databases

Eoin_BI — Thu, 09 Sep 2010 02:23:57 GMT

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

RE: System Databases

coltuszyk — Thu, 09 Sep 2010 02:10:55 GMT

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:

RE: System Databases

cengland0 — Thu, 09 Sep 2010 02:06:48 GMT

[quote][b]Hugo Kornelis (9/9/2010)[/b][hr]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: [url=http://msdn.microsoft.com/en-us/library/ms345408.aspx]http://msdn.microsoft.com/en-us/library/ms345408.aspx[/url][quote][b]UMG Developer (9/8/2010)[/b][hr]I think it would have been more challenging if it has been multiple choice...[/quote]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.[/quote]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. [b]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.[/b]This is found on the following page: http://msdn.microsoft.com/en-us/library/ms187837.aspx

RE: System Databases

Michael Riemer — Thu, 09 Sep 2010 02:02:29 GMT

Great question, and interesting feedback as always- Thanks all

RE: System Databases

chris.reynolds — Thu, 09 Sep 2010 01:57:37 GMT

Interesting question:SQL 2008 help says you can:[quote]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.[/quote]which, I guess is superceded by the KB mentioned above.http://msdn.microsoft.com/en-us/library/ms345408.aspx

RE: System Databases

Chris Houghton — Thu, 09 Sep 2010 01:56:12 GMT

[quote][b]vk-kirov (9/9/2010)[/b][hr][quote]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.[/quote]Sorry, but this conclusion is wrong. In SQL Server 2005, the Resource database can be moved along with the master database: [url]http://msdn.microsoft.com/en-us/library/ms345408(v=SQL.90).aspx[/url]. In SQL Server 2008, the Resource database cannot be moved (as I think) just by design.Good question, anyway.[/quote]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

RE: System Databases

SequelSurfer — Thu, 09 Sep 2010 01:23:41 GMT

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.

RE: System Databases

vk-kirov — Thu, 09 Sep 2010 01:02:41 GMT

[quote]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.[/quote]Sorry, but this conclusion is wrong. In SQL Server 2005, the Resource database can be moved along with the master database: [url]http://msdn.microsoft.com/en-us/library/ms345408(v=SQL.90).aspx[/url]. In SQL Server 2008, the Resource database cannot be moved (as I think) just by design.Good question, anyway.

RE: System Databases

Hugo Kornelis — Thu, 09 Sep 2010 00:43:32 GMT

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: [url=http://msdn.microsoft.com/en-us/library/ms345408.aspx]http://msdn.microsoft.com/en-us/library/ms345408.aspx[/url][quote][b]UMG Developer (9/8/2010)[/b][hr]I think it would have been more challenging if it has been multiple choice...[/quote]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.

RE: System Databases

UMG Developer — Wed, 08 Sep 2010 20:41:13 GMT

Nice question thanks!I think it would have been more challenging if it has been multiple choice...

System Databases

TravisDBA — Wed, 08 Sep 2010 20:26:16 GMT

Comments posted to this topic are about the item [B]System Databases[/B]