• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer
sqlserver tutorial

SQL Server Tutorial

SQL Server Tutorial for Beginners

  • HOME
  • START HERE
  • BASICS
  • ADVANCED
    • Index
    • Views
    • Triggers
    • Stored Procedures
    • User-defined Functions
  • FUNCTIONS
Home / SQL Server Basics / SQL Server DROP DATABASE

SQL Server DROP DATABASE

Learning Objective

The objective of this SQL Server tutorial is to teach you how to delete the database using DROP DATABASE T-SQL script and using SQL Server Management Studio.

Note:- this tutorial uses sampleDB and TestDB databases which have been created in our earlier tutorial SQL Server CREATE DATABASE.

SQL Server DROP DATABASE Introduction

As a SQL Server Database Administrator or Developer, you sometimes may need to remove a SQL Server database. There are two simple methods to delete a SQL Server database as below.

  1. Using DROP DATABASE T-SQL Script.
  2. Using SQL Server Management Studio.

1) Delete SQL Server database using DROP DATABASE Statement

In order to remove an existing database in SQL Server, you need to use the DROP DATABASE statement.

SQL Server DROP DATABASE Syntax

The DROP DATABASE statement allows you to drop one or more databases using the following statement.

DROP DATABASE [ IF EXISTS ]
database_name
[,database_name2,…];

In this syntax,

  • DROP DATABASE – Keyword to delete the existing database from SQL Server.
  • IF EXISTS – This option is available from SQL Server 2016. It allows you to delete the database only if the database exists. If you attempt to delete a nonexisting database, SQL Server will give an error.
  • database_name – Name of the existing database, you want to delete from SQL Server.

SQL Server DROP DATABASE Example

In our previous tutorial, we have created two databases name sampleDB and TestDB. In this example, we will drop sampleDB using the DROP DATABASE statement.

Before deleting a database, you should remember below points.

  1. DROP DATABASE will remove the database along with it’s physical disk file. So make sure to take backup the database before dropping it.
  2. You cannot drop a database if the database is being used by other sessions.

If you try to drop an existing database which is currently being used by other users, SQL Server gives the following error.

Cannot drop database "sampleDB" because it is currently in use.

The following example will delete the sampleDB database from the SQL Server.

DROP DATABASE IF EXISTS sampleDB;

2) Delete SQL Server database using SQL Server Management Studio

Now, we will delete the other database named TestDB using SQL Server Management Studio.

You can follow the below steps to delete a existing database from SQL Server using SQL Server Management Studio.

a) First, right click on the Database name which you want to delete and click on Delete option.

sql server drop database

b) Second, select the checkbox Close Existing Connections to drop existing connections before deleting the database and click OK to drop the database from SQL Server.

sql server drop database

c) Third, by Selecting “Delete backup and restore history information for databases“, you will able to delete the backup and restore history which is stored in msdb system database;

d) Fourth, verify the dropped database not exist from object explorer.

sql server object explorer

Summery

In this tutorial, you have learned how to delete a database using the DROP DATABASE statement and SQL Server Management Studio.

Was this tutorial helpful?
YesNo
« Previous: SQL Server CREATE DATABASE
SQL Server CREATE SCHEMA :Next »

Primary Sidebar

DATA MANIPULATION

  • SELECT
  • SELECT TOP
  • SELECT DISTINCT
  • OFFSET FETCH
  • ORDER BY
  • GROUP BY
  • BETWEEN
  • LIKE
  • ALIAS
  • HAVING
  • AND
  • OR
  • IN
  • WHERE
  • SELECT INTO
  • INSERT
  • INSERT Multiple rows
  • INSERT INTO SELECT
  • UPDATE
  • DELETE
  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE CONSTRAINT
  • NOT NULL CONSTRAINT
  • SUBQUERY
  • CORRELATED SUBQUERY
  • JOINS
  • CROSS JOIN
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL JOIN
  • SELF JOIN
  • UPDATE Join
  • CASE
  • COALESCE
  • NULL
  • NULLIF
  • UNION
  • INTERSECT
  • MERGE
  • EXCEPT
  • EXISTS
  • GROUPING SET
  • PIVOT
  • ROLLUP
  • CUBE

DATA DEFINITION

  • CREATE DATABASE
  • DROP DATABASE
  • CREATE SCHEMA
  • ALTER SCHEMA
  • DROP SCHEMA
  • CREATE TABLE
  • RENAME TABLE
  • DROP TABLE
  • TRUNCATE TABLE
  • IDENTITY column
  • Sequence
  • ALTER TABLE ADD Column
  • ALTER TABLE ALTER Column
  • ALTER TABLE DROP Column

Footer

About

SQLServerTutorial.org provides free tutorials and guide on SQL Server for Developers, Database Administrators, and Solution Architects who want to get started SQL Server quickly.

Recent Posts

  • SQL Server DROP VIEW
  • SQL Server Indexed View
  • Check view definition
  • SQL Server Rename View
  • SQL Server List Views

Quick Links

  • About
  • Contact Us
  • Privacy Policy
  • SQL Server Index
  • SQL Server Views
  • Terms of Use

Copyright © 2023 www.sqlservertutorial.org. All Rights Reserved.