Repair SQL Server Database marked as Suspect or Corrupted

http://www.sqlservercurry.com/2011/03/repair-sql-server-database-marked-as.html

There can be many reasons for a SQL Server database to go in a suspect mode when you connect to it - such as the device going offline, unavailability of database files, improper shutdown etc. Consider that you have a database named ‘test’ which is in suspect mode

You can bring it online using the following steps:

1. Reset the suspect flag
2. Set the database to emergency mode so that it becomes read only and not accessible to others
3. Check the integrity among all the objects
4. Set the database to single user mode
5. Repair the errors
6. Set the database to multi user mode, so that it can now be accessed by others

Here is the code to do the above tasks:

EXEC sp_resetstatus 'test'

ALTER DATABASE test SET EMERGENCY

DBCC CheckDB ('test')

ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ('test', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE test SET MULTI_USER