“The SQL Guy” Post #17: Recovering Data Using SQL Server Emergency Mode

Remember those days when the database would go in to suspect mode and you had to perform various steps to recover the database by putting the database in the emergency mode and then extracting the data out of that emergency database?

These are the high level steps you had to perform in previous (<SQL2K5) versions of SQL Server

1. Enable modifications to system catalogs.

2. Change the status of the database in SysDatabases system object to 32768

3. Restart SQL Server services (Once restarted database would appear in Emergency mode)

4. You would then transfer the data from your database in to another database

This process was not an easy process and involved manually updating system tables. Often this information was not publicly available.

This process has changed since the release of SQL2K5 onwards, putting the user database in EMERGENCY mode is now a supported and documented feature in the current release of SQL Server (unlike SQL Server 2000/7.0/6.x where you had to change the status of SYSDATABASES)

With the release of SQL2K5, SQL Server no longer allows making any changes to the system tables even by SA’s. Making even a slightest change to system objects is restricted. However, there may be situations when you would need to put the database into EMERGENCY mode and export/extract the data out of the corrupt database in to another database and in order to do that, SQL Server now provides a new feature as part of the ALTER DATABASE statement that would enable System Administrators to put the database in to EMERGENCY mode.

In the below example, we will see how this can be done using the ALTER DATABASE statement. Note: This is simply an example of how to put the database in emergency mode and how to bring it back to its normal state. In a real life scenario, once the database is in suspect mode and you put it in EMERGENCY mode, you may not be able to put it back in the normal state due to corruption. In this situation, you must export the data to another database.

IMPORTANT: It is strongly recommended that you perform regular backups of your database to avoid any data loss.

NOTE: One of the good feature of SQL Server EMERGENCY mode is that when you run DBCC CHECKDB on a user database that doesn’t have a log file (ex: disk on which log file(s) were residing crashed and can’t be recovered), CHECKDB will rebuild the log file automatically for that user database when it is run while the database is in EMERGENCY mode.

THINGS TO KEEP IN MIND:

When the database is put in the EMERGENCY mode, it is marked as READ_ONLY and logging is disabled. Only members of SYSADMIN role can set and are allowed to access the database when in emergency mode.

You can verify if the database is in emergency mode or not by examining the “STATE” and “STATE_DESC” columns in the sys.databases catalog view or from the “STATUS” property of the DATABASEPROPERTYEX function.

IMPORTANT: It is strongly recommended that you perform regular backups of your database to avoid any data loss.