Know How to Delete a SQL Server Database
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 & thus users need to know an alternative solution for SQL delete database task.
Table of Contents
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 the Database Snapshot name and execute DROP DATABASE statement. Evidently, this can delete database SQL server.
DROP DATABASE sales_snapshot0600;
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:
DROP DATABASE Sales;
Note: Make sure to avoid any sort of mistakes to delete database SQL & get desired results.
To Drop multiple database using Transact-SQL, execute the following query:
DROP DATABASE Sales, NewSales;
Also Read: How to Clear SQL Server Transaction Log
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 delete the SQL Server Database and drop SQL Server database snapshot. While executing these commands, users need to be aware of the right execution. Evidently, the wrong execution can lead to delete SQL database in an unhealthy manner.