Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Resource database - SQL Server 2008


Resource database - SQL Server 2008

Author
Message
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10707 Visits: 12008
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

TravisDBA
TravisDBA
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1462 Visits: 3069
Dupe alert! I submitted this same question back a couple of months ago. :-D

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21075 Visits: 18259
Thanks for the question.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw

sql-kevin
sql-kevin
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
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.
khelloufsofiane 6183
khelloufsofiane 6183
Mr or Mrs. 500
Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)

Group: General Forum Members
Points: 573 Visits: 179
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.
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10707 Visits: 12008
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

SQL-DBA-01
SQL-DBA-01
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2327 Visits: 3175
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.
Nakul Vachhrajani
Nakul Vachhrajani
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1824 Visits: 2127
Good question, Hardik! Thank-you.

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

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
salprith
salprith
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 5
I managed to do it so i answered yes... =(
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10707 Visits: 12008
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

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