Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

System Databases Expand / Collapse
Author
Message
Posted Thursday, September 9, 2010 2:23 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, March 7, 2014 9:49 AM
Points: 272, Visits: 739
As other posters have mentioned, It can be moved, I have done it.


Post #982900
Posted Thursday, September 9, 2010 2:31 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 27, 2013 4:28 AM
Points: 1,248, Visits: 779
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.


_____________________________________________________________________
"The difficult tasks we do immediately, the impossible takes a little longer"
Post #982907
Posted Thursday, September 9, 2010 3:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:42 PM
Points: 13,239, Visits: 11,018
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."




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #982925
Posted Thursday, September 9, 2010 3:34 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 2:46 AM
Points: 282, Visits: 291
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
Post #982929
Posted Thursday, September 9, 2010 3:50 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 8:06 AM
Points: 235, Visits: 498
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.
Post #982935
Posted Thursday, September 9, 2010 5:24 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Sunday, June 29, 2014 11:26 PM
Points: 1,481, Visits: 1,960
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.

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

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.
Post #982989
Posted Thursday, September 9, 2010 5:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 10:58 AM
Points: 5,333, Visits: 25,272
Hmm seems like we have a disagreement on moving the Resource DB.

Now which reference is Correct can it be moved or not? ? ?

http://technet.microsoft.com/en-us/library/ms345408(SQL.90).aspx

From the above reference (Bold emphasis added by this poster):

Moving the master and Resource Databases
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. 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.ldf
Stop 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 /T3608
For a named instance, run the following command.
Copy Code
NET START MSSQL$instancename /f /T3608
For 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
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME= 'new_path_of_master\mssqlsystemresource.mdf');
GO
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=log, FILENAME= 'new_path_of_master\mssqlsystemresource.ldf');
GO
Move the mssqlsystemresource.mdf and mssqlsystemresource.ldf files to the new location.


Set the Resource database to read-only by running the following statement.
Copy Code


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #983000
Posted Thursday, September 9, 2010 5:50 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:03 PM
Points: 1,334, Visits: 3,069
Sorry all, I should have stated the version. As far as the reference in SQL 2008 this is where the reference is
http://msdn.microsoft.com/en-us/library/ms345408.aspx


"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ..."
Post #983009
Posted Thursday, September 9, 2010 5:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:07 AM
Points: 6,040, Visits: 8,322
bitbucket-25253 (9/9/2010)
Hmm seems like we have a disagreement on moving the Resource DB.

Now which reference is Correct can it be moved or not? ? ?

http://technet.microsoft.com/en-us/library/ms345408(SQL.90).aspx

From the above reference (Bold emphasis added by this poster):
(snip)

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 SQL Server 2005" box at the top of the page.

The corresponding page for SQL Server 2008 is at http://msdn.microsoft.com/en-us/library/ms345408.aspx. Here, the text reads:
Moving the Resource Database

The location of the Resource database is <drive>:\Program Files\Microsoft SQL Server\MSSQL10_50.<instance_name>\MSSQL\Binn\. The database cannot be moved.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #983010
Posted Thursday, September 9, 2010 6:01 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:36 PM
Points: 2,818, Visits: 2,565
I had to do a little reading for this one. Thanks.
Post #983016
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse