Repair/Restore Option for Database in Suspect Mode

If a database is in Suspect Mode…

1. Try to bring the database back online.
2. If that does not work, try to repair the database with data loss.
3. If that does not work, you have to restore the database from a backup file.

-- Error, unable to bring back online.
ALTER DATABASE [MyDB01] SET ONLINE
GO

-- Set database to Emergency state.
ALTER DATABASE [MyDB01] SET EMERGENCY

-- Check for errors.
DBCC CheckDB ([MyDB01])

-- Need to set to single user mode before running repair.
ALTER DATABASE [MyDB01] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

-- Repair database with data lost.
DBCC CheckDB ([MyDB01], REPAIR_ALLOW_DATA_LOSS)

/*
Msg 5028, Level 16, State 4, Line 1
The system could not activate enough of the database to rebuild the log.
DBCC results for 'MyDB01'.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'MyDB01'.
Msg 7909, Level 20, State 1, Line 1
The emergency-mode repair failed. You must restore from backup.
*/

-- After successfully repair, set database back to multi user mode.
ALTER DATABASE [MyDB01] SET MULTI_USER

-- If repair not successfully, restore database from backup file.

-- Database Backup File Info.
RESTORE FILELISTONLY  
   FROM DISK = 'C:\MSSQL\MyDB01\MyDB01_backup.bak' 

-- Restore database from backup file.
RESTORE DATABASE [MyDB01] FROM DISK = 'C:\MSSQL\MyDB01\MyDB01_backup.bak' 
WITH FILE = 1, REPLACE, maxtransfersize = 4194302, buffercount = 96, stats = 5