Learn more about SQL Server tools

   
   






















































Connect with MSSQLTips




MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Pinterest Page MSSQLTips Twitter Page MSSQLTips Google+ Page

SQL Server backup and restore of the Resource database

MSSQLTips author Edwin Sarmiento By:   |   Read Comments (9)   |   Related Tips: More > Backup

Problem
I have been hearing about the Resource database in SQL Server 2005 and that it should be included in our system database backups. Unfortunately, this database does not show up in SQL Server Management Studio for me to include in my database backups. How do we backup and restore the Resource database?

Solution

What is the Resource database?
The Resource database (shortly referred to as RDB) is a hidden, read-only database that contains all the system objects that are included with SQL Server 2005. This is the reason why it does not appear in SQL Server Management Studio. It contains all the system objects that ship with SQL Server 2005. These objects physically exist in the Resource database but logically appear in the sys schema of every database on the instance. It complements the master database in a sense as the SQL Server service now also depends on this database. The Resource database makes it easy for service packs to be applied or rolled back whenever necessary. In SQL Server 2000, whenever a service pack is applied, all the system objects that reside on both system and user databases will be updated, making it more difficult to rollback the change whenever necessary. It is also the reason why Microsoft recommends that you backup all the system and user databases before applying a service pack.

In SQL Server 2005, changes will only be made to the this database and will be reflected on all the system and user databases on the instance. If you need to apply a service pack on multiple instances, all you need to do is copy the Resource database's MDF and LDF files to the target instances. Rolling back the changes is as simple as overwriting the database files with an older copy. The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf and are located, by default, in <drive>:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\

Why is it important?
The Resource database appears to be a critical system database as the SQL Server service is now dependent on this. You can verify by renaming the database files while the service is stopped. You will not be able to start the service after this. You can also try moving the master database on a different location without moving the Resource database together with it and you will not be able to start the service. It's location is dependent on the master database. This is critical during a disaster recovery process as we have gotten used to dealing with only the master database in previous versions.

Backing up the Resource database
Since the Resource database is not available from the SQL Server tools, we cannot perform a backup similar to how we do it with the other databases. You can backup the database using the following options:

  1. You can use a simple xcopy command to copy from the source location to a destination where you keep your daily database backups. Use the -Y option to suppress the prompt to confirm if you want to overwrite the file. You can create a scheduled task to do this on a daily basis. If you want to keep multiple copies of the database files, you can create an automated script to rename them after the copy process.
    xcopy <drive>:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource.mdf <destination folder> /Y

    xcopy <drive>:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource.ldf <destination folder> /Y

     

  2. You can use your file-based backup utilities such as NTBackup, IBM Tivoli Storage Manager, Symantec BackupExec, etc.

Restoring the Resource database
It is important to document the location of your master database as part of your disaster recovery process. In previous versions of SQL Server, all we need to do to restore the server instance is to worry about the master database.

After a SQL Server 2005 instance has been rebuilt a restore of the master database will be done, the Resource database files should go along with it should a WITH MOVE option be required. This means that if the old location of the master database will be different from the one after the restore, the Resource database files should already be there prior to restoring the master database. This is very critical if a hardware failure occurred and you need to move the system databases on a different drive during the server instance rebuild.

To restore the Resource database, just copy the database files to the location of the master database files. If you have an older version of the Resource database, it is important to re-apply any subsequent updates. This is why the recommended approach is to simply do a daily backup of these files.

Next Steps

  • Try renaming the Resource database files while the service is stopped and verify that the SQL Server service will not start after that
  • Backup the Resource database in your SQL Server 2005 instances
  • Include the Resource database in your disaster recovery plan


Last Update: 7/21/2008


About the author
MSSQLTips author Edwin Sarmiento
Edwin Sarmiento works as a SQL Server DBA for The Pythian Group in Ottawa and is a SQL Server MVP.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Thursday, February 14, 2013 - 4:20:26 AM - Eduard Read The Tip

Hello,

After installing CU4 for 2008R2SP2  unsuccessfull, SQL service failed to start as it can't find resource db. I've copy it from another Instance from another host and it started.
Now I'm not sure how to proceed as SQL version is 10.50.4270 and resource ver is 10.50.4266
(not to mention that is from another SQL server ...).checkdb on master show no errors. Please if you have some advice let me know about it.I would very much appreciate it. Thanks.

edi


Thursday, January 10, 2013 - 7:11:39 PM - bass_player Read The Tip

Kevin,

You can XCOPY the resource database even when the SQL Server service is up and running. You do, however, have to make sure that your backups work by restoring them on a test environment


Thursday, January 10, 2013 - 1:40:39 PM - Kevin Read The Tip

I am wondering if the XCOPY for the resource database backup can be done when the SQL Server is up & running.  It's a read only database but the files are open when the SQL is up.

 

Kevin

 

 


Wednesday, December 05, 2012 - 8:25:03 AM - vajeed Read The Tip

hi my name is s.vajeed.i am doing course in sql server 2008 r2 dba.i have some doubt's regarding schemas and roles concept

1)how to group user's(say user1,user2,user3 & user4) and also how to assign permission to those group using schema concept

2) i have some user's(say 5 user's) and i have some tables like 3 tables.how to assign permission to 2 tables for specific user and 1 table for specific user.how can we do it


Monday, October 15, 2012 - 6:21:23 AM - Satinder Thakur Read The Tip

RDB location isn't dependent on the master database for SQL Server 2008 onwards.Is that correct Edwin?


Friday, December 26, 2008 - 2:40:33 PM - bass_player Read The Tip

The disaster recovery process for restoring the master database is quite complex especially if you are restoring to a different drive.  What I normally do is that if you are moving the master database to a different drive letter - say, from R:\ to C:\ - I would keep the other system databases on the original drive until such time that SQL Server is able to restart successfully. This is because the pointers to the old drive/path still exist. Once SQL Server managed to restart successfully, I restore the other system databases - msdb, tempdb, model(if necessary) - on the new drive target.  The problem arises when the old drive is no longer accessble - such as a hardware failure or a totally inaccessible server.  There are a couple of ways to do that.  One is to emulate the old drive - stick in a USB drive, change the drive letter to the old one and restore the databases. Another way is to change the startup parameters. You can change the startup parameters to point to the new location of the master database files but make sure this is where your Resource database files whould be as well


Thursday, December 18, 2008 - 7:26:45 AM - sri Read The Tip

 Excellent Informatio compiled together. 

But I wanted to know can we really restore master database to a different location than original.

 I tried to Restore the master database from Drive R: to Drive C:, the restore went perfect but then i could not start the SQL Server as it was unable to find R:\...\mssqlsystemresource.mdf and R:\...\mssqlsystemresource.ldf for SQL Server 2005 and R:\...\model.mdf and R:\...\msdb.mdf for SQL Server 2000.

In my opinion, the reason behind this is master can be restored from R: to C: but the pointers to msdb/model/resource databases inside master are still referring to R:.

 I would like to know if there is a chance to change these pointers too while restoring the master database.

 Thaks

Sri


Tuesday, July 22, 2008 - 8:35:40 AM - bass_player Read The Tip

The Resource database is required by SQL Server 2005 service to start and it has to be in the same location as your master database files.  Try it out. Stop the service and rename the two database files.  Then, start the SQL Server 2005 service.  You will notice that the service will not start. I agree that it seems a little too simple but service packs are the ones that changes a lot of system objects.  Security patches only change DLLs, EXEs and some other files, in general.

Imagine that you have a failed disk that contains your system database files. If you have to rebuild and restore the master database, you need to have the Resource database files on that same disk, otherwise, your SQL Server service won't start.  Check out this MSDN article about the Resource database


Tuesday, July 22, 2008 - 5:11:52 AM - asentell Read The Tip

I'm confused as to why I need to worry about backing up the resource database for DR purposes. In BOL under How to: Restore the master Database (Transact-SQL) I don't see any mention of the resource database. How would a backup be useful for anything other than upgrades?

I'm also curious about the applying and rolling backup upgrades and/or service packs. According to BOL:

The Resource database makes upgrading to a new version of SQL Server an easier and faster procedure. In earlier versions of SQL Server, upgrading required dropping and creating system objects. Because the Resource database file contains all system objects, an upgrade is now accomplished simply by copying the single Resource database file to the local server. Similarly, rolling back system object changes in a service pack only requires overwriting the current version of the Resource database with the older version.

Seems a little too simple to me. I'm pretty sure service packs do a lot more than update system tables (e.g., upgrade dlls, executables, etc.). I'm also not clear on what is meant by an "upgrade". Does this refer to only service packs  as specifically mentioned in the text, or are cumulative updates and security patches included in this definition as well? These make modifications outside of the database for sure.

If anyone can clear this up for me I'd appreciate it.

 Aaron




 
Sponsor Information







 

Follow

Get Free SQL Tips

Twitter

LinkedIn

Google+

Facebook

Pinterest

RSS

Learning

DBAs

Developers

BI Professionals

Careers

Q and A

Resources

Tutorials

Webcasts

Whitepapers

Tools

Search

Tip Categories

Search By TipID

Top Ten

Authors

Community

First Timer?

Pictures

Free T-shirt

Contribute

Events

User Groups

Author of the Year

More Info

Join

About

Copyright

Privacy

Disclaimer

Feedback

Advertise

Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
Some names and products listed are the registered trademarks of their respective owners.