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»»

Resource database - SQL Server 2008 Expand / Collapse
Author
Message
Posted Friday, February 11, 2011 11:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:57 PM
Points: 7,745, Visits: 9,493
Lady Di (2/11/2011)
In SQL Server 2008 BOL you can read this:

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;


If you look at the latest version of the page you will find that has changed. And at the bootom of the page you will find the change history section, with a single (undated) entry which reads
Updated the section "Moving the Resource Database" to indicate that the Resource database cannot be moved.


Tom
Post #1062769
Posted Friday, February 11, 2011 2:22 PM


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
Dupe alert! I submitted this same question back a couple of months ago.

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ..."
Post #1062834
Posted Friday, February 11, 2011 3:54 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:29 PM
Points: 17,729, Visits: 15,594
Thanks for the question.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1062882
Posted Friday, February 11, 2011 4:13 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 31, 2012 12:04 AM
Points: 47, Visits: 716
I gave it a try on SQL Server 2008 (10.0.2531.0) and I can confirm the resource database can be moved.

1. Stop SQL
2. Move mssqlsystemresource.mdf and mssqlsystemresource.ldf to C:\
3. From the cmd prompt run sqlservr.exe -f -T3608 (NB: I was in the folder C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn folder)
4. From another cmd prompt run sqlcmd and use

ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME= 'c:\mssqlsystemresource.mdf');
GO
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=log, FILENAME= 'c:\mssqlsystemresource.ldf');
GO

5. Close both cmd prompts and Start SQL Server normally.

Can I get my 1 point back?

The strange thing is that when you query the path e.g. by starting SQL using sqlservr.exe -m then from another prompt using sqlcmd -A and running select * from mssqlsystemresource.dbo.sysfiles I got

e:\sql10_katmai_t\sql\mkmastr\databases\objfre\i386\mssqlsystemresource.mdf and
e:\sql10_katmai_t\sql\mkmastr\databases\objfre\i386\mssqlsystemresource.ldf

I don't have an E drive.
Post #1062888
Posted Sunday, February 13, 2011 12:30 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 2:46 AM
Points: 453, Visits: 153
Hi everybody ,

My answer was Yes, because I have read in my local MSDN this :

Moving the Resource Database
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.

Stop the instance of SQL Server if it is started.


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 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
ALTER DATABASE mssqlsystemresource SET READ_ONLY;


Exit the sqlcmd utility or SQL Server Management Studio.


Stop the instance of SQL Server.


Restart the instance of SQL Server.

Is that wrong ?

Thanks in advance.

Post #1063100
Posted Sunday, February 13, 2011 7:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:57 PM
Points: 7,745, Visits: 9,493
khelloufsofiane 6183 (2/13/2011)
Hi everybody ,

My answer was Yes, because I have read in my local MSDN this :

Moving the Resource Database
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:
...

Is that wrong ?

Thanks in advance.


Yes it's wrong. It was an error in an early version of that BoL page. The current up to date SQL 2008 BoL page (Moving System Databases) states that it cannot be moved. If you try doing what was on your old version of that page you will discover that your SQL stops working and you have to undo it.
This has been pointed out be several people in earlier comments; and the first reference given in the explanation of the answer is to the current version of that BoL page. When you have an issue with a QotD answer it's sometimes a good idea to look at the references given and read the discussion before jumping in.


Tom
Post #1063131
Posted Sunday, February 13, 2011 10:13 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:25 AM
Points: 860, Visits: 1,402
The Resource database (mssqlsystemresource)depends on the location of the master database. If we move the master database, can't the resource database also would follow the master database?

Thanks.
Post #1063271
Posted Monday, February 14, 2011 12:52 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:39 AM
Points: 1,426, Visits: 1,841
Good question, Hardik! Thank-you.

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
Post #1063334
Posted Monday, February 14, 2011 2:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 15, 2011 7:06 PM
Points: 39, Visits: 5
I managed to do it so i answered yes... =(
Post #1063375
Posted Monday, February 14, 2011 7:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:57 PM
Points: 7,745, Visits: 9,493
Sourav-657741 (2/13/2011)
The Resource database (mssqlsystemresource)depends on the location of the master database. If we move the master database, can't the resource database also would follow the master database?

No, in SQL SERVER 2008 (and 2008 R2) the resource database has to live in the same place as the SQL Server executables.


Tom
Post #1063521
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse