If you haven’t been asked to Delete a database in your DBA career…its just a matter of time:
“We don’t need database XYZ anymore…delete it.” — Some well meaning manager
Stop right now, before blindly following an order like that.
First…there are some questions to ask:
- What environment is this? (Prod, Dev, etc.)
- Are you sure we don’t need it?
- Has anyone verified that?
- When was it last used?
- Did something replace it?
- Is it backed up?
As a DBA, any time anyone asks you to delete anything, from data to an entire database, you need to question it. Not doing so can be a CLM/RGE combo! (Career Limiting Move/Resumé Generating Event)
The request may be perfectly valid, but in my experience most people that ask for a database to be deleted use the term to mean “I don’t want to see that database in SSMS anymore” or “I don’t want it on Server A, put it on Server B”, with very little regard for impact to other teams or applications.
There are three main ways to take a database “out of service”:
- Take it Offline
- Detach it
- Delete it
Each has its own considerations and each is the proper choice in different circumstances. I am going to walk you through each one, from least risky to most, in that order.
Offline:
Takes the database offline, but still visible in SSMS. Cannot be accessed by applications, backups, etc. Can be brought back online relatively quickly.
My list of databases before starting this post…we’ll be messing with the Rename2222 database (nothing in it…came from a rename script I was writing/testing):
Right-click the database in question, Tasks, Offline:
Dialog Box you get after clicking “Take Offline”:
Note 1: You do not get the below dialog in SSMS 2014 (and possibly earlier)…this was done using SSMS 2016.
Note 2: If you click the Script button, it will take the database offline in SSMS2016, instead of generating a script. I’m told this is resolved in SSMS 2017.
Results in SSMS after Offline Operation:
Upside to Offline method: Database is still in place, visible in SSMS, Record in master database is still there, relatively simple.
Downside to Offline: May cause SQL Monitoring tools to throw alerts and create tickets, files are not in use by SQL Server, can be deleted.
Detach:
Removes the database from SSMS, deletes its meta information in the master database, but leaves the physical files intact on the drive.
The detach menu:
The detach dialog:
Choose “drop connections” here…if not, the detach may fail. Or, leave it unchecked as a test to see if anyone is using it
After Detaching:
Note that the MDF/LDF files are still right where they were. SQL Server no longer has a handle on them:
You can re-attach fairly quickly should the need arise (i.e. user comes screaming at you 5 minutes after the detach):
Right-click “Databases”, select Attach
Click ‘Add’ and browse for the .MDF file….should find and include the .LDF file on its own
Click ‘OK’ to attach
Upside to Detach method: Files are still in place, re-attach is fairly straightforward, Monitoring tools will generally not error. Can save your job by not having deleted a database someone else needed.
Downside to Detach: Files not manually deleted take up disk space…set reminders to delete after some time period.
Delete:
Drops the database, its meta data in Master, AND its associated physical files…you cannot reverse a Delete (DROP Database).
This is one of those “Stop right here” moments. Before you Delete a database, make very sure you are following protocols. Make sure you have a recent FULL backup:
If not, run one. The simplest possible command to backup a database:
-- backs up to default location Backup Database Rename2222 To Disk = 'Rename2222.bak'
Better:
-- backs up to specified location -- with date included in the file name: Backup Database Rename2222 To Disk = 'c:\MyBackups\Rename2222_20170531.bak'
Once you have a backup, you also should get a ticket, change control, or at the very minimum an email from the requester with approval for the delete. Depends on your process. Especially important if this is a production environment. This can save your job. If you Delete a database that a system depends on and cause an outage…whoever told you to do may claim they were not involved.
To delete is way too simple:
Deleting backup/restore history is up to you. Drop all connections to force everyone out.
If anyone is connected, should you be deleting?
Remember…once you Delete a database…its gone. The ONLY ‘undo’ is to restore from Backup. You can call Microsoft, but they will tell you the same thing. Worst case, you can spend tens of thousands of dollars on a data recovery service, assuming you can take the server down and nothing overwrote the space the the files were in on the drives.
Wrap up:
Almost everywhere I’ve worked, Detach with a 2-4 week follow up to delete the files was the best choice. But beware that some systems may only be useful for Quarterly or Annual reporting. In an ideal situation, you have a complete inventory of every database on every server from Production down to Dev and know exactly why they are there, as well as who the stakeholders are for each one. If you don’t have this for the O/S, SQL and DB level info, start now.
Thanks for reading!
My Pluralsight course for new DBAs
