LinkedIn | FaceBook | Twitter
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?
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!
That's an interesting suggestion - and in fact, with the new DMF feature in SQL Server 2008 you can prevent things like that.
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 ;-)