Thursday, May 19, 2011

Recover Suspect Database

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 solve this and bring back the database to normal. Try the below solution

Solution 1:
Step 1: If your database is marked suspect execute the below query,
Use Master
Exec 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')

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

1 comment:

  1. sql recovery has necessary probabilities for solving out issues related to corrupted or lost mdf files. It restores .mdf after power failures, hacker attacks. The tool starts under all available MS Windows working with big mdf files too.