Move Database To Emergency Mode In SQL Server

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.

SQL Move Database To Emergency Mode

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

Reference

Related Articles & Tools

Leave your thoughts...