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?
Here’s a flash back on how we used to do in the past:
- Enable modifications to system catalogs.
- Change the status of the database in SysDatabases system object to 32768
- Restart SQL Server services (This would put the database in Emergency mode)
- You would then transfer the data from your database in to another database
You no longer have to perform the above tedious process anymore as Microsoft has made the process much simpler
SQL Server no longer allows making any changes to the system tables even by system admins. 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.
Best Practice: It is strongly recommended that you perform regular backups of your database to avoid any data loss.
Putting the database in EMERGENCY mode:
ALTER DATABASE <SQLDB> SET EMERGENCY GO
Once the database is in emergency mode, you should now export the data from the SQLDB database in to some other database.
Once you have been able to successfully export the database to another DB, you may put the database back to normal state:
ALTER DATABASE SQLDB SET ONLINE GO
EMERGENCY Mode Creates Missing Log File
One of the feature of EMERGENCY mode is that when you run DBCC CHECKDB on a user database that doesnt have a log file (ex: disk on which log file was residing crashed and cant be recovered), CHECKDB will rebuild the log file automatically for that user database when it is run while the database is in EMERGENCY mode.
Keep the following in mind when putting the database in Emergency mode:
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.
Recent Comments