The Situation
I never had a case of the Monday’s as I did today. Here’s the scenario, Data Center (DC) goes dark (yes, complete power failure). Don’t be mistaken, this is a well respected DC used by many large corporations and hosting providers. And yes, I was livid.
When a DC goes dark, every piece of hardware is immediately shut down. This is not a safe power down either, it’s just like unplugging your desktop computer from the electrical outlet while it is running. Most technical folks understand the significance of this, because the after affects can be all sorts of corruption, hardware failures, etc.
The Impact
The power outage lasted probably 2 or so hours. Upon power being restored, most equipment came right back up. Servers were online, alerts were being sent, etc. Unfortunately some applications were complaining about databases not being found. So now I’m starting to get concerned. I was able to successfully connect to the SQL Server in which the databases were stored. The data drive was still mapped and in-tact. I could not, however, start SSMS. Upon checking services, I notice the SQL Server service was stopped. I tried to start it, and then when things became interesting. The SQL Server service would not start. Upon investigation, I found that my master database was corrupt. Those familiar with SQL Server know the master database is the “brain”. It keeps track of all the databases on the given instance, as well as credentials and database access.
I read several posts stating copying a master database from another SQL instance of the same version would allow the service to start. Not having another instance of the same version, I merely installed a second instance on the affected database server. I then copied the master database and log to the affected instance. Unfortunately I was still unable to start the service. In this case it was because the SQL Server account did not have permissions to this particular master database. I changed the service logon account temporarily to local system. Sure enough, the service started. I was them able to start SSMS.
Upon starting SSMS and connecting to my instance, of course none of the database I’m used to seeing on that instance showed up. Again, this is because the master database I am using was from a new SQL 2012 install.
Restore the master database
This task proved to be a bit challenging. First off, let it be known that I do not claim to have deep technical knowledge with all facets of SQL Server. I do know quite a bit, however, this was new. The first thing I tried was to restore the master database from SSMS. The message I received stated that SQL Server needs to be in single user mode in order to restore the master database. Getting SQL Server to start in single user mode was fairly easy. Just start SQL Server configuration manager, then right-click on the instance and add the –m parameter. I found a good article here. I then started SSMS just fine. Then I tried to restore the database. When I did, I received yet another error, that stated another admin was logged in, and two admins could not be logged in at the same time in single user mode. I tried a few things, however, nothing worked. I then found a TechNet article discussing using sqldmd to perform the restore. So I gave this a try. The article is here. Ultimately I ran a command similar to this:
C:> sqlcmd 1> RESTORE DATABASE master FROM DISK = 'Z:SQLServerBackupsmaster.bak' WITH REPLACE; 2> GO
The restore took place so quick I was not sure it worked. I noticed 400+ pages were updated, therefore I was optimistic.
End result
Next I took the database out of single user mode, and started SSMS to see where things stood. I was elated that I was able to login to SSMS without issue AND that all my databases were attached! needless to say this 6hr ordeal appears to be coming to a successful end.
Thanks for reading!
Feel free to comment if you know of a better method, or if this was helpful.
-Dan