Recovering an MS SQL Database From Suspect Mode: Step-By-Step Guide
This article will discuss what causes the database to go to suspect mode, and we will describe step-wise instructions to fix the ‘SQL server suspect database.
Join the DZone community and get the full member experience.Join For Free
The SQL database is always in one of the modes: online, offline, suspect, storing, recovery pending, and emergency. When the SQL database recovery fails, or the database becomes damaged or corrupted, it moves to suspect mode. When the database is marked as SUSPECT mode, the database is unavailable for user access. You can recover the database from the suspected state using different commands in SSMS. In this article, we’ll cover what causes the database to go to suspect mode and its recovery methods. Also, we’ll outline an advanced MS SQL repair tool to help you quickly restore the database from suspect mode without data loss.
Reasons for SQL Server Marked As” Suspect Mode”
The SQL Server database suspect mode indicates the recovery process has started but failed to finish. The database states may become suspect due to several reasons. Some of them are below:
- Database file corruption.
- Damaged Database’s primary file group.
- Unavailable database files.
- The database is terminated abnormally.
- System disk space is limited.
- Missing transaction log files.
- SQL server crashes in the middle of a transaction.
Methods To Recover SQL Database From the Suspect Mode
To restore the SQL database from the suspect mode, you can follow the below methods:
1. Restore SQL Backup
You can run the SQL commands to restore the SQL database backup. Here’s how:
- Launch SSMS and connect to the database engine.
- Click New Query from the standard bar.
- In the code window, type the below SQL command:
RESTORE DATABASE moni
FROM DISK = 'Z:\SQLServerBackups\moni.bak' ;
2. Repair MS SQL Database
SQL database can marked as suspect mode due to corruption in it. You can run the DBCC CHECKDB to identify and repair the corruption in the database. Here’s how to do so:
Before troubleshooting, you need to set a database to Emergency mode.
- In SSMS, click New Query.
- In the Query editor window, type the below command to turn off the suspect flag on the database and set it to Emergency mode:
EXEC sp_resetstatus mon;
ALTER DATABASE mon SET EMERGENCY
- The database marked as suspect might not be corrupted. So you can check whether the database is damaged or not with the DBCC CHECKDB command as shown below:
DBCC CHECKDB (mon)
- On executing the CHECKDB command, you will see consistency errors (if any) in the database. It will also recommend executing the repair option to fix corruption.
- Next, fix the consistency errors using the CHECKDB command.
- Before initiating the repair process, first set the database into Single User mode and roll back previous transactions to free resources. Use the below command:
ALTER DATABASE mon SET SINGLE_USER WITH ROLLBACK IMMEDIATE
- Run the DBCC CHECKDB command with the REPAIR ALLOW DATA LOSS option.
Note, before executing this command, first create the backup of the SQL database
DBCC CHECKDB (mon, REPAIR_ALLOW_DATA_LOSS)
- Bring back the database in Multi-user mode using the below command:
ALTER DATABASE mon SET MULTI_USER
- Change the EMERGENCY mode to ONLINE mode.
ALTER DATABASE mon SET ONLINE
- Next, refresh the database server.
- Check whether you can connect the SQL database.
Recommended Method To Recover MS SQL Database From Suspect Mode
In most cases, the above methods can help to change the database suspect mode to Normal. However, sometimes, these methods need to be revised due to certain scenarios, such as when the SQL database is severely corrupted. In such a case, you can opt for a professional MS SQL database repair tool like Stellar Repair for MS SQL. The tool can repair severely corrupt/damaged NDF/MDF files with complete integrity and precision.
The tool can fix common SQL database corruption errors, including the MS database in suspected mode issues. It uses enhanced algorithms to repair SQL databases. It helps to restore a MS SQL database from suspect mode to the online state.
There can be multiple reasons why your database changes to suspect mode. In this state of the database, you may fail to connect or access the SQL database. You can try the above-discussed methods, such as backup recovery, running 'DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS,' etc, to make the database available. If the severe corruption has turned the SQL database into suspect mode, you can try an advanced SQL repair tool like Stellar Repair for MS SQL. It can quickly repair and restore severely corrupted databases without any data loss.
Opinions expressed by DZone contributors are their own.