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
- Does DBCC Automatically Use Existing Snapshot?
- Protection From Restoring a Backup of a Contained Database
- Determining Files to Restore Database
- Back That Thang Up
- Dealing With Corruption in a Nonclustered Index
- Dealing With Corruption in Allocation Pages
- Writing SLAs for Disaster Recover
- Resolutions for All DBAs
- Use All the Checksums
- Monitoring for Corruption Errors
- Converting LSN Formats
- Extreme Disaster Recovery Training
- Standard Backup Scripts
- Fixing a Corrupt Tempdb
- Running DBCC CheckTable in Parallel Jobs
- Disaster Recovery Gems From Around The Net
- When are Checksums Written to a Page
- How to CHECKDB Like a Boss
- How Much Log Can a Backup Log
- The Case of the Backups That Wouldn’t Restore
- Who Deleted That Data?
- Which DBCC CHECK Commands Update Last Known Good DBCC
- Restoring Differential Backups With New Files
- Handling Corruption in a Clustered Index
- Improving Performance of Backups and Restores
- The Mysterious Case of the Long Backup
- Restoring Part of a Database
- Recovering SQL if the Tempdb Drive Dies
- Using Database Snapshots to Restore Replicated Databases in Test
- 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!
SqlChow
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.
SQLSoldier
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.
sabuv
Appreciate your effort Robert. I Really enjoyed the whole series!
SQLSoldier
Thanks sabuv! Later this week, I will post a “round-up” post and put all 31 days into a downloadable pdf.
Day 31 of 31 Days of Disaster Recovery: Backup and Restore of the … « Quick Disaster Recovery.com
[…] Follow this link: Day 31 of 31 Days of Disaster Recovery: Backup and Restore of the … […]
mike
Another great article. Thanks. Full Disclosure. I’ve never backed it up.
SQLSoldier
Thanks! Not many people do.
Ramon Gutierrez
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.
SQLSoldier
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.