By: Atif Shehzad | Updated: 2011-03-23 | Comments (15) | Related: > Disaster Recovery
Problem
I noticed a new feature that was first introduced in SQL Server 2005, which is the Emergency state for a database. It looks that it may facilitate dealing with a suspect database. How can I use the Emergency state for a database effectively while working with a suspect database?
Solution
During a disaster recovery process, the Emergency state provides flexibility to perform several operations on a corrupt/suspect database. When a database is put in the Emergency state, it makes three major changes to the database configuration:
- Makes the database READ ONLY
- Restricts the access to members of sysadmin fixed server role only
- Logging is disabled (as database is READ ONLY)
As you can see it helps by putting the database in a read only and restricted access mode, but the real power of the Emergency state is the fact that you can use it for a Suspect database.
By putting a Suspect database in the Emergency state you have direct access to the data and many of the database configuration settings. Although a suspect state is not a prerequisite for putting a database in an Emergency state, this is probably the most useful time that you would want to use the Emergency state. When a database is in the Emergency state you can access your data, so you can export it to another database and you can even run a DBCC CHECKDB on the database to fix corruption.
In the following sections we will walk through how to use this feature.
Create a database in SUSPECT state
To show an example, we will create a suspect database. So first create a database with some data in it as shown below.
CREATE DATABASE ForEmergency
GO
USE ForEmergency
GO
CREATE TABLE students (rollNo TINYINT, name VARCHAR(15))
GO
INSERT INTO students (ROLLNO, NAME)
SELECT 1,'Atif' UNION ALL
SELECT 2,'Shehzad' UNION ALL
SELECT 3,'Shahid' UNION ALL
SELECT 4,'Mohsin' UNION ALL
SELECT 5,'Asad'
GO
There is more than one approach to turn a database to a suspect state, but if you follow the below steps you can create a suspect database.
- Stop SQL Server service
- Open the .ldf file for database ForEmergency with a text editor, make a change to some data in the file and save and close the file
- Start SQL Server Service
Note: if you are using Windows 7 or Windows Server 2008 you may need to copy the file to another location and then paste back to the original location after modification.
The database should now have a Suspect status and it can be verified by running the following statement.
SELECT DATABASEPROPERTYEX ('ForEmergency', 'STATUS') AS 'DBStatus'
GO
In this state we are not able to access the data in the database.
SELECT * FROM ForEmergency..students
GO
If we try to access data in the suspect database, we will get the following error message.
Database 'ForEmergency' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Msg 208, Level 16, State 1, Line 2
Invalid object name 'students'.
Put database in EMERGENCY state
As we are not able to access the database in Suspect state, let's convert it to Emergency state.
ALTER DATABASE ForEmergency SET EMERGENCY
GO
Emergency status can be confirmed through Script 2 above or in SSMS as shown below.
Options in EMERGENCY state
After a suspect database is put in Emergency state, it is now accessible with some limitations. The database may be accessed as READ ONLY, so you can export the data out of the database by using any method like BCP, DTS or SELECT INTO statement. In addition, various database settings may be manipulated including:
- Recovery model
- Auto shrink, Auto update Statistics
- Cursors Settings
- ANSI NULL settings
These settings may be accessed in the Options frame for the database in SSMS as shown below.
Using DBCC CHECKDB
DBCC statements can also be used when the database is in an emergency state. We can run DBCC CHECKDB to check for corruption. In addition, we can use the REPAIR_ALLOW_DATA_LOSS option. When using the REPAIR_ALLOW_DATA_LOSS option, damaged data or indexes may be removed to make the database physically consistent. As the option indicates, by using this option anomalies of the database will be removed which may cause some data loss. In addition, if there are problems with transaction log corruption, this command will rebuild the log file.
Note: to avoid any data loss, try all possible recovery options especially restoration from a database backup before using this option.
In the below code we are using the DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS option to fix the corruption for the database. Also note that in order to fix the corruption we need to put the database in single user mode.
ALTER DATABASE ForEmergency SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DBCC CHECKDB (ForEmergency, REPAIR_ALLOW_DATA_LOSS)
GO
In our case the transaction log is corrupt and it will be rebuilt. If the command is successful the database will be put in an Online state and could be switched back to multi-user mode by using this command.
GO
Now the database is online and accessible in a physically consistent state. The rebuilt transactional log may result in a loss of transactional consistency in the target database, so it would be a good idea to run DBCC CHECKCONSTRAINTS to find any anomalies in data relations.
Put database in ONLINE/OFFLINE state
To leave the Emergency state and turn to either an ONLINE or OFFLINE state you can use these commands.
ALTER DATABASE ForEmergency SET ONLINE -- put the database online if corruption is fixed
GO
ALTER DATABASE ForEmergency SET OFFLINE -- put the database offline
GO
Next Steps
Although the Emergency state is a great option, it is not always a sufficient approach for large databases. Pulling out a large amount of data and making it ready for use is problaby not a feasible option the amount of time you are given to resolve the problem. So the most important thing to remember is that there is no alternate for a good and sound backup strategy. In the event of any type of disaster proper backups would save a lot of time and effort in the recovery process.
Some other notes:
- A database marked as RESTORING cannot be set to EMERGENCY. Also, such databases can not be set to OFFLINE or ONLINE either.
- Click here to read about different database states in SQL Server 2005/2008
About the author
Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2011-03-23
