This page looks best with JavaScript enabled

Taking Databases Offline and/or Dropping through SQL Query

 ·  ☕ 2 min read  ·  ✍️ Chris Titus

The following SQL query is for taking databases offline, drop the offline databases, or bring them all back online. This affects every database in your SQL instance, so be very careful when using these. The following three queries will save you a considerable amount of time if repurposing an instance.

Taking databases offline and then dropping them

use master;
declare @nsql nvarchar(max);
select @nsql=coalesce(@nsql+CHAR(13)+CHAR(10),'')+
'ALTER DATABASE ['+name+'] SET OFFLINE WITH NO_WAIT;'
from master..sysdatabases where sid <> 0x01
exec (@nsql)`

Once all databases are offline you can drop them to clear out the entire instance with this SQL query.
Note: This is extremely destructive so please use this with care.

WARNING Running this WILL DESTROY ALL DATABASES!!!

use master;
declare @nsql nvarchar(max);
select @nsql=coalesce(@nsql+CHAR(13)+CHAR(10),'')+
'DROP DATABASE ['+name+'];'
from master..sysdatabases where sid <> 0x01
exec (@nsql)

Use the following SQL query to take all databases online

use master;
declare @nsql nvarchar(max);
select @nsql=coalesce(@nsql+CHAR(13)+CHAR(10),'')+
'ALTER DATABASE ['+name+'] SET ONLINE WITH NO_WAIT;'
from master..sysdatabases where sid <> 0x01
exec (@nsql)

Contact Me

I live stream on Chris Titus Tech YouTube Channel every Friday at 10 AM CST and archive clips to Titus Tech Talk. I also regularly publish to christitus.com, but if you’d like to contact me directly or want to contribute to help keep these articles and videos being made consider joining the CTT members.

Two Memberships exist:

  • ChrisTitus.com Members Section (CC Only)
    • Full Archive of All Unlisted Live Streams
    • Direct Members Only Email
    • Monthly Members Only Video
    • Starting at $2 Per Month (100% of Proceeds goes to Chris Titus Tech)
  • YouTube Chris Titus Tech Membership (All Payments Accepted)
    • YouTube Emojis for Comments and Live Chat
    • YouTube Badges that changes based on membership time for comments and chat.
    • All YouTube comments are highlighted when I review comments daily.
    • Immediate Access to Full Live Streams
    • $4.99 Per Month (70% of the Proceeds goes to Chris Titus Tech)
Share on

Chris Titus
WRITTEN BY
Chris Titus
Tech Content Creator