How to Recover MS SQL Database from Suspect Mode?

Suspect mode is one of the states of MS SQL Server database. The SQL database goes into suspect mode when the database recovery fails or the primary data files are either missing or corrupted. In suspect mode, the database becomes inaccessible. This means you can neither connect to the database nor recover it during server startup. The database will remain in the suspect mode until it is restored to the normal state.

Below, we’ll discuss why the SQL database goes into suspect mode and how to recover the database from suspect mode.

Why SQL Database goes into Suspect Mode?

The database may go into suspect mode due to one or more of the following reasons:

• The system’s hard disk where database is stored is out of space.
• The database file is corrupted or damaged.
• The primary file group of the database is corrupted or missing.
• The transaction log file is inaccessible or corrupted.
• MS SQL server shuts down or crashes in the middle of a transaction.
• The database is terminated unexpectedly.
• SQL is unable to complete a rollback or roll-forward operation.

Solutions to Recover SQL Database from Suspect Mode

You can first check the database status. For this, open the SQL Server Management Studio (SSMS), go to the Object Explorer, and check the database. If the database is in suspect mode, check the SQL Server error log in SQL Server Management Studio (SSMS). This log contains all user-defined events and system events that will help you find the exact cause behind the database is in suspect mode issue. If corruption is the reason behind the database in the suspect mode issue, then follow the given methods.

Method 1: Restore Database from Backup

If the database is corrupted or damaged, the easiest solution is to restore the database from backup. If you have an updated database backup file, then you can restore the backup file. To restore the database backup file, follow these steps:

• Open SQL Server Management Studio (SSMS) and connect to your server instance.
• From the standard bar, click ‘New Query’.
• In the ‘Query Editor’ window, type the below command:

RESTORE DATABASE databasetest13  
FROM DISK = 'Z:\SQLServerBackups\databasetest13.bak' ;

• Next, try connecting to the database.

If your backup file is not updated or available, then follow the next method.

Method 2: Use DBCC CHECKDB Command to Repair the Database

You can use the DBCC CHECKDB command to repair the damaged SQL database and restore it from suspect mode to online mode. Following are the steps to repair the database using the DBCC CHECKDB command:

Note: It is suggested to create a backup of the SQL database before running this command.

Step 1: Open SSMS and connect to the server instance.

Figure 1: Connect to Server Instance

Step 2: Select the New Query option.

Figure 2: Select New Query

Step 3: In the Query Editor window, enter the following code to turn off the suspect flag on the database and set it to EMERGENCY mode:

EXEC sp_resetstatus ‘db_name’;
ALTER DATABASE db_name SET EMERGENCY

Figure 3: Set Database in Emergency Mode

Note: If you are unable to set the database in Emergency mode, skip to the next solution.

Step 4: Now, run the following DBCC CHECKDB command. This command will check the integrity/consistency of the database. It will also report the consistency errors (if found) in the database and recommend the repair option.

DBCC CHECKDB (‘database_name’)

Figure 4: Check Database Consistency

Step 5: Next, set the database to Single User mode to prevent other users from making any changes to the database during the repair process. To set the database to Single User mode and roll back the previous transactions, execute the below command:

ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Figure 5: Set Database to Single_User Mode

Step 6: After putting the database in SINGLE USER mode, run the DBCC CHECKDB command with the suggested repair option. If the REPAIR_REBUILD option is suggested, then run DBCC CHECKDB command as given below. This option can quickly repair missing rows in non-clustered indexes.

DBCC CHECKDB (‘database_name’, REPAIR_REBUILD)

However, if the REPAIR_ALLOW_DATA_LOSS is suggested as a minimum level of repair, then run the command as given below:

DBCC CHECKDB (‘database_name’, REPAIR_ALLOW_DATA_LOSS)

Figure 6: Repair Database with DBCC CHECKDB

Note: The DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS option usually helps to bring the database to a consistent state. But, this command results in data loss.

Step 7: When the database is repaired, bring it back to Multi-User mode. To do so, execute the below command:

ALTER DATABASE database_name SET MULTI_USER

Figure 7: Set Database to Multi-User Mode

Now, you should be able to connect to the database.

Method 3: Use a Professional MS SQL Repair Tool

The DBCC CHECKDB command can repair the database and helps you recover the database from suspect mode. However, it does not guarantee the accuracy of repaired data in the database. To avoid data loss and quickly repair the corrupt SQL database, you can use a professional SQL database repair tool, like Stellar Repair for MS SQL. The tool can repair the database (MDF/NDF) file and recovers all the objects, including tables, triggers, stored procedures, and indexes with complete precision and integrity. It can help fix common SQL database corruption errors. The software is compatible with SQL 2022, 2019, 2017, and earlier versions.

Let’s see how to repair SQL database with Stellar Repair for MS SQL and recover it from suspect mode.

Note: Close the server instance before running the software.

Step 1: Download, install, and run Stellar Repair for MS SQL software.

Step 2: From the Select Database window, choose Browse or Search to select the SQL database file (.mdf).

Step 3: Once the file is selected, click Repair.

Note: Uncheck the ‘Include Deleted Records’ checkbox if you don’t want the deleted records to be recovered.

Step 4: When the repair process is complete, it will display a “Selected MS SQL database repaired successfully” message. Click OK.

Step 5: The software will present all the recoverable SQL database objects. You can preview the objects.

Step 6: Click Save on File menu to save the repaired file.

Step 7: On the Save Database window, under the Saving Option, select New Database, Live Database, or Other Formats. Click Next.

Step 8: On the next window, fill in the details required under Connect To Server and click on the Next option.

Step 9: Select the Save Mode and click Save.

When the database is repaired, open SSMS and attach the repaired database file. You will now be able to access the database. 

Conclusion

Above, we have discussed the solutions to recover MS SQL database from suspect mode. The best approach is to restore the database from a healthy backup. If you don’t have a healthy or an updated backup, then use the DBCC CHECKDB command to repair it. However, the DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS option can result in data loss. A better option is to use a specialized SQL database repair software, such as Stellar Repair for MS SQL that can repair the database without any data loss and helps you recover the database from suspect mode.

FAQ