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)

Chris Titus Tech

Social

Exclusive Content

  • ChrisTitus.com Members Section (CC Only)
    • Digital Downloads with Guides and Pre-Built Images
    • Monthly Members Only Video
    • $5 Per Month (100% of Proceeds goes to Chris Titus Tech)
  • YouTube Chris Titus Tech Membership (All Payments Accepted)
    • Monthly Members Only Video
    • 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.
    • $4.99 Per Month (70% of the Proceeds goes to Chris Titus Tech)

Chris Titus
WRITTEN BY
Chris Titus
Tech Content Creator