DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations

Trending

  • Health Check Response Format for HTTP APIs
  • Tomorrow’s Cloud Today: Unpacking the Future of Cloud Computing
  • Design Patterns for Microservices: Ambassador, Anti-Corruption Layer, and Backends for Frontends
  • Managing Data Residency, the Demo

Trending

  • Health Check Response Format for HTTP APIs
  • Tomorrow’s Cloud Today: Unpacking the Future of Cloud Computing
  • Design Patterns for Microservices: Ambassador, Anti-Corruption Layer, and Backends for Frontends
  • Managing Data Residency, the Demo
  1. DZone
  2. Data Engineering
  3. Databases
  4. How to Utilize Emergency State for Corrupt SQL Server Database Repair

How to Utilize Emergency State for Corrupt SQL Server Database Repair

What is Emergency state of SQL Server and what are its uses?

John  Walker user avatar by
John Walker
·
Updated Jan. 29, 19 · Tutorial
Like (1)
Save
Tweet
Share
8.03K Views

Join the DZone community and get the full member experience.

Join For Free

“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.

Database sql

Opinions expressed by DZone contributors are their own.

Trending

  • Health Check Response Format for HTTP APIs
  • Tomorrow’s Cloud Today: Unpacking the Future of Cloud Computing
  • Design Patterns for Microservices: Ambassador, Anti-Corruption Layer, and Backends for Frontends
  • Managing Data Residency, the Demo

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com

Let's be friends: