SQL DROP DATABASE with SSMS, Azure Data Studio, T-SQL and PowerShell

By:   |   Updated: 2024-01-24   |   Comments   |   Related: More > Database Administration


Problem

We need to drop a SQL Server user database. What happens when we drop a SQL database? What are the risks for Database Administrators (DBAs)? And what problems could DBAs encounter?

Solution

This tip will look at the permissions required, the preparation needed, and several ways to drop a SQL Server database.

Things to Know Before Dropping a SQL Database

Before we look at ways to drop a database, we need to point out that under almost all circumstances, it is an irreversible action. The entries for the database are removed from system tables in the master database, and the physical database files are removed from the file system, thereby deleting all tables, views, stored procedures, functions, etc. The only way to reverse this action is by restoring it from a good backup. It's always best to back up the database and restore it elsewhere to be sure it's good before proceeding.

Permissions Required

The DROP DATABASE statement requires one of the following server-level permissions:

  • CONTROL
  • ALTER ANY DATABASE
  • Be in the dbcreator group
  • Be in the sysadmin group
  • Or be in the db_owner group in the database.

Preparations for Dropping a Database

  1. Backup the database and restore it elsewhere to test the backup.
  2. Delete any database snapshots.
  3. If the database is part of log shipping, remove it from log shipping.
  4. Remove the database from replication.

SQL Server Management Studio (SSMS)

The first example will use SSMS to drop a database, as it's the most likely tool you'll use.

Step 1

In SSMS Object Explorer, expand Databases.

  1. Right-click the database to drop
  2. Delete
Delete Database from SSMS

Step 2

  1. The Delete Object window appears. Click OK.
Delete Database from SSMS

Step 3

An error may appear, saying the drop failed.

  1. Click on the hyperlink in the Message field.
Attempt Failed

The following message tells us the failure is due to the database being in use. 

TITLE: Microsoft SQL Server Management Studio 
------------------------------
Drop failed for Database 'Database_1'.  (Microsoft.SqlServer.Smo)
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.200.48044.0&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Database&LinkId=20476 
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot drop database "Database_1" because it is currently in use. (Microsoft SQL Server, Error: 3702)
For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-3702-database-engine-error 
------------------------------
BUTTONS:
OK
------------------------------

Using sp_who shows there is a connection in the database.

sp_who

One way to remove the connection is with the kill command: kill 54

Or all connections can be forced out as part of the database drop.

  1. Check 'Close existing connections.'
  2. Click OK.
Close existing connections

The database has been successfully dropped.

Azure Data Studio (ADS)

The process of dropping a database with ADS is almost the same as with SSMS.

Step 1

  1. Click the Trashcan icon. Or right-click the database and choose Drop from the menu.
Delete Database from ADS

Step 2

Neither Drop Database Options selected by default.

  1. Check 'Close existing connections' to force connections out of the database.
  2. Check 'Delete backup and restore history information for database' if you want to delete the history.
  3. Click Drop.
Select Options

Dropping a database via SSMS or ADS is ideal for a one-off scenario. However, if you need to drop a database routinely as part of a testing, development, or deployment process, doing it manually via a GUI would be impractical. This is when we would do it programmatically.

T-SQL DROP DATABASE Command

To drop a database via T-SQL syntax (i.e. DROP DATABASE database_name), configure the database name in the @DatabaseName variable and run the SQL commands below. It will kill any connections, delete backup history, and drop the database including tables, indexes, primary keys, foreign keys, stored procedures, functions, views, etc.

/* mssqltips.com */
USE master;
GO
-- declare and config variables
DECLARE @DatabaseName VARCHAR(50) = 'Database_3'; -- set database name to drop  here
 
DECLARE @CloseConnectionSql VARCHAR(200)
    = 'ALTER DATABASE ' + @DatabaseName + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
DECLARE @DropDatabaseSql VARCHAR(200) = 'DROP DATABASE ' + @DatabaseName
 
IF EXISTS (SELECT 1 FROM sys.databases WHERE name LIKE @DatabaseName)
BEGIN
    EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = '@DatabaseName'; -- delete backup history
    EXEC (@CloseConnectionSql) -- kills any connections
    EXEC (@DropDatabaseSql); -- drop database
END

PowerShell

To drop the database with PowerShell, configure the $ServerInstance and $DatabaseName variables and run. Any processes will be killed, the backup history will be deleted, and the database will be dropped.

# mssqltips.com
#import SqlServer module
Import-Module SqlServer
 
$ServerInstance='JGAVIN-LT\SQL2022' # set SQL Server instance here
$DatabaseName='Database_4'          # set name of database to drop here
$DeleteBkupHistSql="EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = $DatabaseName" # delete Backup history

Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $DeleteBkupHistSql
 
# drop database
$Server = New-Object Microsoft.SqlServer.Management.Smo.Server($ServerInstance)
$Db = $Server.databases[$DatabaseName]
if ($db) {
  $Server.KillAllprocesses($DatabaseName)
  $Db.Drop()
}

Dropping Offline Databases

So far, Database_1 through Database_4 were all online, which means when we dropped them, they were removed from system tables in the master, and the physical files were also deleted.

Offline Database

Let's see what happens when we drop an offline database. Using the same T-SQL code as above, minus the section to kill the connection, because by default, if the database is offline, there are no connections.  Here are the SQL commands:

USE master;
GO
 
-- declare and config variables
DECLARE @DatabaseName VARCHAR(50) = 'Database_5'; -- set database name to drop  here
 
DECLARE @DropDatabaseSql VARCHAR(200) = 'DROP DATABASE ' + @DatabaseName
 
IF EXISTS (SELECT 1 FROM sys.databases WHERE name LIKE @DatabaseName)
BEGIN
   EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = '@DatabaseName'; -- delete backup history
   EXEC (@DropDatabaseSql); -- drop database
END

Database_5 is gone.

Database Deleted

But where the database was offline, only the entries in the master had been deleted. Look in the data directory. The other data and log files are gone, but Database_5.mdf and Database_5_log.ldf remain.

Physical Files Not Deleted

At the beginning of this tip, it was stated that dropping a database is an irreversible action under almost all circumstances. This is one time it is not if the database files are intact, as in this case.

Step 1

Let's go back to SSMS Object Explorer.

  1. Right-click on Databases.
  2. Click Attach…
Attach Database

Step 2

  1. In the Attach Databases window, click Add…
Attach Database

Step 3

  1. Select the data file.
  2. Click OK.
Locate Database File

Step 4

  1. Click OK.
Attach

And the database reappears.

We were able to recover the database, but this is still no substitute for taking a backup and testing it. Under most circumstances, though, you will manually delete the files to reclaim disk space and allow for reuse of the database name.

Next Steps

The following links are additional tips related to dropping, backing up, and restoring databases.

The following links are for common database and table management:



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Joe Gavin Joe Gavin is from the Greater Boston area. He started working with SQL Server and Sybase in 1998 in the financial services industry and has been a SQL Server Database Administrator for a dairy cooperative since 2011. He graduated from Northeastern University in Boston with a Bachelor of Science in Engineering Technology (BSET) degree in Computer Technology. Joe has spoken at the Boston and Providence SQL Saturday events.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2024-01-24

Comments For This Article

















get free sql tips
agree to terms