Recovering a SQL Server Database From SUSPECT Mode

How to Repair Database which is in SUSPECT mode in SQL Server 2005/2008

Morning you come to your office and you see your one of the critical database in SUSPECT mode and you are not able to do any action against that database. You need to repair the database at the earliest.

Why Database Is In SUSPECT Mode

There could be so many reasons for this but here are the main reasons that I can think of.

1.    Data files or log files are corrupt.

      2.    Database server was shut down improperly.
      3.    SQL cannot complete a rollback or rollforward operation.

If you want to find the exact reason why your database is in SUSPECT mode, you can use below query. This should give you the exact error message.

DBCC CHECKDB ('<Database Name>') WITH NO_INFOMSGS, ALL_ERRORMSGS

How to Repair the database and bring it online

Follow below steps to bring the database ONLINE and accessible.

1.    Change the status of your database by running following query

          EXEC sp_resetstatus '<Database Name>';

2.    Set the database in EMERGENCY mode by running following query

ALTER DATABASE <Database Name> SET EMERGENCY;

3.    Check the database for any inconsistency by executing below query

DBCC CHECKDB('<Database Name>');

4.    If you get any error after executing DBCC CHECKDB then immediately bring the database in SINGLE USER MODE by running following query. If no error found then you are done.

          ALTER DATABASE <Database Name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

5.    Take the backup of the database just to be in safe side.

6.    Run the following query as next step. Remember while using the below query, that uses REPAIR_ALLOW_DATA_LOSS, is a one way operation that is once the database is repaired all the actions performed by these queries can’t be undone. There is no way to go back to the previous state of the database. So as a precautionary step you should take backup of your database in step 5 mentioned above.

DBCC CHECKDB ('<Database Name>', REPAIR_ALLOW_DATA_LOSS);

7.    Finally bring the database in MULTI USER mode by running following query.

ALTER DATABASE <Database Name> SET MULTI_USER;

8.    At last step you verify the connectivity from your database. Now users should be able to connect to the database properly. If any data loss, you may need to get from the backup taken in step 5.

Now your database is ONLINE and accessible. Now Rock and have the coffee to RELAX.