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