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

SQL SERVER - Offline, Detach and Drop - Differences - SQL in Sixty Seconds #103 Offline-Detach-Drop-800x450 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


Taking Database Online


Detaching Database

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

Attaching Database

EXEC sp_attach_db @dbname = N'SQLAuthority',
@filename1 =
@filename2 =

Drop Database

DROP DATABASE SQLAuthority -- noway to recover data

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)

SQL in Sixty Seconds, SQL Scripts, SQL Server, SSMS, System Object
Previous Post
Are Not Equal to Operators Equal to Not In? – SQL in Sixty Seconds #102
Next Post
SQL SERVER – Live Query Statistics – SQL in Sixty Seconds #104

Related Posts

2 Comments. Leave new

  • Philip Christiaan van Gass
    August 18, 2020 8:33 pm

    Hi Pinal Dave. Is there any difference between the commands DROP and DELETE ?


Leave a Reply