EMERGENCY mode is used when the transaction log for the database has been damaged and no backups are available to restore from. In this case, regular repairs do not work—repairs are fully logged and this cannot occur if the transaction log is damaged.
In SQL Server 2000 and earlier, EMERGENCY mode was undocumented and was used to allow the transaction log to be rebuilt using the undocumented DBCC REBUILD_LOG command. Unfortunately, this procedure became publicized on the Internet but usually without all necessary steps. For this reason, the dev team decided to add a documented and supported method of rebuilding a transaction log and recovering the database in SQL Server 2005. The feature is called emergency mode repair and its mechanism is unchanged for SQL Server 2008.
When the database is in EMERGENCY mode and SINGLE_USER mode, and DBCC CHECKDB is run with the REPAIR_ALLOW_DATA_LOSS option, the following steps are taken:
EXEC sp_resetstatus 'yourDBname'; ALTER DATABASE yourDBname SET EMERGENCY DBCC checkdb('yourDBname') ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS) ALTER DATABASE yourDBname SET MULTI_USER