Click here to Skip to main content
Click here to Skip to main content

Recover SQL Server 2005 Database from SUSPECT Mode

, 30 Sep 2009
Rate this:
Please Sign up or sign in to vote.
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.

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.

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)

Share

About the Author

Aruna Randeni
Software Developer (Senior)
Sri Lanka Sri Lanka
No Biography provided

Comments and Discussions

 
Suggestionsql database recovery tool PinmemberMember 1085609931-May-14 1:26 
Questionmdf recovery Pinmemberpeggyburton19-May-14 20:52 
Questionsql database recovery PinmemberMember 1078496229-Apr-14 20:29 
QuestionMessage Automatically Removed Pingroupwalllerry29-Jul-13 20:03 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140827.1 | Last Updated 30 Sep 2009
Article Copyright 2009 by Aruna Randeni
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid