When accessing MS SQL database you can face multiple errors and issues. The “MS SQL database is marked as suspect mode” is one such issue that can make the database inaccessible or unavailable. The database can change the state to suspect mode due to several reasons. In this article, we’ll discuss the causes of suspect mode in MS SQL and methods to recover MS SQL database from suspect mode.
Table of Contents
What causes MS SQL database to change its state to suspect mode?
The database may move into suspect mode due to multiple reasons. Some of them are below:
- Your system hosting the MDF/NDF file is out of space.
- The MDF/NDF file is corrupted or damaged.
- The SQL server crashes while working on the database.
- The database crashes in the middle of a transaction.
How to recover MS SQL database from suspect mode?
Follow the below methods to change the mode of the database from suspect to normal mode :
Method 1-Restore your Backup:
If you have an updated backup (.bak) file, then you can restore the .bak file using SSMS or T-SQL commands:
Before restoring the backup, make sure you have all permissions and privileges to restore the backup file
Use SSMS to restore the BAK file
- First, open SSMS and then connect to the SQL Server instance.
- Locate the Object Explorer and then click the Server Name.
- Go to Databases and then open the affected database you need to restore in SQL Server.
- Right-click on the Database and then select Restore Database.
- In the Restore Database wizard, on General window, choose the Database and then click on the drop-down to choose the database you need to restore.
- Click on the Device option and then choose ellipses(…) to search for the backup file.
- In the Select Backup devices wizard, click File as backup media and then select Add.
- Next select the backup file you need to restore and then click OK.
- A window with restore progress is displayed.
- After this, you will see the message “‘The restore of database completed successfully’, then click OK.
Alternatively, you can use T-SQL to restore the backup to recover the SQL database from suspect mode.
Method 2-Repair your database using DBCC CHECKDB command
If your backup is corrupted or unavailable, then you can use SQL utility-DBCC CHECKDB command to repair your database. It can help you restore database from suspect mode to online mode. Refer the below steps to repair MDF/NDF file using the DBCC CHECKDB utility:
- As your database is inaccessible in suspect mode so, first you can change the mode to EMERGENCY mode to access the database. For this, in SSMS , connect to the Server instance, click New Query button and type:
EXEC sp_resetstatus ‘database_name’;
ALTER DATABASE ‘database_name’ SET EMERGENCY
- Next, run the following DBCC CHECKDB command to check the integrity/consistency of the overall database:
DBCC CHECKDB (‘database_name’)
- After this, change the database mode to Single User mode so that no other users can update the database during the repair process. Here is how to do so:
ALTER DATABASE ‘database_name’ SET SINGLE_USER WITH ROLLBACK IMMEDIATE
- Next, run the DBCC CHECKDB command using the suggested repair option. If the REPAIR_ALLOW_DATA_LOSS is suggested as a minimum level of repair, then run the command as given below:
Note: The REPAIR_ALLOW_DATA_LOSS can cause data loss, so it is recommended to create a backup of the database.
DBCC CHECKDB (‘database_name’, REPAIR_ALLOW_DATA_LOSS)
- Once it is repaired, change the mode to Multi-user mode by executing the following command:
ALTER DATABASE ‘database_name’ SET MULTI_USER
- Next, you will be able to connect to the database.
What if nothing works to recover database from suspect mode?
Running the CHECKDB command with the REPAIR_ALLOW_DATA_LOSS option can lead to data loss. A better method is to use an advanced SQL recovery tool like Stellar Repair for MS SQL. This tool can help you regain access to the SQL database without adding data loss risk.
Read Also: How to Become an Android Developer (Step-By-Step Guide)
It can restore your database file (MDF/NDF), including deleted objects, partitioned tables, primary key, foreign key, etc., with all its data intact. Also, it is an easier and more convenient tool with a simple user interface to repair your SQL database.
Some highlighting features of Stellar Repair for MS SQL:
- Repair damaged or corrupted MDF/NDF files of all sizes.
- Recovers all data of the SQL database, including schemas, procedures, tables, keys, etc.
- Offers multiple options for saving the recovered SQL database file– CSV, XLS and HTML.
- Supports MS SQL Server 2022, MS SQL Server 2019, and earlier versions.
Conclusion:
The MS SQL Database becomes inaccessible after entering into a SUSPECT state. If your database seems stuck in suspect mode, try the methods discussed in the article. And if nothing works for you or you want to recover a corrupt database quickly, then using Stellar Repair for MS SQL can prove to be a handy tool. In the event of database corruption, the SQL recovery tool helps repair database files (.mdf/.ndf) and restore the database to its original form with utmost accuracy. The tool supports all the latest versions of the MS SQL server.