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
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…
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