How to recover SQL Server 2005 Database from SUSPECT Mode

A database may go to suspect mode and will not allow you to perform any operation unless and until you repair. The reasons for the database to be in suspect mode are:

Causes:

• A database can go in suspect mode for many reasons like improper shutdown of the database server, corruption of the database files etc. 
• If the DB recovery fails and one of the conditions for a delayed transaction does not qualify or the failure is not associated with a single database page (for example, damage to the transaction log is detected). In this situation, the engine changes the database state to SUSPECT
• To get the exact reason of a database going into suspect mode can be found using the following query,

DBCC CHECKDB ('suspect_db') WITH NO_INFOMSGS, ALL_ERRORMSGS

Output of the above query will give you an error messages.

Workaround:

You can find out the cause of the error by looking into the SQL server error log. If SQL Server has been restarted since the failed recovery, look at previous SQL Server error logs. If the recovery failed because of a persistent I/O error, a torn page, or other possible hardware problem, resolve the underlying hardware problem and restore the database by using a backup. If no backups are available, consider the repair options of DBCC CHECKDB.

• You can restore from a backup. Page-level restore is not an option here, because the problem is not specific to a database page.
• Use DBCC CHECKDB for emergency mode repair.
• You can change the database state to EMERGENCY and try to copy as much data as possible.

The final attempt to resolve the issue is, using ‘REPAIR_ALLOW_DATA_LOSS’.  First you can change the database status to the EMERGENCY mode. This could permit the system administrator read-only access to the database. Only members of the sysadmin fixed server role can set a database to the EMERGENCY state.

Then set the database to the single-user mode. Single user mode allows you to recover the damaged database.

Then run DBCC CheckDB command. This command checks the allocation, structural, logical integrity and errors of all the objects in the database. When you specify ‘REPAIR_ALLOW_DATA_LOSS’ as an argument of the DBCC CheckDB command, the procedure will check and repair the reported errors. But these repairs can cause some data to be lost.

EXEC sp_resetstatus 'suspect_db';

ALTER DATABASE 'suspect_db'

SET EMERGENCY

DBCC checkdb ('suspect_db')

ALTER DATABASE 'suspect_db'

SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ('suspect_db', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE 'suspect_db'

SET MULTI_USER

If the above script runs without any problem, you can bring your database back to the multi user mode.
Note: Using DATA LOSS repair options can lead to other problems. This is not a recommended way to recover the database. The database should be restored from a backup made earlier to the corruption, rather than repaired.