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.

The above query takes database AdventureWorks2014 offline.

1_Detach or take offline in SQL Server

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.

2_Detach or take offline in SQL Server

The database can be brought online by executing below query.

Let’s now detach a database.

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.

3_Detach or take offline in SQL Server

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

 

Regards

Ahmad Osama

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

Follow me on TwitterFollow me on FaceBook