Detach or take offline in SQL Server

Detach or take offline both causes a database to be inaccessible to users. The difference is that detach deletes database metadata from SQL Server i.e. database file information, status information and all the other details that we see in sys.databases view. On the other hand taking database offline retains database metadata in SQL server system views.

Let’s take a database offline.

ALTER DATABASE AdventureWorks2014 SET OFFLINE WITH ROLLBACK IMMEDIATE

The above query takes database AdventureWorks2014 offline.

If we query sys.databases view the database information can be seen as shown in above snapshot. Also, the object explorer shows database as offline as shown below.

The database can be brought online by executing below query.

ALTER DATABASE AdventureWorks2014 SET ONLINE WITH ROLLBACK IMMEDIATE

Let’s now detach a database.

USE [master]
GO
ALTER DATABASE [AdventureWorks2014] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'AdventureWorks2014'
GO

The above query first drops database connections by setting it to single user mode and then executes sp_detach_db to detach it. Let’s query the sys.databases view for the detached database.

The database details aren’t maintained when database is detached. The data files can be copied or accessed when a database is detached.

Let’s now execute the below query to attach

USE [master]
GO
CREATE DATABASE [AdventureWorks2014] ON 
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.DMSQL2014\MSSQL\DATA\AdventureWorks2014_Data.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.DMSQL2014\MSSQL\DATA\AdventureWorks2014_Log.ldf' )
 FOR ATTACH
GO

Like us on FaceBook  |  Join the fastest growing SQL Server group on FaceBook