How I completely remove a SQL Server database – the right way!

I was shocked today!
I found this blog post on how you should remove a SQL Server database. I didn’t know if I should started crying or laughing…
This blogpost might be ridiculous for some of you but looks like it might be useful after all.

There are 2 possibilities to delete a database

GUI

With the SQL Server Management Studio you can right click on the database and select “Delete”

In the delete object window select the option “Delete backup and restore history information for databases” if you want to remove this information.
If you want to kick out open connections to your database select the “Close existing connections”. It will be impossible to remove the database if you don’t select the last option and there are still open connections to your database. You will receive an error that the database is still in use and cannot be deleted.

Once you hit the OK button, the database will be removed of the SQL instance and the database files on the OS level will also be removed. Definitely not necessary to shut down the whole instance to remove a database…

T-SQL

The script below does exactly the same as the actions that were taken in the GUI and have the same result. The database will be removed!

--This will remove the backup history
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'DBToDelete'
GO
USE [master]
GO
--This will drop all the open connections
ALTER DATABASE [DBToDelete] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
--This will remove my database
DROP DATABASE [DBToDelete]
GO

Now after the removal you still have some extra cleanup stuff to do that people often forget…

Delete the jobs

Delete the jobs that were related to the database. If you will not remove them, the jobs will fail and you will receive unnecessary alerts.

Delete the backup files

If you don’t need the backup files anymore, just remove them. However, I would recommend to keep the last full backup of the database and archive it for at least a year or 2. You never know that somebody needs the data in the future… J

Delete the logins without DB user

Your database had probably some database users configured that were linked to a server login.
If that server login isn’t used for any other database user and isn’t member of any server role besides public, I would recommend to remove that login. Not only for security reasons but also to keep your server clean.

Well, I hope that this can help some junior DBA’s and that they find my blog post instead of the other one J

Pieter

One comment on “How I completely remove a SQL Server database – the right way!

Leave a Reply

Your email address will not be published. Required fields are marked *