Recover Suspect SQL Server Database
Where I work we have about 160 instances of SQL Server Express running on laptops and as you can imagine these database instances tend to become corrupted from time to time. One of the most frequent issues is that a database becomes suspect. Obviously your first choice should be to restore a backup and the transaction logs since the last backup. However if you don't have this (as I never do on my SQL Server Express instances) this is usually pretty simple to take care of by putting the database into single user mode and then emergency mode and then running a dbcc checkdb. Here is the T-SQL you should use. NOTE: You should be in the master database when you run this.
1) Alter database mydb set Single_User
2) Alter database mydb set Emergency
3) DBCC CheckDB ('mydb') -- This will tell you the Repair level to use
4) DBCC CheckDB ('mydb', { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD })
5) Alter database mydb set Multi_User
After a successful DBCC CheckDB the database is back in a consistent state and it's status is now online. For more information on DBCC CheckDB go here.
I normally run the REPAIR_ALLOW_DATA_LOSS as the users running the express instance generally don't have much critical data so if we lose some data it isn't that much of an issue. NOTE: Running DBCC CheckDB can take a long time to run and you may want to consider running it with the "noindex" option.
Let me know if you have any other strategies for recovering databases in suspect mode.