SQL Server Resource database corruption–yes, it’s possible
It’s very rare that I run into an issue with the Resource database, and the one I ran into recently was rarer still. But before I get into the nitty-gritty of the issue, let us begin by outlining a few details about the resource database:
The Resource database
The resource database is a hidden system database, and cannot be accessed explicitly by the users. Also, as documented here, there is no way inside SQL Server to back up the resource DB. The only way to take a backup of the resource db is to make file level copies. This is something that you can do either manually or through VSS (disk level) backups.
Now, it’s not without reason that we do not have any way to take backups of the Resource database. A few salient points:
- The resource DB is a read-only database
- Outside of a hardware issue, there is no way for the resource db to get corrupted.
But what if there is a hardware problem, say, god forbid, your SAN crashes, or if there’s some sort of a “scribbler” issue with one of the hardware drivers (more details on that in a different post), and you end up with your resource database corrupted, what do you do? Here are the options, in order:
- The ideal way to get out of this situation is to restore the resource db files from file level backups. So if you’re reading about this database for the first time, the first thing you should do is to make file-level copies of the resource db files (or add them to the set of files you back-up using VSS backups). I would recommend taking backups of the resource db files immediately after the successful application of a hotfix/Service Pack/CU/Security Update.
- If you are in this situation already, and do not have a backup of your resource db files, do not despair. Simply take another server, install an instance with the same instance id and instance name as the target instance, and bring it to the same build as well. Once this is done, stop the SQL Service, copy the resource db files, and use them to replace the corrupted resource db files on the problem instance. Your SQL server service should come online now. I’ve tested this extensively on SQL 2008 and 2008 R2, and it indeed works.
- If this is a cluster, and you’re on SQL 2008 or later, you can try bringing SQL up on the second node. If the second node’s copy of the resource db files are not corrupted, you should be successful.
Now, allow me to explain why this special case described in bullet 3 exists:
In SQL 2005, the resource db was tied to the master database, and the resource db mdf and ldf files had to be in the same folder as the master db files, else your SQL Service would fail to start. In case of a cluster, the resource db resided on a clustered drive, and when the failover happened, the ownership of the resource database was passed to the second node. Since we had only one copy of the resource database to patch, we were able to patch all the nodes on the cluster in a single run in case of SQL 2005.
This behaviour changed from SQL 2008 onwards. In SQL 2008 and 2008 R2, the resource database is no longer tied to the master database, and exists in the Binn folder instead. So basically, the resource database is a part of the instance binaries from SQL 2008 onwards. This is why, in case of SQL 2008 and 2008 R2, you need to patch both the nodes separately (one by one). Makes sense? This is why I mentioned in point 3 above that if you are on a cluster and SQL is 2008 or later, there is a good chance you might be able to get SQL up on the other node, even if the resource db files on one node are corrupted.
As a last word, if you’re not sure how your resource db files came to be corrupted, please take it as a top priority to find the root cause behind the corruption, as this is definitely something that warrants further investigation.
If you have any interesting incidents to share w.r.t the resource database, please feel free to do so in the comments section.
Comments
Anonymous
October 09, 2012
Well, the simplest way is probably to detach a DB, running DBCC CHECKDB check database consistency running DBCC CHEKCTABLE state to repair SQL table You may recover corrupted sql database see more exchangerecovery.hubpages.com/.../How-to-perform-SQL-Database-RecoveryAnonymous
October 09, 2012
Interesting comments John. I'd like to point out that it's not possible to detach the Resource database. Also, it's possible to run Checkdb against the resource database (by running it against the master db), but repairing the corruption forcefully is not advised, as the resource db is a system, read-only database. So, in short, though your comments might be applicable for user databases, they're not applicable for the resource database. Thanks.Anonymous
May 31, 2013
Gud blog :) Can you please elaborate more on point 3 as resourceDB is on shared storage. when ownership change to another node,(auto failover) it will see the same corrupt files.Anonymous
June 05, 2013
Hi Manish, Thanks for showing interest in the blog. Starting SQL Server 2008, the resource database is no longer tied to the master db, and is stored in the Binn directory. This implies that in a clustered installation of SQL, each node has its own copy of the resource database. Thus, its possible that the SQL Server may come online when failed over in this scenario. Hope this clarifies your doubt. Regards, HarshAnonymous
October 28, 2013
excellent..Anonymous
October 28, 2013
Thanks for appreciating Kishore...!!!Anonymous
November 13, 2013
Hi, I'm looking for information on how to best standardize MS SQL installations for different backup and recovery scenarios. Where to locate the shared and instance binaries, master and model databases, user databases, and transaction logs. Can all the binaries be located on one drive? Our sys ops guys, don't want us to install the MS SQL biniaries with the OS although a large part of the binaries go there anyway. They also say the only recovery process that is supported is a rebuild although they are taking VSS backups. In short is there any guidance that covers the 3 or 4 recovery scenarios for the binaries and master/model databases. this was very helpful on the resource database which I didn't know existed. I'm DBA manager driving my MS SQL dBA with this request. thanks, Laura.Anonymous
November 14, 2013
Hi Laura, I believe we've already continued this discussion offline. Please let me know if I can be of further assistance. Thanks, HarshAnonymous
June 22, 2014
Good Article HarshDeep :)Anonymous
June 23, 2014
Thanks for appreciating Manish.Anonymous
March 29, 2015
Hi Harsh How to recover if master database is corrupted...Anonymous
April 19, 2015
thanks for sharing about resource database . could yo please share about the internal processes of sql server for backup .what and how it happens.Anonymous
April 20, 2015
Hi Nandeesh, Unfortunately, the only way to recover from master database corruption is to first rebuild it, and then restore it from a good backup. Details on rebuild process can be found here: msdn.microsoft.com/.../dd207003.aspx Hope this helps. HarshAnonymous
April 20, 2015
Hi Ravi, thanks for appreciating. Backup details are discussed to some extent in the following posts: technet.microsoft.com/.../2009.07.sqlbackup.aspx technet.microsoft.com/.../ms191455(v=sql.105).aspx Hope this helps. Regards, HarshAnonymous
April 22, 2015
Hi Harsh, my question is regarding the treatment of Resource db in a cluster. Suppose if I changed a value within sys.configurations, how is this value persisted within SQL Server and especially in the event of a failover or a crash.Anonymous
April 27, 2015
Very Good Article HarshDeep.. It clarified many of my doubts Resource Database.. Good work.!!!!!Anonymous
July 27, 2015
Harshdeep, Fantastic info. I had the same issue, but we had same instance on dev, i copied the files, everything came back up fineAnonymous
July 29, 2015
Hi Shantanu, To answer your question, the data in sys.configurations (or for that matter, any settings that can be modified by SQL Admins) is stored in master, not resource db, and since there is only one copy of the master, the changes are retained in case of a failover. Hope this helps. Regards, HarshAnonymous
July 29, 2015
Thanks a lot Ramesh and Prasanth. Glad you found the blog useful.Anonymous
October 04, 2015
excellent blog...douts are getting cleared by follwing each articles