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.
