What to do if database is in corrupt or suspect state ?

Leave a comment (0) Go to comments

I just received a call from one of my friend, who is in panic situation. He was running a SQL Server 2000 Server where one of the hosted database get in suspect state. This is what I recommend him

  1. Relax don’t panic have a cup of coffee.
  2. Find out why this happened. Check eventlog, SQL Server Error Log
  3. If there is a hardware/file issues , ensure the faulty hardware is replaced or File are in place.
  4. Backup the log; assumption: log backup schedule is in place. If the database is suspect, you must use the NO_TRUNCATE option for the BACKUP command.
  5. You might want to do a file backup of the mdf and ldf files, for extra safety
  6. If the database is suspect and 2000 or earlier, a secondary option can be to try to “un-suspect” the database using sp_resetstatus.
  7. If the database isn’t suspect, then DBCC CHECKDB with a REPAIR option might be a secondary option but this will often result in loss of data. Search Books Online and KB for the any error numbers returned.
  8. If nothing works, to cut down your downtime, it’s time to restore from your last good backup.
  9. But in case,  you still might have a chance to retrieve any changed data that’s in the database. SQL Server 2005 introduces a new database state, called EMERGENCY. This state puts the database into read-only, single-user mode, with access allowed by members of the sysadmin role only. This will make your database accessible and let you get in to pull out any data before doing a full restore.

To put a database into EMERGENCY state, use the following T-SQL, substituting the name of your database for ‘YourDatabase’:


EOF - What to do if database is in corrupt or suspect state ?, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

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


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.