Article 3.6.1H : How to Recover SQL Database from Suspect Mode?

Article 3.6.1H : How to Recover SQL Database from Suspect Mode?

Steps to Fix the SQL Server Database Suspect Mode Error

Here are the steps to change ‘SQL database suspect mode to normal mode’ :

  1. Open SQL Server Management Studio and connect your database
  2. Select the New Query option
  3. Turn off the suspect flag on the database and set it to EMERGENCY

EXEC sp_resetstatus ‘db_name’;
ALTER DATABASE db_name SET EMERGENCY

  1. Perform a consistency check on the master database

DBCC CHECKDB (‘database_name’)

  1. Bring the database into the Single User mode and roll back the previous transactions

ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

  1. Take a complete backup of the database
  2. Attempt the Database Repair allowing some data loss

DBCC CHECKDB (‘database_name’, REPAIR_ALLOW_DATA_LOSS)

  1. Bring the database into the Multi-User mode

ALTER DATABASE database_name SET MULTI_USER

  1. Refresh the database server and verify the connectivity of database
Note: if any data loss due to step 7 then restore it from backup.