SQL SERVER – Offline, Detach and Drop – Differences – SQL in Sixty Seconds #103

During the recent consulting engagement of Comprehensive Database Performance Health Check, I was asked by my client, if I can help them understand the difference between database status like Offline, Detach and Drop. It was very important to differences between them as they can lead to data losses.

Warning: Do not try any of the suggestion discussed in this video on your production server. Try them out on your Development environment with utmost care as it is possible that you may lose the data permanently.

Let us understand the difference between Offline, Detach and Drop with the help of a SQL in the Sixty Seconds video.

I hope it is clear from the video how Offline and Detach works, additionally, we should remember to use Drop very carefully as once the data is lost it is lost forever.

Offline Database command will make your database unavailable to use and also release any resources (like memory) it is consuming. The best part of the offline database is that as the database stay visible in SSMS, we can bring it back online very quickly.

Detach and attach command is usually helpful when you want to move your database from one location to another location. In most cases, Offline and Detach commands can be executed safely without any data loss.

However, when you execute Drop Database command you pretty much lose all of your data immediately and the only way to get that back is via restoring the backup.

Here is the script used in the demonstration. Please use them carefully as they can incur you database loss.

Taking Database Offline

ALTER DATABASE SQLAuthority
SET OFFLINE
WITH ROLLBACK IMMEDIATE
GO

Taking Database Online

ALTER DATABASE SQLAuthority
SET ONLINE
GO

Detaching Database

EXEC master.dbo.sp_detach_db @dbname = N'SQLAuthority'
GO

Attaching Database

EXEC sp_attach_db @dbname = N'SQLAuthority',
@filename1 =
N'D:\data\SQLAuthority.mdf',
@filename2 =
N'D:\data\SQLAuthority_log.ldf'
GO

Drop Database

DROP DATABASE SQLAuthority -- noway to recover data
GO

If you are using TempDB and want to move the TempDB files to a different location, you cannot use any of the methods described in this blog post. In that case, you will have to use the method described in this blog post and video.

Reference: Pinal Dave (https://blog.sqlauthority.com)