In my earlier articles, I’ve explained the various methods to detach a database and the methods to take a database offline and back to online. In this article we’ll see how to move a database to emergency mode for repair or troubleshooting purpose and taking it out of emergency mode to online state.
Move Database To Emergency Mode
To move a database to emergency mode use the ALTER DATABASE statement along with SET EMERGENCY option.Use one of the below statements to move the database to emergency state. Once the database is set to emergency, the database icon before the database name in SSMS object explorer turns into red.
ALTER DATABASE [Database-Name] SET EMERGENCY GO -- OR ALTER DATABASE [Database-Name] SET EMERGENCY WITH ROLLBACK IMMEDIATE GO -- OR ALTER DATABASE [Database-Name] SET EMERGENCY WITH ROLLBACK AFTER 15 SECONDS GO
Take Database Out Of Emergency Mode To Online
To take a database out of emergency mode to normal online mode use the ALTER DATABASE statement along with SET ONLINE options. follow the below statement to bring back the database online from emergency state.
ALTER DATABASE [Database-Name] SET ONLINE GO
Related
- Difference between detaching, taking offline and moving to emergency mode of a database.
Reference
- Details on various database status in Microsoft Docs.