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.
留学生网课代上为学生们提供了解决学业困扰的优质选择。通过网课代上,学生们可以解决学业压力大、时间有限等问题,获得更多的学习资源,提升自己的学术能力。然而,使用网课代上 http://www.meeloun.com/ 也需要留学生们具备一定的自主学习能力和学术诚信意识。相信随着留学生网课代上的发展,学生们将能够更好地适应国外的学习环境,取得更好的学业成就。
Repairing an MS SQL database from suspect mode requires precision and expertise. Oteemo’s foundation is rooted in the belief that impactful enterprise transformations are crafted from the ground up. Utilizing advanced cloud IT solutions, we guide businesses in modernizing their digital capabilities, ensuring a competitive edge and superior service for both commercial enterprises and federal agencies.