Recover Suspected SQL Databases in SQL 2005/2008

SQL Database Suspect Issues and Solution

Some time databases may be suspect due to insufficient space or HDD issues in SQL 2005/2008. we can recover those suspected databases from SQL 2005/2008 using below queries.

Execute Below queries one by one using Query Analyzer

Step 1

Stop and restart the instance of SQL Server using 

EXEC sp_resetstatus 'myDB';

Step 2 

EMERGENCY mode is not SINGLE_USER mode. A database needs to be in SINGLE_USER mode for repair to run and EMERGENCY mode allows multiple connections from members of the sysadmin role. 

ALTER DATABASE myDB SET EMERGENCY

Step 3

Note that the usual error log entry from running DBCC CHECKDB is preceded by ‘EMERGENCY MODE‘ this time.

DBCC checkdb('myDB')

Step 4

Alter Database with single user using below query

ALTER DATABASE myDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Step 5

Repair Database using

 DBCC CheckDB ('myDB', REPAIR_ALLOW_DATA_LOSS)

Step 6

 Alter database for Multiuser

ALTER DATABASE myDB SET MULTI_USER

Change myDB as your Suspected database Name.

Recover Query for Pincode Database Suspected : Example

EXEC sp_resetstatus 'pincode';

ALTER DATABASE pincode SET EMERGENCY

DBCC checkdb('pincode')

ALTER DATABASE pincode SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ('pincode', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE pincode SET MULTI_USER