DBA - How to bring database online from recovery/suspect mode?

Bringing a database online from recovery or suspect mode involves several steps. Here’s a summary of the process:

Steps to Recover a Database from Suspect Mode:

1. Check the SQL Server Error Log:

Identify the root cause of the issue by examining the SQL Server error log .

2.Set the Database to Emergency Mode:

Execute the following command to set the database to emergency mode:

ALTER DATABASE Test_Database SET EMERGENCY;

3.Run DBCC CHECKDB:

Run the DBCC CHECKDB command to check the integrity of the database and identify errors:

DBCC CHECKDB Test_Database WITH NO_INFOMSGS;

4.Set the Database to Single User Mode:

Set the database to single-user mode to perform repairs:

ALTER DATABASE Test_Database  SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

5.Repair the Database:

Run the recommended repair option (REPAIR_REBUILD or REPAIR_ALLOW_DATA_LOSS):

DBCC CHECKDB Test_Database REPAIR_ALLOW_DATA_LOSS;

6.Set the Database to Multi-User Mode:

After repairs, set the database back to multi-user mode:

ALTER DATABASE Test_DatabaseSET MULTI_USER;

Additional Solutions:

•Restore from Backup: If you have a recent backup, restoring the database from the backup can be a quick solution 

•Check for Disk Space: Ensure there is sufficient disk space available, as lack of space can cause the database to go into suspect mode .

Example Commands:

-- Set database to emergency mode

ALTER DATABASE TestDatabase SET EMERGENCY;

-- Check database integrity

DBCC CHECKDB TestDatabase WITH NO_INFOMSGS;

-- Set database to single user mode

ALTER DATABASE TestDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

-- Repair database

DBCC CHECKDB TestDatabase  REPAIR_ALLOW_DATA_LOSS;

-- Set database to multi-user mode

ALTER DATABASE TestDatabase  SET MULTI_USER;