How to Recover SQL Server Database from SUSPECT Mode

Question

I am facing the issue on my MS SQL 2008 Database, which is marked as suspect. I have a database backup but it's not updated (older, more than a week). Is there a any other way to repair suspect SQL Server database.

And can anybody explain what is the cause for database goes to SUSPECT mode?

Edwin J. Hoffer · Accepted Answer

Some of the basic reasons for SQL Server Database to go into suspect mode are: n n SQL Server Database file or transaction log could have been corrupted n Unexpected shutdown of SQL Server database  n Database could not be opened due to the lack of disk space n nTo recover SQL Server database from suspect moden n At very first change the status of your database  nnn nnn Then set the SQL Server database in emergency mode  nnn nnn Check the database for any inconsistency  nnn nnn Then execute the

Mariya Beckham · Answer

First of all make sure that you do not detach suspected database nThen execute the following query: n

EXEC sp_resetstatus ' DatabaseName' nALTER DATABASE DatabaseName SET EMERGENCYnDBCC checkdb(' DatabaseName')nALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATEnDBCC CheckDB (' DatabaseName', REPAIR_ALLOW_DATA_LOSS)nALTER DATABASE DatabaseName SET MULTI_USERn

nn