How to Utilize Emergency State for Corrupt SQL Server Database Repair

“I could not use SQL Server database for some days due to numerous error messages. Whenever I try to access the data, one or another error message pops up. I think that the database has become severely corrupt and that is why these errors are appearing. I have also noticed that the database has gone into the Suspect mode. I do not know how to fix a database that is in suspect mode. I read somewhere that I should use Emergency state for corrupt SQL Server database that has gone into Suspect Mode. If this is true, then can anyone tell me how to enable Emergency state to repair SQL database?”

Emergency state is a common term for SQL Server users. This is often used to when SQL database goes into the Suspect mode due to severe corruption. Many SQL users have mentioned in SQL forums that they have heard of the term but do not know the process well. In order to help those users, we will describe how to set Emergency state for corrupt SQL Server for repairing purpose. Before that, we will learn about Emergency state in detail.

What Is Emergency State of SQL Server and What Are Its Uses?

Emergency state is one of the states of SQL database that got introduced at first in SQL Server 2005. This state can be enabled only manually and users with database admin status can perform this task. Emergency state provides facilities that allow users to perform certain operations is a corrupt database (or when the database has gone into the Suspect Mode). That is why this has become a popular way of disaster recovery. Emergency state of SQL does these to the database configuration:

• Enables READ ONLY mode for the database
• Disables logging into the SQL database
• Only users with “sysadmin fixed server role” can have access to it

By using the Emergency state, users can access data and many configuration settings of the database directly. Users should not think to put a database in the Emergency state, it has to be in the suspect mode first. But, suspect mode is surely the most suitable time for enabling Emergency state. By putting the database in the Emergency state, users can run DBCC CHECKDB command to fix database corruption and access their data in order to export the database to another database.

How to Use Emergency State to Repair a Corrupt SQL Database

The whole process of fixing database corruption by putting it is Emergency state consists of multiple steps. Here, we will discuss all the steps one by one.

1. Verify SQL Database Suspect Mode

The first step of this process is to check if the database is in the suspect mode or not. For this, users have to run this script. In case of suspect mode of data, users will get an error message.

 SELECT * FROM database_name.table_name 

2. Put SQL Server Database in Emergency State

After the confirmation, it is the right time to enable the emergency state for the database. Run this script to set the Emergency mode:

 ALTER DATABASE database_name SET EMERGENCY 

3. Repair Corrupt SQL Server Database

As the database is in Emergency mode now, its corruptions can be fixed using some commands. First, put the database into mandatory single user mode and then remove corruptions.

ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

DBCC CHECKDB(database_name,REPAIR_ALLOW_DATA_LOSS)

Go

4. Enable Multi-User Access to the Database

When the database is repaired, use this command to switch it from single user mode to multi-user mode.

 ALTER DATABASE database_name SET MULTI_USER WITH ROLLBACK IMMEDIATE 

5. Set the Database Online

Put your database online from the Emergency state as the database is not corrupt anymore. Use the below script for this task:

 ALTER DATABASE database_name SET ONLINE 

Looking for a Secure Way to Repair Corrupt Database?

Emergency state lets users repair the corrupt SQL database using DBCC CHECKDB command. But the manual method does not guarantee to fix the database corruptions without any data loss. Rather, this particular command contains a high risk of data loss. If this technique does not work or you cannot afford to lose any data, use SQL Recovery Tool. This application is designed to remove all types of corruptions from your database and that too without losing any data. The tool can be used for all SQL Server database including SQL 2017.

Conclusion

This write-up talked about the process of enabling Emergency state for corrupt SQL Server database repairing. It also explained the definition and usage of Emergency state in SQL Server. It is expected that users will be able to use this process during database corruption. Otherwise, they can utilize the software we have mentioned above.