Click here to Skip to main content
1,837 members
Articles / Multimedia / SQL
Article

Recover SQL Server 2005 Database from SUSPECT Mode

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
14 Nov 2011CPOL 7.4K  
Recover SQL server 2005 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.

SQL
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.

SQL
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.

SQL
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:

SQL
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.

History

  • 30th September, 2009: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
Sri Lanka Sri Lanka
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
-- There are no messages in this forum --