Fix: Recovering from a suspect Microsoft SQL Server database

Recently, (NO) thanks to our wonderful host, we had to recover from a RAID failure which caused severe data corruption on our dedicated server’s RAID array. And upon recovering from the fiasco and spending several hours getting the server back up, we discovered multiple lost files and worst of all, corrupted database files!

And we had both MySQL and Microsoft SQL Server 2005 Express running on the server. We managed to backup the critical MySQL databases up to date, but unfortunately for the Microsoft SQL Server databases, we didn’t had the chance to back it up before the server went down.

So, we were forced to reuse the file that was recovered from the hard disk.

While I managed to attach the mssql database file back to the new installation of MS SQL Server on the server that was just rebuild, it seems that I was hit with a suspect database error and therefore, the database would not load! A sure sign of corruption, but perhaps if it’s just a suspect problem, it may be fixed!

And I was right. A quick search on (wonderful) Google provided me with the sweetest lines of SQL codes I’ve ever seen. Mainly because if it works, I’d be a very happy man. 🙂 And indeed it worked.

EXEC sp_resetstatus ‘database_name’;
ALTER DATABASE database_name SET EMERGENCY;
DBCC checkdb(‘database_name’);
ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC checkdb(‘database_name’, REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE database_name SET MULTI_USER

What more can you ask for? The basic idea, reading from the code I found above, is to reset the status of the suspect database, the check the database and then repair it. Note that the repair mode used here allows data loss to enforce it to get back to the normal status. So, do note that the will be the possibility of loss data.

So there you go, a really quick fix if you unfortunately come upon a suspect MS SQL Server database.