Recover SQL Database from Emergency Mode to Normal Mode

The EMERGENCY mode is a read-only status used to read data from databases in the RECOVERY PENDING/SUSPECT mode. Essentially, when a database status is changed to SUSPECT, it becomes inaccessible. Setting a database in EMERGENCY mode helps access the database and run a repair on it using DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS command.

Since the database is under repair, it might take you a few minutes to bring the database back online. But, if the database seems stuck in EMERGENCY mode, try to bring it ONLINE by executing this command:

ALTER DATABASE database_name SET ONLINE

Note: 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.

If the database is still in an EMERGENCY state, you will need to manually repair your database.

How to Recover SQL Database from Emergency Mode to Normal Mode?

Follow these steps to manually repair a SQL database and restore it to normal mode.

Step 1 – Check the Database State

Before proceeding, check if the database is still in EMERGENCY mode by running the following command:

SELECT name, state_desc FROM sys.databases WHERE name = Test_Database

This command will return the state of the database ‘Test_Database’.

As you can see the ‘Test_Database’ is in EMERGENCY state. So, let’s repair the database manually.

Step 2 – Repair the SQL Database Manually

Before starting the repair procedure, it’s important to switch the database to SINGLE_USER mode to prevent other users from using the database. Once the database is in SINGLE_USER mode, repair it by running DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS command.  

ALTER DATABASE Test_Database SET SINGLE_USER WITH ROLLBACK IMMEDIATE DBCC CHECKDB (Test_Database, REPAIR_ALLOW_DATA_LOSS)

The CHECKDB command runs successfully without any consistency errors.

Now put the database in MULTI_USER mode.

ALTER DATABASE Test_Database SET MULTI_USER

Now refresh the database and it will be back online.

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 backup is corrupt or unavailable, use a SQL recovery tool like Stellar Repair for MS SQL to restore your database to its original state. The software helps restore a SQL database by repairing .mdf/.ndf data files. 

The software allows saving the restored data to a new or an existing SQL database. Also, it supports saving the data in file formats like CSV, HTML, or XLS.

Conclusion

The EMERGENCY mode is used when a SQL Server database is stuck in RECOVERY PENDING or SUSPECT mode and is no longer accessible. Changing the database status to EMERGENCY helps read the data from the inaccessible database. A situation may arise when you fail to bring the database back ONLINE from EMERGENCY state. This may happen when the database is still getting repaired. So, wait for some time to check the database state. If it doesn’t return to its normal mode, set the database state to ONLINE. If this doesn’t work, you will need to manually repair the database to bring the database back to normal mode. Another option is to restore the database from a valid backup. If nothing works, use Stellar Repair for MS SQL to make the database accessible with minimal downtime.

Was this article helpful?

YES3

NO

About The Author

Charanjeet Kaur

Charanjeet is a Technical Content Writer at Stellar®who specializes in writing about databases, e-mail recovery, and e-mail migration solutions. She loves researching and developing content that helps database administrators, organizations and novices to fix multiple problems related to MS SQL and MySQL databases and Microsoft Exchange.