In scenarios where you have no backups and the data or log files are damaged, the only way to access the database is with EMERGENCY mode.
The sequence of events was:
- Set the database to EMERGENCY mode.
- Use the undocumented and unsupported DBCC REBUILD_LOG command to build a new transaction log.
- RUn DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS option to fix up corruptions in the data files – both those may have caused the issue, and those caused by rebuilding the transaction log
- Figure out what data was lost or is inconsistent as far as your business logic is concerned
- Take the database out of EMERGENCY mode
- Perform root cause analysis
EMERGENCY mode example
EXEC sp_resetstatus 'SuspectDemoDB';
ALTER DATABASE SuspectDemoDB SET EMERGENCY;
GO
DBCC CHECKDB ('SuspectDemoDB',REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO
ALTER DATABASE [SuspectDemoDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [SuspectDemoDB] SET MULTI_USER
ALTER DATABASE SuspectDemoDB SET ONLINE
EMERGENCY mode is not SINGLE_USER mode. A database needs to be in SINGLE_USER mode for repair to run and EMERGENCY mode allows multiple connections from members of the sysadmin role. You can set SINGLE_USER mode as well as EMERGENCY mode – however, the sys.databases field state_desc will still just say EMERGENCY.
Btw, if the ALTER DATABASE statement to set the database into EMERGENCY mode fails, try setting the database OFFLINE and then ONLINE again first. Then set EMERGENCY mode again and retry the above script
How the state of the database looks like when in suspect mode:
SELECT [state_desc]
FROM sys.databases
WHERE [name] = N'SuspectDemoDB';
GO
state_desc
-----------------
RECOVERY_PENDING
How the state of the database looks like when it is back Online :
SELECT [state_desc]
FROM sys.databases
WHERE [name] = N'EmergencyDemo';
GO
state_desc
-----------
ONLINE
Please note that, you should only use this as a last resort, but if you do get yourself into trouble, you know there’s a command that should be able to help you.
Database Blog 