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?
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.
--Script 1: Create and populate database
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.
--Script 2: Check database status
SELECT DATABASEPROPERTYEX ('ForEmergency', 'STATUS') AS 'DBStatus' GO
In this state we are not able to access the data in the database.
--Script 3: Try to access data in suspect database
SELECT * FROM ForEmergency..students GO
If we try to access data in the suspect database, we will get the following error message.
Msg 945, Level 14, State 2, Line 2 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.
--Script 4: Set database to Emergency mode
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:
Auto shrink, Auto update Statistics
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.
--Script 5: Run DBCC CHECKDB command
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.
ALTER DATABASE ForEmergency SET MULTI_USER WITH ROLLBACK IMMEDIATE 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.
--Script 6: Set database to Normal mode
ALTER DATABASE ForEmergency SET ONLINE -- put the database online if corruption is fixed GO
ALTER DATABASE ForEmergency SET OFFLINE -- put the database offline GO
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
Last Update: 3/23/2011
About the author
Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.
@Ahmad. I have tried several scenarios on suspect database and was able to turn database to offline or emergency state. Just want to repeat a point mentioned at the end of tip "A database marked as RESTORING cannot be set to EMERGENCY. Also, such databases can not be set to OFFLINE or ONLINE either.". So i think there is no problem to change state of database from suspect to emergency/offline. If you get any problem in your case then please mention scenario and error statement here.
@Ahmed. A database in suspect state has many restictions. You can not access the data in suspect database, DBCC statements can not be used to repair the database. To provide a way to access the data and settings in the suspect database we can use emergency state. It provides a lot of flexiblity to repair and save data from suspect database. After a suspect database is put in emergency state, you can access data in it along with trouble shooting and verification statements like DBCC CHECKDB.