Summary: This post is intended for users looking for solutions to recover MS SQL database (db) from Suspect Mode. It describes steps to fix the SQL server suspect database issue by running Transact-SQL (T-SQL) commands in SQL Server Management Studio (SSMS). Also, it provides an alternative solution to restore the database by using a SQL Recovery tool.
Sometimes when connecting to a SQL Server database, you may find that the database is marked as Suspect.
When SQL database goes into suspect mode, it becomes inaccessible. In such a situation, you will neither be able to connect to the database nor able to recover it during server startup.
Step 8: Bring the database into the Multi-User mode:
Figure 8: Set Database to Multi-User Mode
ALTER DATABASE database_name SET MULTI_USER
Step 9: Refresh the database server.
After completing these steps, you should be able to connect to the database. In case of any data loss, you’ll have the db backup to restore from (Step 6).
What if this solution doesn’t work?
If your server database file has turned severely corrupt, the above-mentioned steps may fail to revive the database. At this point, try restoring the database by using Stellar Repair for MS SQL.
The software can fix common SQL database corruption errors that occur due to reasons such as database in suspect mode and several others. The software uses advanced algorithms to repair SQL db and restores it to normal state (online).
Steps to Recover ‘SQL Database from Suspect Mode’ with Stellar SQL Recovery Tool
NOTE: Make sure to close the server instance before running Stellar Repair for MS SQL software.
Step 1: Download, install and run Stellar Repair for MS SQL software.
Step 2: From Select Database window, choose Browse or Search to select SQL database file (.mdf) of the suspect database.
Figure 9: Select Database File
Step 3: Once the file is selected, hit Repair.
Figure 10- Repair Selected File
NOTE: Make sure to uncheck the ‘Include Deleted Records’ checkbox if you don’t want the deleted records to be recovered.
Step 4: Preview the repaired MDF file for recoverable SQL server database objects.
Figure 11: Preview window
Step 5: Click Save on File menu to save the repaired file.
Figure 12: File menu
Step 6: From Save Database window, perform the following:
Select MDF under Save As.
Save the repaired file in New database or Live database.
Fill in the details under Connect To Server.
Figure 13: Save Database window
Step 7: Click Save.
Open SSMS and attach the db (containing the repaired MDF file). You will be able to access the database.
Supports MS SQL 2019, 2017, 2016, 2014, 2012, 2008 R2, and lower versions.
Provides multiple saving options to save the repaired database including MS SQL (MDF), CSV, HTML, and XLS.
The software is trusted by Microsoft MVPs
This post discussed methods on ‘How to recover MS SQL database from suspect mode’. It outlined the manual steps to fix the database in suspect mode issue. Also, it explained how you can use Stellar Repair for MS SQL software to repair and restore the suspect database.