Categories
Troubleshooting

How to Recover from Suspect Database

Many DBAs would have seen the database marked as suspect. If a database is marked suspect it cannot be accessed and hence nothing can be performed in the database. In this article I am going to explain how to overcome this and bring the database back to normalcy. There are three possible Workarounds to bring the database online and to retrieve the data. First try the Workaround 1 and if it fails go to Workaround 2 if that doesn’t yield the desired result try the Workaround 3.

WORKAROUND 1:

Step 1 : If your database is marked suspect execute the below query,

USE Master
GO
Sp_resetstatus 'Database Name'

The above command will reset the status flag of the suspect database.

Step 2: Once the command is executed you need to restart the SQL services and check the status of the database.

Step 3: Execute the below query to check the integrity of the database

DBCC Checkdb('Database Name')

WORKAROUND 2:

Step 1: But if you want to avoid any data loss you can bring the suspect database into emergency mode as follows,

ALTER DATABASE DATABASE dbName
SET EMERGENCY

This will bring the Database into emergency mode so that you can Export the data using SSIS to another Test server.

Step 2: One you exported you can drop the suspect database and restore it from the latest available backup. Those objects Exported to Test server could be scripted and rerun in the source database after restoring from the backup so that we can minimize the data loss if any.

By Deepak

Microsoft Most Valuable Professional (MVP) award-winning Database Designer with 5 years of experience in designing, administering, developing & performance tuning relational databases and data warehouse.

I possess strong leadership skills experience by serving as the Chapter Leader of Professional Association for SQL Server (PASS) which has around 50 members.

I am passionate about solving business problems using my technical skills and hence I am interested in the field of Technology Consulting in Information Management.

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

*