MSSQL: How to recovery Database from SUSPECT Mode

Database generally Database moves to SUSPECT State due to following Reasons

• Database is corrupted
• ldf file is corrupted
• Disk Hardware Failure or Lack of Disk Space
• SQL Server does not have sufficient memory to Start SQL Server

Step 1

Never detach a suspect database it end up without re attach .as it is a Suspect database we can’t
take Backup of it. take the database into EMERGENCY MODE

– Take the database into Emergency mode
ALTER DATABASE [Database Name] SET EMERGENCY
GO

Step 2

set the database into single user mode using
– Change into single user mode
ALTER DATABASE [Database Name] SET SINGLE_USER

Step 3

Run DBCC Checkdb with allow data loss, by using this command all the open transactions will
be deleted ,
– Then run DBCC CheckDB command. Warning! REPAIR_ALLOW_DATA_LOSS can cause
some data to be lost!.
DBCC CheckDB (<[Database Name]> , REPAIR_ALLOW_DATA_LOSS)

Step 4

when the database repair is done set the database in multi user mode using

– Change from multiuser

ALTER DATABASE dbName SET MULTI_USER

Altogether

ALTER DATABASE ReportServer SET EMERGENCY
ALTER DATABASE ReportServer SET SINGLE_USER
DBCC CheckDB (ReportServer , REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE ReportServer SET MULTI_USER