Missing mssqlsystemresource database

Last night, one of our Development servers had a service pack get pushed to the server and automatically install.  I was unaware our patching process was automatically patching all of our servers which are not in Production.  Not a good idea, I would rather control this process and deploy the patches in a uniform process.

After the server was patched, the patch required a reboot.  When the server rebooted, Sql Server would not start up.  After reviewing the Windows Application log and Sql Server log, I quickly discovered the mssqlsystemresource database was missing.  I did search for it just in case it was placed somewhere else, but to no avail.

If the database was misplaced, I could have used this article just to move it back to the proper location, http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/01/22/error-945-severity-14-state-2-database-mssqlsystemresource-cannot-be-opened.aspx.

In my situation, the database was not misplaced, but it was gone.  Next I read the blog, http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/9ddc5d52-5835-405f-ad38-ac25022373f4/.  As you can read, I needed to find a server with the same version number to copy the files from the working server to the broken server.  I opened the Sql Server error log to get the version of Sql Server.  I scrambled around the office looking for a server with the same version number of the broken server.  No luck.

What is a DBA to do?  I have the version number but what do I need to install.  Thanks to our http://sqlserverbuilds.blogspot.com/ which shows use all the versions and links to the website for the download. I started to think how could I get this server back online, create a VM, install Sql Server 2008 R2 with cumulative update 1. I could then copy the files I needed from the new server to the old server.  Do I need to save the Master and MSDB databases?  This could take a while.  I need this server back online.

Instead, I went to Add/Remove program, found the Sql Server program and did a repair installation.  After the repair completed, my server was back online.  Yea!

But I am not finished, what do I need to do to prevent this same issue in the future.  The files look like a database, why can’t I just back them up in Sql Server like other databases. Microsoft does not let you do that.  But per this article, http://msdn.microsoft.com/en-us/library/ms190190.aspx, you can backup the files for emergency recover.  If needed, you can use the backup files for recovery but be carefully as the article states.  Finally, I think I am protected here.  Time to move on to the next issue.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s