Recover SQL Database from Emergency Mode to Normal Mode

Introduction

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:

  1. Any SQL Server version installed.
  2. The SQL Server Management Studio (SSMS) installed on the machine.
  3. Stellar Toolkit for MS SQL installer.

Getting started

 Setting in emergency mode.

In order to set in emergency mode, you can use the T-SQL commands like this:

If it is in emergency mode, it will show a yellow signed icon like this:

Database in EMERGENCY mode
Database in EMERGENCY Mode

If you want it to set to a normal state, the set online should work:

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:

SQL Toolkit Main Interface – Backup Recovery Software

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.

Stellar toolkit for MS SQL - Main interface
SQL Toolkit Main Interface – SQL Recovery Software

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.

Instruction to use SQL repair software
Instruction

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.

select corrupt MDF file
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:

Tree view
Tree View of the Database Objects

Finally, you can save the information in SQL Server, HTML, CSV or Excel:

Saving options
Saving Options

Conclusion

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!

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.