SQL Server 2005 – Recover Database from SUSPECT Mode

Introduction

Sometimes, you may have experienced that your Microsoft SQL database is marked as SUSPECT. Database may go into SUSPECT mode because the primary filegroup is damaged and the database cannot be recovered during the startup of the SQL Server. Generally when the database is in SUSPECT mode, nobody can deal with the data.

Workaround

When the database is in SUSPECT mode, 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.

You can run the following SQL query to get the database to the EMERGENCY mode.

ALTER DATABASE  dbName  SET  EMERGENCY

After that, you set the database to the single-user mode. Single user mode allows you to recover the damaged database.

ALTER DATABASE  dbName   SET SINGLE_USER

Then you can 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.

DBCC CheckDB (dbName , REPAIR_ALLOW_DATA_LOSS)

If the above script runs without any problems, you can bring your database back to the multi user mode by running the following SQL command:

ALTER DATABASE  dbName  SET MULTI_USER

Recommendations

Using any 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 prior to the corruption, rather than repaired.

DBCC CheckDB command should be run on working databases at regular intervals to check for errors.