In this article, we will talk about the Emergency Mode status and how to recover a SQL Database from Emergency Mode to Normal Mode.
The emergency mode is a read-only status used to read data from databases in suspect status.
System Setup Requirements
The following requirements are necessary for this article:
- Any SQL Server version installed.
- The SQL Server Management Studio (SSMS) installed on the machine.
- Stellar Toolkit for MS SQL installer.
Setting in emergency mode.
In order to set in emergency mode, you can use the T-SQL commands like this:
ALTER DATABASE mydatabase SET EMERGENCY
If it is in emergency mode, it will show a yellow signed icon like this:
If you want it to set to a normal state, the set online should work:
ALTER DATABASE mydatabase SET ONLINE
However, if your database is damaged in suspect status, setting to ONLINE will just return to a database in suspect status and you will not be able to access the information.
Recover from a backup
If you are in emergency mode, try to look for the last backup in order to recover your information. If the database backup is in a healthy state, then you can restore the database without errors.
In case that your backup is corrupt then, you can restore the database from corrupt .BAK file using Stellar Toolkit for MS SQL.
Stellar Toolkit for MS SQL is the combination of three software which is very helpful for SQL DBAs and these are:
- Stellar Backup Extractor for MS SQL
- Stellar Repair for MS SQL
- Stellar Password Recovery for MS SQL
Select backup recovery software from the toolkit and repair corrupt .BAK file:
You may also read this knowledge base article: Steps to repair corrupt .BAK file
How to recover the information using DBCC tools
If your database is in suspect status, you may be able to recover the information by going to the emergency mode.
The emergency mode was introduced on SQL Server in the version 2005 until the SQL Server 2017 version.
If your database in suspect mode, you can set it to emergency mode. Once it is in emergency mode, you can try to repair your data using the DBCC commands using the CHECKDB command. For more information about the DBCC command, refer to the following link: DBCC CHECKDB (Transact-SQL)
If you do not have a backup or if the DBCC CHECKDB command cannot repair your databases, you can try a reliable solution recommended by MVPs and DBAs: The Stellar Repair for MS SQL.
This software allows to repair a damaged database when it is corrupt. The database can be corrupt for different reasons like malware, hacker attack, hardware failure and other reasons. Stellar Repair for MS SQL requires you to stop the SQL Server or take the corrupt database online. Otherwise, the database cannot be recovered because it is in use.
The MDF file contains the data of the SQL Server database. Each database can contain one MDF file and several or none ndf files.
The software repairs corrupt MDF file and restores all important data.
To restore, you need to specify the path of the MDF file. If you do not where the file is stored, you can use the search button to get the path.
The Repair button will repair the information. There is also an option to include the deleted rows.
You can restore tables, views, stored procedures, views, rules and more:
Finally, you can save the information in SQL Server, HTML, CSV or Excel:
In this article, we learned how to deal with a machine in Emergency mode. The emergency mode is used when the SQL Server database is in suspected status and we change to emergency mode to read the data. In order to recover the information, you can use the DBCC CHECKDB command with the repair_allow_data_loss option to repair the information.
Another option is to try to restore the information from the last backup. If your backup file is also corrupted then you can repair it using SQL backup recovery software. If you do not have a backup and the DBCC command does not work either, you can use the Stellar Repair for MS SQL. Take the free trial to see the preview of repairable SQL Database!