Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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!!!
1 | SELECT DATABASEPROPERTYEX (N'Test', N'STATUS') AS N'Status'; |
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.
1 2 3 4 5 | EXEC SP_RESETSTATUS 'Test' ALTER DATABASE [Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO DBCC CHECKDB (N'Test') WITH NO_INFOMSGS, ALL_ERRORMSGS; GO |
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.
1 2 3 4 5 | EXEC SP_RESETSTATUS 'Test' ALTER DATABASE [Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO DBCC CHECKDB (N'Test',REPAIR_REBUILD) WITH NO_INFOMSGS, ALL_ERRORMSGS; GO |
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.
1 | select * from msdb..suspect_pages |
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.
1 2 | EXEC SP_RESETSTATUS 'Test' SELECT DATABASEPROPERTYEX (N'Test', N'STATUS') AS N'Status'; |
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”.
1 2 3 4 5 6 7 8 | EXEC SP_RESETSTATUS 'Test' ALTER DATABASE [Test] SET EMERGENCY; GO ALTER DATABASE [Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO DBCC CHECKDB (N'Test', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS; GO |
