Monday, August 1, 2011

Recovering/Restoring the Resource system database in SQL Server 2005

Here is the scenario I have been working through in the LAB today:

The Resource database (mssqlsystemresource) has become corrupt on a SQL 2005 SP4 instance. You are not sure why the database has become corrupt but when you try and start the SQL Server DB Engine the following is reported in the SQL Server ERRORLOG:

2011-08-01 14:57:23.89 spid5s Starting up database 'mssqlsystemresource'.
2011-08-01 14:57:23.91 spid5s Error: 5172, Severity: 16, State: 15.
2011-08-01 14:57:23.91 spid5s The header for file '\MSSQL.1\MSSQL\DATA\mssqlsystemresource.mdf' is not a valid database file header. The PageAudit property is incorrect.
2011-08-01 14:57:23.95 spid5s Error: 945, Severity: 14, State: 2.
2011-08-01 14:57:23.95 spid5s Database 'mssqlsystemresource' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
2011-08-01 14:57:23.97 spid5s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

We know that we cannot backup/restore the Resource database using SQL Server (as per and you could restore the physical file from the server's file system backups. Unfortunately though the file is unable to be recovered from the server's file system backup for an unknown and unrelated external factor.

Never fear there is a simple fix to this. Reinstall the Service Pack as that will recreate the Resource database.... Hurray

Here's the steps and things to note when performing the Service Pack install though:
1. Launch the Service Pack install
2. Accept the terms and conditions and click Next
3. When asked to select the Feature to update you will notice that the Database Services for the required instance will already report "Upgraded". Simply select the instance though as this will re-apply the service pack.
4. Complete the service pack install

Once the Service Pack has been installed you should be able to start the SQL Server Service for the required Instance.

TIPS: You can use the following TSQL to grab information about your SQL Instance and Resource Database. These were taken from

SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');

SQL 2008 / SQL 2008 R2
For the process to rebuild the Resource database in SQL 2008 follow the instructions at

Happy Disaster Recovering everyone :)


  1. Thanks Matt!
    Your post gave me confidence, but I couldn't force the patching again. But I had another successfully patched instance in the same sever and I copied the Resource database data files to the \Binn folder and when I restarted the SQL, it worked!!
    I realized that Resource database may not have specific instance info within it like the Master.

  2. The blog gave me idea to Recover/Restore the Resource system database in SQL Server 2005 Thanks for Sharing it
    Dot Net Training in Chennai

  3. I just see the post i am so happy the post of information's.So I have really enjoyed and reading your blogs for these posts.Any way I’ll be subscribing to your feed and I hope you post again soon. SQL Server Training in Chennai

  4. Hi, Great.. Tutorial is just awesome..It is really helpful for a newbie like me.. I am a regular follower of your blog. Really very informative post you shared here. Kindly keep blogging. If anyone wants to become a .Net developer learn from Dot Net Training in Chennai. or learn thru ASP.NET Essential Training Online . Nowadays Dot Net has tons of job opportunities on various vertical industry.
    or Javascript Training in Chennai. Nowadays JavaScript has tons of job opportunities on various vertical industry.

  5. Nice information about test automation tools my sincere thanks for sharing post Please continue to share this post.

    weblogic tutorial

  6. Awe! What An Blog Very Helpful and interesting Really A great center for acquiring knowledge.Very Helpful Post And Explained Very Clearly About All the things.Very Helpful. Coming To Our Self We Provide Restaurant Equipment Parts .Really Thankfull For the blogger providing such a great information.Thank you. Have a Nice Day.


  7. This Blog is very helpful and useful,came to know that i should be strong in my basics and this blog helps me to improve it,Urgent Care Services Provided by Us.Thanks For Posting.I Am refereed by my friend to this blog and i also want to refer my other friends to this blog.

  8. Good article. It is very useful for me to learn and understand easily USMLE Thanks for posting.

  9. Thanks For Sharing Valuable Information, Very Useful For U Digital Transformation Consulting

  10. This was an nice and amazing and the given contents were very useful and the precision has given here is good.
    Java Training in Chennai

  11. Hello. This post couldn’t be written any better! Reading this post reminds me of my previous roommate. He always kept chatting about this. I will forward this page to him.
    bigdata training institute in bangalore

  12. Make money on kasino online and do not regret anything gamble casino with us Make money on kaino online and do not regret anything.

  13. У нас есть светодиодная лента цена которой выше или ниже рыночной в зависимости от того что вы ищете, дешевую или дорогую.

  14. Hey, would you mind if I share your blog with my twitter group? There’s a lot of folks that I think would enjoy your content. Please let me know. Thank you.
    machine learning Training in Chennai | machine learning Training institute in chennai | Best machine learning Training in chennai

  15. repliche christian louboutinIl campo Platinana di repliche espadrillas donne Christian Louboutin presenta un iconico design del tallone da 100 mm che assicura che le tue gambe raggiungano lo standard della top model. Il cinturino alla caviglia è realizzato in vernice e pelle opaca ed è piegato nella parte anteriore per rendere le dita affilate. Naturalmente, sono rifiniti con una base di vernice rossa firmata. Questi sono buoni come abiti in denim e cocktail.

  16. replica Cartier watches has recommended many classic and successful watch collections, such as replica Cartier ballon bleu de cartier watches, Cartier Santos and Cartier Ronde and so on. Every Cartier watch has its special charm, and Cartier watches are many people's dreams. Our shop provides different Cartier replica watches at very low prices. Just enjoy shopping here.

  17. replica longines watches uk have been a landmark of the industry since their brand began in 1832 in Saint-Imier. Today, replica mens longines 1832 watches the brand offers a wide variety of watches for men and women. Watches manufactured by Longines have been utilized throughout history for everything from exploration ventures to the expression of elegance. In addition to being famous for their ties to the racing and equestrian worlds, Longines watches are globally recognized for their timeless chic sophistication. we have a variety of Longines watches for sale, all new, beautiful, and guaranteed to be genuine. Even better, when shopping our Longines watches online, you know you are getting the best value. Browse our selection to find the Internet’s best Longines watch price.

  18. Use Alexander McQueen shoes to create a look from the feet.Cheap ALEXANDER MCQUEEN Shoes Wacky women's sneakers use jewellery replica mens ALEXANDER MCQUEEN embellishments and iconic skull motifs to instantly elevate any casual outfit to luxury, while glossy leather boots