I’ve already explained about how to detach a database, moving a database to emergency, taking the database offline and bring back it online. In this article I’ll list down the differences between offline, detach and emergency.
Comparison: Offline vs Detach vs Emergency
| # |
Offline |
Detach |
Emergency |
|---|---|---|---|
| 1 | Taking the database offline will make the database unavailable. But the details of the database and its files are still intact in master database | Detaching the database will remove the database details completely from the SQL server leaving the data, log and other files independent. | When the database state is set to emergency, the database is moved to read_only single-user mode. |
| 2 | Once offline, You can see the reference of the database in SSMS object explorer. In object explorer, the offline database name is prefixed by a red down arrow icon. | You wont see any reference of the detached database anywhere in the SSMS including object explorer | Database in emergency mode is represented by the red database Icon before the database name in SSMS object explorer |
| 3 | Details of the offline database are intact in master..sysdatabases. | Once detached the details of the database are removed from master..sysdatabases. | Details of the emergency mode database are intact in master..sysdatabases. |
| 4 | Users and application cannot access the database. | Users and application cannot access the database. | Only member of sysadmin can access the database in read-only mode. |
| 5 | After taken offline, you cannot delete the mdf and sql files as it is still linked to the SQL server. | Detached databases files like mdf, ldf, etc… can be deleted as they are detached from SQL Server. | After setting the database to emergency mode, you cannot delete the mdf and sql files as it is still linked to the SQL server. |
| 6 | Offline database can be easily brought back online. You don’t need to tell the file locations to bring back it online. | To attach a database again, you need to specify the location of the files in the attach statement. | Emergency state database can be easily brought back online without specifying the file location. |
| 7 |
Usage:Taking the database offline is a quick way to make it unavailable to any one or any application. For example, if you want to prevent all the users from connecting the database for a short time then taking it offline will be the easiest option. |
Usage:Detaching a database is normally useful for moving a database permanently from one SQL Server or instance to another server or instance. |
Usage:Moving the database to emergency mode is primarily for troubleshooting purpose. It helps to avoid the regular users and applications from accessing the database. But the sysadmin can access it in read-only mode for troubleshooting and repairing. |
| 8 |
T-SQL syntax for taking database offline:ALTER DATABASE [Database-Name] SET OFFLINE |
T-SQL syntax for detaching database:Exec dbo.sp_detach_db ‘Database-Name’, ‘true’; |
T-SQL syntax for moving database to emergency mode:ALTER DATABASE [Database-Name] SET EMERGENCY |
Reference
- About the states of the database in Microsoft Docs.