Day 31 of 31 Days of Disaster Recovery: Backup and Restore of the Resource Database

31 Days of Disaster Recovery
31 Days of Disaster Recovery
It has been a long journey to the final day my 31 Days of Disaster Recovery series, but we have finally reached the final post, day 31. My final topic for the series is born out of a conversation I had today with my good friend and fellow DBA, Ed Watson (blog|@SQLGator). today, I want to talk about backing up and restoring the resource database.

The resource database stores critical system objects safely separated away from the master database. It is critical in operation of the system and the master database is fairly useless without it. If you lose the resource database, the system cannot start the master database and SQL Server will not being able to start. So we need to back it up and be prepared to restore it if need be.

If you missed any of the earlier posts in my DR series, you can check them out here:

    31 Days of disaster Recovery

  1. Does DBCC Automatically Use Existing Snapshot?
  2. Protection From Restoring a Backup of a Contained Database
  3. Determining Files to Restore Database
  4. Back That Thang Up
  5. Dealing With Corruption in a Nonclustered Index
  6. Dealing With Corruption in Allocation Pages
  7. Writing SLAs for Disaster Recover
  8. Resolutions for All DBAs
  9. Use All the Checksums
  10. Monitoring for Corruption Errors
  11. Converting LSN Formats
  12. Extreme Disaster Recovery Training
  13. Standard Backup Scripts
  14. Fixing a Corrupt Tempdb
  15. Running DBCC CheckTable in Parallel Jobs
  16. Disaster Recovery Gems From Around The Net
  17. When are Checksums Written to a Page
  18. How to CHECKDB Like a Boss
  19. How Much Log Can a Backup Log
  20. The Case of the Backups That Wouldn’t Restore
  21. Who Deleted That Data?
  22. Which DBCC CHECK Commands Update Last Known Good DBCC
  23. Restoring Differential Backups With New Files
  24. Handling Corruption in a Clustered Index
  25. Improving Performance of Backups and Restores
  26. The Mysterious Case of the Long Backup
  27. Restoring Part of a Database
  28. Recovering SQL if the Tempdb Drive Dies
  29. Using Database Snapshots to Restore Replicated Databases in Test
  30. Using Partial Availability and Initialize from Backup to Replicate a Partial Database

Back Up the Resource Database

SQL Server service packs (SPs), cumulative updates (CUs), hotfixes, etc., may or may not upgrade the resource database to a new version. It is highly critical that we back up the resource database after installing an SP, CU, hotfix, etc. to ensure that we have a current version. In some scenarios, you may be ale to restore a slightly older version of the resource database and then reapply all patches and updates to bring it current. In some cases, you may not. The safest bet is to always make sure you have a current copy of the database.

If you thought the resource database was backed up with the master database, it is not. You must back it up separately. You can’t, however, back it up using native SQL Server backup. My recommended process is to simply copy the resource database files to the backup directory using simple file copy (robocopy, xcopy, copy-item, etc). Then I am sure that whatever process saves my backups to tape or alternative storage is saving backups of the resource database as well.

Below are a couple examples of how I back up the resource database as part of an automated job. Each example copies the files to the backup folder and adds a data string in the format _YYYYMMDD to the end of the filename.

Windows script (DOS):

for /f "tokens=1* delims= " %%a in ('date/t') do set cdate=%%b
for /f "tokens=1,2 eol=/ delims=/ " %%a in ('date/t') do set mm=%%b
for /f "tokens=1,2 delims=/ eol=/" %%a in ('echo %cdate%') do set dd=%%b
for /f "tokens=2,3 delims=/ " %%a in ('echo %cdate%') do set yyyy=%%b
set date=%yyyy%%mm%%dd%
xcopy "C:\Program files\Microsoft SQL Server\MSSQL11.SQL13\MSSQL\Binn\mssqlsystemresource.*" D:\Backup\SQL13\mssqlsystemresource\mssqlsystemresource_%date%.* /J /Q


$date = "_" + (get-date -format yyyyMMdd) + "."
copy-item -path "C:\Program files\Microsoft SQL Server\MSSQL11.SQL13\MSSQL\Binn\mssqlsystemresource.*" -destination D:\Backup\SQL13\mssqlsystemresource\ -force -passthru | rename-item -newname { $ -replace '\.',$date}

Restore the Resource Database

The process of restoring the resource database is just as straightforward as backing it up. If you determine that your resource database is corrupted, fixing it is as simple as copying a good copy of the resource database files in place of the old ones. If corruption is detected, SQL Server will not be able to start and you will find a message in the application event file similar to the following:

SQL Server detected a logical consistency-based I/O error: invalid protection option. It occurred during a read of page (1:3289) in database ID 32767 at offset 0x000000019b2000 in file ‘C:\Program Files\Microsoft SQL Server\MSSQL11.SQL13\MSSQL\Binn\mssqlsystemresource.mdf’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

If you are wondering, yes, I intentionally corrupted my resource database file to be able to demonstrate this problem. Since SQL Server is offline, I copy a good backup copy of the files in place of the old ones, and then SQL is able to restart successfully. Easy, peezy, lemon-squeezy.

But what happens if you don’t have any backups of the resource database files? Well, you can always rebuild the master database (see Books Online for the process) which means all system databases get rebuilt and you will have to recreate server objects (logins, linked servers, SQL jobs, etc) and settings (server configurations, changes to the model database, replication settings). In short, rebuilding the master database can be a colossal pain.

So be safe and always have backups!

9 thoughts on “Day 31 of 31 Days of Disaster Recovery: Backup and Restore of the Resource Database

  1. In case we do not have the resource DB files backed up but find ourselves in need of recovery, then we could also use files from a different instance which is at the same version and has the same features installed on it.

    1. Thanks for calling that one. I thought about mentioning it while writing it, but then I started wondering if it was a supported solution and did not want to call it out without knowing that it wouldn’t make their installation unsupported. I don’t think it would but want to be sure before publicly recommending it.

  2. hi Robert.
    I have learned a lot from you. Good job and thanks for sharing the knowledge.
    I think the you forgot one thing or maybe i’m wrong. What happen if you move the master file to a new location??? is resource db depends on the master as too???

    Thanks you once again.

    1. Hi Ramon. It depends on the version of SQL Server you’re using. Older versions of SQL require that the resource db and master db files be in the same location. Newer version use a specific location for the resource db file. I don’t recall off of the top of my head which version that changed.

      My advice in this post doesn’t talk about moving the resource db or master db. Backing up and restoring the resource db is a totally separate issue from moving databases.

Comments are closed.