How To Recover Suspect Database in SQL Server

 

The fall of the database into the suspect mode is the fearful dream of every dba. It is necessary to take precautions with a robust backup strategy.

First of all, you should not detach the database, which is suspect. SQL Server does not allow us to detach in 2014. I can’t remember which version of this feature was provided. But I wanted to make this reminder, because it allowed to detach in some previous versions.

Important Note: When your database falls into suspect mode, confirm it with the following command. After making the confirmation that the database is suspect, the first thing we need to do before attempting to recover must be to get a backup of suspected database!!!

When I run the query, I see that the status of the database is suspect.

There are several methods to recover the database.

 

Method 1:

With the help of the following script, we find the reason why the database falls into the suspect mode.

If you see that the indexes are suspect after running the command above, you can recover your database with the help of the following script . Or you can drop and create related indexes.

 

Method 2:

Return from the latest healthy backup.

Instead of returning from Full backup, you can find only the suspect pages and restore these pages. You can find suspect pages with the help of the following script.

You can use the “Page Restore On SQL Server” article for Page Restore. Once the Page Restore is complete, you should check that the suspect problem is resolved by using the DBCC CHECKDB (N’Test’) command. If the error has not returned after the CHECKDB process is complete, you should also perform the final check with the following script.

If your problem is not solved with page restore, you have to restore from the full backup. You can read the article “Backup Restore On SQL Server” for backup restore operations.

 

Method 3:

If the problem is not caused by indexes like in 1. Method, we can recover our database with the help of the following script. But in this option there will be data loss.

So you can use this method as a last resort if you cannot save your database with Method 1 and Method 2.

For details of the DBCC CHECKDB command, I suggest you read my article “DBCC CHECKDB Command On SQL Server”.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

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