Know How to Delete a SQL Server Database

Introduction

The blog discusses how to delete SQL Server Database by using SQL Server Management Studio and Transact-SQL. The query is user want to delete a SQL Server database when data is no longer required or data moved to another server or database. When user drop the SQL Server database, then it cannot be retrieved without the backup.

Limitation: You cannot delete the System database file.

Before Dropping SQL Server Database

• You cannot delete the database that is involved in the log shipping. You have to remove it, if your database is involved in the log shipping.
• If the database that you want to delete is subscribed to merge replication or published for transaction replication, then you have to remove the replication from the database.
• Snapshot of Database must be deleted.

To delete SQL Server Database Snapshot user must have DROP DATABASE permission.Then, specify Database Snapshot name and execute DROP DATABASE statement.

CONTROL and ALTER ANY DATABASE permission or membership in db_owner is must required to delete the database

Delete Database Using SQL Server Management Studio

• To drop a database using SQL Server Management Studio, connect to an SQL Server Database Engine instance from Object Explorer, and Expand the instance.



• Right click on the database which you want to delete, and select Delete



• Confirm the database and click Ok button

Delete Database Using Transact-SQL

To drop a database using Transact-SQL, Connect to Database Engine, from the Standard bar, click New Query and execute the following query in a query box

To Drop multiple database using Transact-SQL, execute the following query

Conclusion

In this article you have seen how to delete a SQL Server Database using SQL Server Management Studio and Transact-SQL. The blog also covers how you can you delete the SQL Server Database and drop SQL Server database snapshot.