Recover suspect database

Today I have working on recover suspect database. Sometimes you cannot recover database to normal(no data loss with read/write) mode, sometimes it’s recover with read-only mode depend on your database suspect ratio. Most of the time disk space problem or hard disks (located on database data file) issues can be cause of database suspect issue.

Note : most of the time (in your Development databases) you will noticed database shows as “recovery pending” mode. In this case you need to check SQL server error log if it’s has some error reported or not, if it is errors then you have to work with that error else if it’s only “information message no user action required” these situations you have to let them handle to SQL Server Engine.

if user action required then most of the time you can set database offline and then move back to online,

ALTER DATABASE <DB_NAME> SET OFFLINE WITH ROLLBACK IMMEDIATE;
Go
ALTER DATABASE <DB_NAME> SET ONLINE WITH ROLLBACK IMMEDIATE;
Go

if database is suspect then you can try following commands with depend on situations,

ALTER DATABASE <DB_NAME> SET EMERGENCY;

DBCC CHECKDB('<DB_NAME>');

ALTER DATABASE <DB_NAME> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DBCC CHECKDB ('<DB_NAME>', REPAIR_ALLOW_DATA_LOSS);

ALTER DATABASE <DB_NAME> SET MULTI_USER;

DBCC CHECKDB ('<DB_NAME>');