• Sat. Sep 25th, 2021

Recovering from a Suspect Database

Suspect database in SQL Server

I hate when a database in SQL Server goes into Suspect mode.

If you a database administrator in SQL Server, one of the worst things that could happen to you is that you arrive at work one day and find out that one of your databases is in ‘Suspect’ mode in SQL Server.

What does Suspect mode mean, basically it means that your database is not working and you better hope you have a backup SQL Server. 🙂

You should always have a good backup and restore strategy in place for recovering from a Suspect database, but if you have not, there might be a temporary way of you turning on the database again to pull data out.
You should not use the below code as a way to recover then use the database because from my experience; your database is corrupted, (even if you run DBCC CHECKDB as it tells you everything is ok)

Recovering From Suspect Mode Code In SQL Server

EXEC sp_resetstatus <sql server database name>;
ALTER DATABASE <sql server database name> SET EMERGENCY
DBCC checkdb(<sql server database name>)
DBCC CheckDB (<sql server database name>, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE <sql server database name> SET MULTI_USER

Once you have run this code SQL Server., you are freely available to have a look at your data. My suggestion is that you copy all of the data into another database and use the new database in the future. Moving the data may take some time, but it will be worth it in the end. If you use the Suspect database in the future, what could happen is that the Suspect database might become corrupted again and there will be no fix for it. Also if you continue to use the Suspect database, your backups also have hidden faults in there.
After you have moved your data to a new database, I would suggest you investigate why the database went into the ‘Suspect’ mode in the first place SQL Server.

This could have happened from a power failure or a corrupted hard drive, but you need to make sure you find the root-cause because it could happen again.

Did this help? leave a comment below.


Gary Cowan

I have been working with SQL Server for 20 years and have administrated and developed software solutions with SQL Server from SQL Server 6.5 through to SQ Server 2019. I have MCTS, MCP, MCDBA and MCITP Qualifications in SQL Server.

Leave a Reply

Your email address will not be published. Required fields are marked *