How to Repair MS SQL Database from Suspect Mode

Introduction

The current article will explain what to do when a SQL Server database is in suspect mode.

What is the suspect mode in SQL Server?

The suspect mode is a critical error that makes the database inaccessible. You cannot read the tables or work with the data. If you have the database in this status, you need to repair your database immediately. We will explain here different ways to repair your database when it is in the suspect status.

Here you have some alternatives:

Method 1: The restore option

If you have a backup of your database (as a best practice you should backup your database daily or more often according to your emergency plan against disasters), you can recover your database using a backup that contains the database in a healthy status before the suspect problem.

To Restore your database, go to the SQL Server Management Studio, and go to the Object Explorer. Go to the Databases node and select the Restore option.

Select the Device option and press the Browse button.

In the Select backup devices Window, press the Add button.

In the Locate Backup Window, select your backup and press OK.

In the Restore Database windows, select the backup sets to restore and press OK.

Method 2: Restore the backup using the restore command

If you want to automate your restoration process, you could use a code similar to the following.

USE [master]

RESTORE DATABASE [stellar] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\stellar.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

RESTORE DATABASE [stellar] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\stellar.bak' WITH  FILE = 2,  NOUNLOAD,  STATS = 5

GO

The code will go to the master database and restore FILE 1 in NORECOVERY status and then restore the backup using FILE 2 showing the recovery process every 5 %.

Method 3: The DBCC CHECKDB command

Another method is to set the database in emergency mode.

To do that, in the SQL Server, run the following command.

ALTER DATABASE stellardb SET EMERGENCY

Set the database in single-user mode. To do that, in the SSMS go to the Object Explorer.

Run the following command line to set the database in single-user mode:

ALTER DATABASE stellardb SET SINGLE_USER WITH ROLLBACK IMMEDIATE

This command will set the database in single-user mode. This mode will allow a single user to handle the database. No other user can connect to the database. This mode is used when we need to repair the database.

Once it is in single-user mode, run the CHECKDB command using the REPAIR_DATA_LOSS

DBCC CHECKDB (stellardb, REPAIR_ALLOW_DATA_LOSS)

Finally, set the database back to multi-user mode.

ALTER DATABASE stellardb SET MULTI_USER

If everything is OK, this will bring the database online again.

Method 4: Stellar Repair for MS SQL

In some scenarios, if the database is in suspected mode, we do not have a backup or the DBCC command fails. To solve that, problem, we can use third-party MS SQL Database repair software. If you use this software, the database in use error will not occur because it is a newly rebuilt database.

The software recommended for this is Stellar Repair for MS SQL. This software has 3 editions.

• The corporate edition repairs SQL Server Data Files damaged.

• The Technician edition repairs the data files and also the backups. In this scenario we will fix the datafile of the suspected database.

• Finally, we have the Toolkit which also includes the analyze logs and convert database formats.  To work with log files.

To repair the database, install Stellar Repair for MS SQL. In this example, we will use the Technician edition, but my favorite is the Toolkit. Select the Repair MS SQL Database option to fix your database.

In SSMS, go to the Object Explorer. Right-click the database and select Tasks>Take Offline.

This option will disconnect the database in order to repair it.

In the Stellar Repair for MS SQL, press the Browse button and select the mdf file. If you do not know where is your file, press the Find button. Then press the Repair button.

You will have 2 options. One is the Standard Scan and the other is the Advanced Scan. Select the Standard Scan. The advanced option is used if you have problems with the Standard option. Finally press OK.

Once repaired, you can press the Save button.

 Your data can be saved in a New Database, Live Database, or other formats like CSV, Excel, or HTML files.

Select the option of your preference and press Next.

If you select the Live Database or New database the data of the database will be restored.

Conclusion

In this article, we provide different techniques to repair the database. The first option would be to restore the database from a backup. The second option would be to use the CHECKDB command. For this option use the Emergency mode and the single-user mode for the database.

Finally, there is a specialized software named Stellar DB for MS SQL which can repair your database in cases of suspected mode.