How to Recover Database From Emergency Mode in SQL Server


There are many scenarios SQL Server database becomes inaccessible because it is marked as Suspected. When the database is in suspected mode, user cannot access and cannot perform transaction on that data until it comes back to online mode. In addition, if the backup is not generated before database corruption, user cannot perform any action.

In order to resolve this problem user can go for Emergency mode that gives Read Only permission to the user. Emergency Mode facilitates the use of suspected database effectively.

In this article, we will discuss the different aspects of using SQL Server Database Emergency Mode.

Some Reasons That Turns SQL Server Database ‘Suspected’

  • If transaction log files are corrupted or missing
  • Database .mdf and .ndf file corruption
  • Server unexpected shutdown
  • Hardware issue and Power failure
  • Virus attack on database

Using SQL Server Emergency Mode for Suspected Database

At times when user is not equipped with a backup and their log files are also damaged, in such case the only one way to access the data in Emergency mode. It only allows Read Only access and restricts the access to members.

The method is fully documented and supports SQL Server 2005 and all new versions. It uses DBCC CHECKDB to get the database back online.

NOTE:

  • The operations performed during Emergency Mode are a one-way operation and user cannot roll it back. Therefore, the advice is to create a copy of damaged database before performing any operation on it.
  • Suspect state is not prerequisite for using Emergency Mode. Although, putting Emergency Mode is most useful when there suspect data is present in database.

How Does SQL Server Database Emergency Mode Works?

Checking the Suspected State of Database

Firstly, user needs to check database is in suspected state by accessing data from database. By using command:

SELECT *FROM database_name..table_name

If the database is in suspect mode, it will show the error message to the user.

Putting the SQL Server Data in Emergency Mode

To put suspected data in emergency mode, user needs to execute the following command:

ALTER DATABASE database_name SET EMERGENCY

Use DBCC CHECKDB Tool to Check the Integrity

User can use DBCC statements with REPAIR_ALLOW_DATA_LOSS option that will remove all damaged and inconsistent data and results physically consistent database. While removing the inconsistency from database, user may lose some data.

User can run below mentioned command to run DBCC CHECKDB.

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

Switch the Database Back to Multi-user

While using emergency mode, it restricts the database access to limited user, after repairing the data, one can switch it back to multi user access.

ALTER DATABASE database_name SET MULTI_USER WITH ROLLBACK IMMEDIATE

Put the Database in Online State

In the final step user needs to put the database in online state in order to leave SQL Server database emergency mode.

ALTER DATABASE database_name SET ONLINE

Limitations – This approach is not suitable for large databases, user needs an alternative solution for handling such errors. To tackle the limitations of system defined methods, user can go for a third party tool namely SysTools SQL Recovery.

Advertisements
This entry was posted in Erros and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s