Should it be easy to delete a database?

We had a discussion yesterday about Management Studio, and we focused on the “Delete” command. In SQL Server 2005, you can right-click a database and select “Delete”. You get a panel where you can set some options, or you can just click OK and the database is gone. Unless, that is, there are people connected to the database – in that case, you’ll get a warning, which has saved me multiple times!

But the bigger question came around the idea that it shouldn’t be too easy to delete a database. The argument was that we are already bringing up a panel and asking you to click OK, so that is a confirmation panel. And I know a lot of people really hate it when we ask “are you sure you’re sure?” all the time. Of course, we also get lots of angry mails when we don't warn people.

But I wonder if on something as big as deleting a database if we should. What do you think? Should we pop another warning that you’re about to do something kind of dangerous like delete a database, or should you be allowed just to blast through there?

Comments (18)

  1. We had a discussion yesterday about Management Studio, and we focused on the “ Delete ” command. In SQL

  2. Daniel Smith says:

    How about having a check box on the form that you have to tick for the delete to work.

    There’s a similar option in Enterprise Manager when you’re restoring a DB where you have to tick the "Force restore over existing database" check box.  I love this check box!  It’s saved me so many times when I really meant to restore as a new DB.  How often do you have to delete databases anyway?  I don’t think extra sensible precautions are going to annoy anyone.

  3. says:

    How about keeping the current fuctionality and adding the option to block dropping a database if the admin chooses…something similar to a DDL trigger. This option might grey out the menu item for a database.

  4. RandyDyess says:

    I think the two levels you have right now are fine. I do like the second confirmation page as the DELETE command is too close to the RENAME command and it is quite easy to pick the wrong one when you are in a hurry.

    I have not had any problems with any of my clients deleting databases by mistake – the problem comes from giving inexperience people the authority to delete databases and no matter how many steps you make me jump through with the GUI this inexperienced DBAS will still drop databases at the same rate.

  5. mikedotnet says:

    Deleting a database should be a rare occurance, so if I had to click "Yes, i’m sure" multiple times it wouldn’t bother me.

    Although, why not just remove the "delete" option and have people detach a database if they want to get rid of it?

  6. BuckWoody says:

    Interesting comments. You can prevent people from deleting a database by removing the right from them. It won’t gray out the option, but it will keep them from doing it. Of course, if they are SA, all bets are off. But you are VERY frugal with who gets that right, correct? :)

    Detaching is one way to remove the database, but to stay ANSI and industry compliant we do have to provide the delete operation.

    Thanks to everyone for commenting – we’re listening!

  7. justinb486 says:

    What about an admin function to set the status of a database?  ie, if it’s set as critical then you have to go through a lot to delete/overwrite it.  But if it’s flagged as a minor test database then you can right-click and delete.  This way DBAs can set the requirements per DB?

  8. Richg6 says:

    I’m not sure this is your responsibility, I back up for a reason, SQL backup functionality is excellent, if it’s not being used and someone accidentally deletes a database that smells like natural selection to me….

    I think it is important to check the DB for current connections on delete and stop if it finds any, this is probably the most surefire way of stopping you deleting a production DB without being intrusive. On the subject of connections, a recent connection list would maybe help track down interested parties in a larger organisation, so on delete an information box "Over the past 7 days Joe Soap, Joe Bloggs and John Doe have logged into this database"

    A 7 day "tombstone" type approach which emails the DB creator for confirmation would be safer, but that’d just get in the way if you’re short on disk space, and there’s no guarantee you’ll get the right user anyway…….

  9. slamberson says:

    Implementing a feature which requires the user wishing to delete a database to type the full name of the database they wish to delete **might** make the person consciously aware of what they are doing.  (Make sure to disable copy/paste.)

  10. markh319 says:

    I would like an easy way to kick everyone out and delete a db with a couple of keystrokes, instead of having to find my script to kick everyone out then drop the db…..

    You could use a ‘recycle bin’ approach maybe with a ‘destroy permanantly’ checkbox?


  11. says:

    It’s A-OK as it is, because it is secure, and simple, and that dialog box is pretty clear what’s happening. But why not enhance the dialog some, getting the best of all worlds?

    For example, leave the process as it is currently by default. In the confirmation dialog provide a checkmark that backs up the current database to a safe location before deleting it. If checked, spawn a backup and complete it appropriately, then return to the deletion. Inside the SSMS options, provide a checkbox that reads something like "backup any database to the following location by default when deleting," and a path navigator to set that locaation. (Maybe a folder called "BackupsDeleted" or some such, by default.) This way, for those who like the speed provided by the delete as it is, they have no gripes; and for others who want to be extra-safe, they have a simple new feature that will make their lives easier.

    Most of us probably have protocols for deletion, but no one could really argue with that kind of automated helper. (And I’m being selfish, because basically, that’s what I tend to do, just on the off chance I decide later I wanted that database back, and such a feature would reduce my clicks significantly.)

    While you’re at it, though, you could add some colour to certain confirmation dialogs, or maybe a big icon with a warning context. The only complaint I have these days about SSMS, now that I’ve gotten used to its oddities, is that every dialog looks the same for the most part. Any dialog that commits an action of serious nature, like a deletion or detaching, should visually remind the user they are about to do something serious. Colour isn’t a crime when used sparingly, and again no one would argue such a change because at hour 10 of a long day, a few obvious visual clues you’re about to do something heinous would be nice.

    Fantastic discussion, though, and I really look forward to seeing future value is the SSMS interface.

  12. rkralston says:

    I suppose having an "Are you sure" dialog would be OK.  The two phase commit, get a bunch of passwords idea is right out.  We’re not launching nuclear warheads (or is that nuculer?).  

    I understand wanting to save people who don’t know what they are doing, but the organization that turns such a person loose on their mission critical data will be having other problems as well.

    Honestly, this seems like the last group that needs to have their hands held.  

    On the other hand, as you make SQL Server more like Access (is that more accessible?) you will need to consider this expanded user group in your admin designs.

  13. justinb486 says:

    Re the last few comments, in my company (European support only) we have 3,328 SQL databases and growing, all of which follow our service model naming conventions i.e. they’re all named pretty much the same which is very scary!  

    So something that steps out of the ANSI standard of drop database, to something with a bit more validation (optional to only enable on production/important) would be handy.

    In some very secure environments I’ve worked in previously, to delete a key entry, let alone a DB required 2 authorised users to approve.  

    To stop a typo dropping a £50 billion SAP DB rather than it’s n-th degree prod sized copy would be good…  

  14. BuckWoody says:

    Great informsation!  Here’s what I’m thinking based on what I’ve heard: How about a dialog that shows you the last backup of the database before you drop it? And how about an option to "Take Offline and Delete in X days"?

  15. justinb486 says:

    Morning (UK time).  The offline/delete sounds great.  To be able to set a flag on a DB so that the TSQL DROP DATABASE command warns but doesn’t drop would be good, so people can’t accidentally do it via script too.

    That sounds like something that would be great in a service pack/addon for SQL2000/2005…

  16. jrea8830 says:

    I like the backup as an option only; I don’t think it is a good way to resolve the current discussion though.

    I like the idea of instituting a flag/option that can be set on the database to prevent deletion. An example would be: CREATE DATABASE mydb WITH NODROP;

    Then if the user tries to DROP this database they would receive an error stating this flag exists.

    The user could pass a command to remove the flag, such as: ALTER DATABASE mydb WITH ALLOWDROP;

    I think this solution would cover both types of DBAs. Those that do development may not be as interested in more work to DROP a database (think if this was implemented with tables, would you want to have another confirmation?), and those that are in production have an ‘option’ to further secure their databases. How can this not be a good thing? Options are much better than having more work forced upon us.

    Also, can you please move the Delete command in the context popup menu? I agree it is too close to ‘Rename’. Maybe put another line-separator in there to make it so that I don’t accidentally click ‘Delete’.

    Thanks for listening to me!

  17. BuckWoody says:

    That’s an interesting suggestion – and in fact, with the new DMF feature in SQL Server 2008 you can prevent things like that.

  18. FrankR says:

    Making the ability to vreate a database with a NODROP option (or something like it) would, to my opinion, be a great solutio. This gives the DBA the ability to decide on how secure he wants to be.

    Furthermore I would consider making a server setting which can be set to put on to replace the DELETE IMMEDIATLY option by DEFFERED DELETE option. The DEFFERED DELETE (hope my grammar is correct) could be your solution (Take offline and delete in x days). The great thing about this deffered delete is that yu get the opportunity to discover your error ;-)

Skip to main content