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

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

Backup the database and restore it elsewhere to test the backup.
Delete any database snapshots.
If the database is part of log shipping, remove it from log shipping.
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.

Right-click the database to drop
Delete

Step 2

The Delete Object window appears. Click OK.

Step 3

An error may appear, saying the drop failed.

Click on the hyperlink in the Message field.

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.

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.

Check ‘Close existing connections.’
Click OK.

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

Click the Trashcan icon. Or right-click the database and choose Drop from the menu.

Step 2

Neither Drop Database Options selected by default.

Check ‘Close existing connections’ to force connections out of the database.
Check ‘Delete backup and restore history information for database’ if you want to delete the history.
Click Drop.

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.

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.

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.

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.

Right-click on Databases.
Click Attach…

Step 2

In the Attach Databases window, click Add…

Step 3

Select the data file.
Click OK.

Step 4

Click OK.

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:

Resources

Community

Subscribe