SQL Database Repair

How to Recover MS SQL Database from Suspect Mode?


Table of Content

    Summary: Read this post to find solutions to recover MS SQL database marked as suspect. It describes step-wise instructions 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 using a SQL Recovery tool.

    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 recover it during server startup.

    Recover MS SQL Database from Suspect Mode
    Figure 1: Database in Suspect Mode

    Check out the Infographic below for quick solutions to recover database from suspect mode in SQL Server 2008, and higher versions.

    Recover MS SQL Database from Suspect Mode

    When does SQL database goes to suspect mode?

    When SQL server suspects the primary filegroup of the database to be damaged or if the database file is missing, the database status is set to ‘Suspect’.

    Also, there are a wide range of errors that could result in SQL database in suspect mode. Some of them are listed as below:

    • System fails to open the device where the data or log file of SQL server resides.
    • SQL server crashes or restarts in the middle of a transaction, resulting in a corrupt or inaccessible transactions log file.
    • SQL Server tries to open a database, and the file belonging to that database is already open by anti-virus software installed on your system.
    • The database is terminated abnormally.
    • Lack of disk space.
    • SQL cannot complete a rollback or roll forward operation.
    • Database files are being held by the operating system, third-party backup software, etc.

    How to get SQL database out of suspect mode?

    NOTE: You can try restoring the database in suspect mode from a good known backup. If the backup is not available, proceed with the following steps.

    Follow the steps in sequence given below to recover MS SQL database from suspect mode:

    Step 1: Open SSMS and connect to the database.

    Connect to SQL Server Database in SSMS
    Figure 2: Connect to Database

    Step 2: Select the New Query option.

    Select New Query to open Query Window in SSMS
    Figure 3: 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:

    EXEC sp_resetstatus ‘db_name’;
    ALTER DATABASE db_name SET EMERGENCY
    Set SQL Database in Emergency Mode
    Figure 4: Set Database in Emergency Mode

    NOTE: If you cannot set the database in emergency mode, skip to the next solution.

    Step 4: A suspect database might not be corrupted. You can determine if the database is corrupted or not by running the following DBCC CHECKDB command.

    DBCC CHECKDB (‘database_name’)

    This statement will report any consistency errors (if found) in the database and will recommend running the minimum level of repair option to fix corruption.

    Before initiating the repair process, you must first set the database into ‘Single User Mode.’ Doing so will prevent other users from making any changes to the database during the repair process.

    Check SQL Database Consistency
    Figure 5: Check Database Consistency

    Step 5: Now, let’s bring the database into the Single User mode and roll back the previous transactions by executing the below command:

    ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    Set SQL Database to Single User Mode
    Figure 6: Set Database to Single_User Mode

    Step 6: Take a complete backup of the corrupted files to avoid chances of data loss.

    Step 7: After putting the db in SINGLE USER mode, try to fix the consistency errors using the REPAIR_REBUILD option of DBCC CHECKDB. This option can quickly repair missing rows in nonclustered indexes. In addition, you can use it for more time-consuming repair operation, such as rebuilding an index.

    DBCC CHECKDB (‘database_name’, REPAIR_REBUILD)

    However, if REPAIR_ALLOW_DATA_LOSS is suggested as minimum level of repair, then run DBCC CHECKDB with the suggested repair option. The syntax is as follows: 

    DBCC CHECKDB (‘database_name’, REPAIR_ALLOW_DATA_LOSS)
    Use DBCC CHKDB command with Repair Option
    Figure 7: Repair Database with DBCC CHECKDB

    Step 8: Bring the database into the Multi-User mode:

    ALTER DATABASE database_name SET MULTI_USER
    Set SQL Database to 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.

    Free Download for Windows

    The software can fix common SQL database corruption errors that occur due to reasons such as the database in suspect mode and several others. The software uses advanced algorithms to repair and restore SQL db from suspect mode to normal state (online).

    How to Recover SQL Database from Suspect Mode with the 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 the Select Database window, choose Browse or Search to select the SQL database file (.mdf) of the suspect database.

    Initial Screen of Stellar Repair for MS SQL
    Figure 9: Select Database File

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

    Repair selected corrupt database file with Stellar Repair for SQL software
    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.

    Preview repaired database components
    Figure 11: Preview window

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

    Select save option for saving repaired database 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.
    Add details to save repaired database file
    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. 

    Additional features of the software

    • Repairs corrupt MDF and NDF files.
    • Recovers tables, triggers, keys, indexes, stored procedures, defaults, rules, schema, etc.
    • 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

    Trusted by MVPS

    Conclusion

    This post discussed methods on ‘How 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 backup, use the EMERGENCY mode to access the database and repair it. However, you may fail to rollback the transactions that were active when database went into suspect mode. Also, using the REPAIR_ALLOW_DATA_LOSS option as the minimum repair level can lead to data loss. A better alternative is to use a specialized SQL database repair software that helps repair and restore the database from suspect to a normal state.

    Was this article helpful?

    No NO

    About The Author

    Charanjeet Kaur linkdin

    Charanjeet is a Technical Content Writer at Stellar®who specializes in writing about databases, e-mail recovery, and e-mail migration solutions. She loves researching and developing content that helps database administrators, organizations and novices to fix multiple problems related to MS SQL and MySQL databases and Microsoft Exchange.

    34 comments

    Leave a comment

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

    Image Captcha
    Refresh Image Captcha

    Enter Captcha Here :

    Related Posts

    WHY STELLAR® IS GLOBAL LEADER

    Why Choose Stellar?

    • 0M+

      Customers

    • 0+

      Years of Excellence

    • 0+

      R&D Engineers

    • 0+

      Countries

    • 0+

      PARTNERS

    • 0+

      Awards Received