SQL Server backup and restore of the Resource database

By:   |   Updated: 2008-07-21   |   Comments (9)   |   Related: > 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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Edwin Sarmiento Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2008-07-21

Comments For This Article




Thursday, February 14, 2013 - 4:20:26 AM - Eduard Back To Top (22115)

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 Back To Top (21382)

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 Back To Top (21378)

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 5, 2012 - 8:25:03 AM - vajeed Back To Top (20759)

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 Back To Top (19924)

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 Back To Top (2450)

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 Back To Top (2420)

 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 Back To Top (1482)

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 Back To Top (1481)

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














get free sql tips
agree to terms