A guide to recover a database out from Suspect mode

• Elliswhite

  SSCommitted

  Points: 1783

  More actions

  May 22, 2014 at 4:34 am

  #1715626

  Follow simple steps to recover sql database suspect mode

  1. Change the status of your database. Suppose database name is “BluechipDB”

  EXEC sp_resetstatus '';

  Example: EXEC sp_resetstatus 'BlueChipDB'

  2. Set the database in “Emergency” mode

  ALTER DATABASE <Database Name> SET EMERGENCY;

  Example: ALTER DATABASE BlueChipDB SET EMERGENCY

  3. Check the database for any inconsistency

  DBCC CHECKDB('');

  Example: DBCC checkdb('BlueChipDB')

  4. If you get any error after executing DBCC CHECKDB then immediately bring the database in SINGLE USER MODE by running following query. If no error found then you need not execute the following query.

  ALTER DATABASE <Database Name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

  Example: ALTER DATABASE BlueChipDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

  5. For safety, take the backup of the database.

  6. Run the following query as next step. Remember while using the below query, that uses REPAIR_ALLOW_DATA_LOSS, is a one way operation that is once the database is repaired all the actions performed by these queries can’t be undone. There is no way to go back to the previous state of the database. So as a precautionary step you should take backup of your database in step 5 mentioned above.

  DBCC CHECKDB ('', REPAIR_ALLOW_DATA_LOSS);

  Example: DBCC CheckDB ('BlueChipDB', REPAIR_ALLOW_DATA_LOSS)

  7. Finally, bring the database in MULTI USER mode

  ALTER DATABASE <Database Name> SET MULTI_USER;

  ALTER DATABASE [BlueChipDB] SET MULTI_USER

  8. Refresh your database server and verify the connectivity of your database. Now users should be able to connect to the database properly. If any data loss, you can restore database – backup taken in step 5.

  SSMS Expert

• July 31, 2017 at 6:42 am

  This was removed by the editor as SPAM