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

PowerShell:

$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 { $_.name -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!