There are many different reasons as to why databases can go into Suspect Mode in SQL Server 2005 / 2008 / 2012. Making regular backups or even syncing a database with another database on a different computer is vital, and it makes the process a lot quicker to recover from without loss of data.
How to recover SQL Server database from suspect mode?
If you don’t have any backups, don’t worry, there are ways you can recover the database and table data. The first thing I would do is run the following query:-
EXEC sp_resetstatus 'dbNAME';
ALTER DATABASE dbNAME SET EMERGENCY
DBCC checkdb('dbNAME')
ALTER DATABASE dbNAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('dbNAME', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE dbNAME SET MULTI_USER
ALTER DATABASE dbNAME SET online
// Replacing dbNAME with the name of the database you are trying to recover
If for some reason this does not work. I would recommend downloading Geeksnerds SQL Recovery Software. I have used this for many databases and have managed to recover databases including all data (without any loss!) using this software. I personally purchased a license for this software because the program worked great. If your database has inconsistencies, broken indexes or anything else, this program will fix it all!
Did this article help you? Let us know in the comments.
