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.

Published 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 comment

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

*


x

Related Posts

Script to get data file usage and autogrowth details
I've wrote this script to get data file allocated and used space. In addition to this I've also added auto growth setting details for the data fi...
Contained Database - SQL Server 2012
In SQL Server Denali we have new feature called contained database that will let you move your database easily. Understood? Whatever I said is a ...
Dropping a Subscriber from AG Publisher
Today one of my colleague reached out to me for an help to drop a subscriber from an publisher which is participating in Always On. We seen same...
powered by RelatedPosts