Summary: If your database is marked as SUSPECT, you can try accessing the database by setting it to EMERGENCY mode. However, you may face a situation when the database is stuck in the EMERGENCY mode. This blog will discuss how you can recover a SQL database from EMERGENCY Mode to NORMAL Mode. You may also try using Stellar Repair for MS SQL software to quickly restore the inaccessible database to its original (normal) state, without data loss. Download the free trial version of the SQL repair tool and check the preview of the repairable database and its objects.
The EMERGENCY mode is a read-only status used to read data from databases in the SUSPECT mode. Essentially, when a database status is changed to SUSPECT, it becomes inaccessible. In order to access the information from the database and repair it, you need to set the database status to EMERGENCY. Once it is in EMERGENCY mode, repair your SQL data files using the DBCC CHECKDB command. For more information on how to repair a SQL database using the DBCC CHECKDB command, refer to this link.
Now, let’s discuss how to recover a SQL database from EMERGENCY mode to NORMAL mode.
Before we proceed with the steps to move a database from EMERGENCY to NORMAL mode, ensure that your system meets these requirements:
- Any SQL Server version installed.
- SQL Server Management Studio (SSMS) must be installed on the machine.
How to Recover SQL Database from Emergency Mode to Normal Mode?
Here we’ll demonstrate how to set a database in EMERGENCY mode and return it to NORMAL mode.
Step 1 – Setting Database in EMERGENCY Mode
To set a database in emergency mode, use the following T-SQL command:
|ALTER DATABASE mydatabase SET EMERGENCY|
Once the database is in EMERGENCY mode, it will show a yellow signed icon like this:
Figure 1 – Database in EMERGENCY Mode
Step 2 – Bringing Database ONLINE
To recover a database in EMERGENCY mode to NORMAL mode, you must set the database to ONLINE state. For this, execute the following statement:
|ALTER DATABASE mydatabase SET ONLINE|
However, if your database goes into SUSPECT state due to corruption, setting it to ONLINE state will just return a database in SUSPECT status. In that case, you cannot access the database information unless you bring it back to its normal state.
Alternative to Recover Database from EMERGENCY to NORMAL Mode
If your database is stuck in EMERGENCY mode, try to look for the last backup to restore the database and recover its information. If the database backup is in a healthy state, you can restore the database without errors. However, if the backup is corrupt or unavailable, running DBCC CHECKDB with the repair options may help.
If the repair options don’t work for you, try using a reliable solution recommended by MVPs and DBAs: The Stellar Repair for MS SQL software. The software helps restore a SQL database by repairing .mdf/.ndf data files. Stellar Repair for MS SQL requires you to stop the SQL Server or take the corrupt database online; this is important because you cannot recover the database unless it is in use.
Figure 2 – Instruction Window
The MDF file contains the data of the SQL Server database. Each database can have one .mdf file and several or none .ndf files.
The software repairs a corrupt MDF file and restores all its data.
To perform the restore process, you need to specify the path of the MDF file. If you do not know where the file is stored, you can use the Search button to locate the file.
Figure 3 – Select MDF file
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:
Figure 4 – Tree View of the Database Objects
Finally, you can save the information in SQL Server, HTML, CSV, or XLS file formats:
Figure 5 – Saving Options
The EMERGENCY mode is used when the SQL Server database is in SUSPECT mode. We change the database status to EMERGENCY to read the data from the inaccessible database. 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 the DBCC command. If this command does not work either, you can use the Stellar Repair for MS SQL software to bring the database ONLINE. Take the free trial to see the preview of the repairable SQL Database.