A suspect database is not the end of the world

I arrived at work Monday and learned that Saturday night's server crash destroyed my test environment's central objects database. Yes, the one that is supposed to go live in two weeks.

Everyone I know who works with Microsoft SQL Server said this has never happened to them. How reassuring! Every online forum implied these are very extreme circumstances and it's best to call Microsoft and have them help me over the phone. The last time I called was in ... what? ... 1993?

After experimenting with various solutions all afternoon, I found my situation rather simple: The MSSQL Service detected corruption, flagged the database as Suspect, and disabled access so hundreds of users don't log in and try to use it. Before I could view, modify, or otherwise access the objects, I had to reset the status from suspect to emergency mode.

You will not find emergency mode in the help index, and searching help has scant results. But once I had the right steps, it was fairly easy to reset the status, scan for errors, and rescue everything essential. Here are the two most useful articles:

Tibor Karaszi's Recommended Actions for Corrupt or Suspect Databases

A forum post that actually worked for me: http://www.sql.ru/faq/faq_topic.aspx?fid=123


Later, for fun, I called 1-800-MICROSOFT and found that I would have paid a $250 incident fee (credit card only) to resolve the problem. After-business-hours support costs extra.

That's not a bad value under the circumstances, but here's my advice to any budding SQL Server admins: Build a test server and a test database with lots of objects and data; start a lengthy write operation, wait a minute or so, and then unplug the machine. Now you have your very own suspect database to play with! If you fix it, you saved $250 and got a free education.