November 5, 2007 at 5:29 pm
Comments posted to this topic are about the item Whack a Database
November 6, 2007 at 1:40 am
Hmmm. Not sure the article wasn't missing the point somewhat.
Certainly, deleting a database is a big deal, but it's not the worst that could happen. To my mind losing (and having to restore) a database is far less scary than having a database whose data has been mangled. The odd update statement is as much a part of a DBA's job as creating or deleting the odd database, but get that one wrong and you could easily ruin a database far worse than deleting it - not least of all because you may not realise there's a problem immediately.
DBAs are trusted to "measure twice, cut once". People make mistakes. The current setup seems to me to take a sensible line between both these facts and allow the DBA to get on with their job.
IMHO, of course.
Semper in excretia, suus solum profundum variat
November 6, 2007 at 6:27 am
Personally I feel SQL delete function works fine. you can't idiot proof everything. Someone will just create a better idiot (work around). If there are too many actions required to make delete work someone will create a script to bypass the warnings. The old axion applies: if you don't know what you're doing don't do it!
Also I agree with previous reply, it is far easier to restore a missing database than fix a corrupted one. That's why we have backups.
November 6, 2007 at 7:11 am
Personally I would have felt comfortable with ditching the delete from within SQL Server and just keep the Detach option, "forcing" you to earn your delete by having to delete the files yourself, but ANSI seems to think a delete option is required.
Still - I think the delete function works as it should.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 6, 2007 at 7:33 am
Matt, I actually like the detach idea. That might make you think of it a bit more.
The idea wasn't so much to focus on deleting a database as how we might prevent ourselves from causing problems for ourselves. Maybe we should have a confirm for times when we're missing WHERE clauses in updates/deletes?
\
November 6, 2007 at 7:47 am
At my shop I created a stored procedure to delete/archive the database. While this doesn't stop me or another DBA from right clicking in Management Studio, it works well if you a disciplined. When we want to delete a database we run our DBA..usp_archive procedure which will take a final database backup to our default backup location, delete the database, and update our "DatabaseList" table with the archive date for this database. This at least gives us the ability to restore the database if we type in the wrong name, but as I said the GUI tools still let you bypass this.
My own thought is that a message box will still just be a automatic 'click OK' type of operation. The problem with detach is that people will end up with full drives because they forget to clean them out -- sort of like people who never empty their recycle bin 🙂
November 6, 2007 at 7:55 am
Steve Jones - Editor (11/6/2007)
Matt, I actually like the detach idea. That might make you think of it a bit more.The idea wasn't so much to focus on deleting a database as how we might prevent ourselves from causing problems for ourselves. Maybe we should have a confirm for times when we're missing WHERE clauses in updates/deletes?
\
Stop it now steve - you're starting to sound like Lotus Approach.....:w00t:
Really though - I think they've secured it enough: there's a second page already to confirm what you're doing, and security is good enough that you shouldn't be letting just anybody have access to deleting. I'm not sure what else they could do short of outright removing the ability to delete.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 6, 2007 at 8:09 am
Perhaps Microsoft should ask 5 basic SQL Server Admin questions from a vast repertoire whenever you launch Management Studio, and only let you continue if you get all 5 right, thereby requiring at least a basic level of expertise to fiddle with a server.
And yes, that was tongue-in-cheek......;)
Semper in excretia, suus solum profundum variat
November 6, 2007 at 8:32 am
I'm kind of a fan of the "entry IQ test". On the way in to SSMS/EM, test for intelligence. If you pass - you get to administer the server today....
Would help with all of those brain-dead days.... Those days where you're standing in there kind of deep, majorbloodnock 🙂
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 6, 2007 at 11:19 am
I believe the way Delete Database currently works has adequate steps before it deletes the database interms of having security and also prompting the user for confirmation in a separate window. But probably it may not be a bad idea to prompt user if he wishes to take a backup of the database that he desires to delete just as an additional precaution.
Prasad Bhogadi
www.inforaise.com
November 6, 2007 at 11:31 am
Matt Miller (11/6/2007)
I'm kind of a fan of the "entry IQ test". On the way in to SSMS/EM, test for intelligence. If you pass - you get to administer the server today....Would help with all of those brain-dead days.... Those days where you're standing in there kind of deep, majorbloodnock 🙂
In '94 I was on my way in to work and was #2 in a three-car pile-up. No one was seriously injured, all cars were drivable afterwards, but I was shaken up a bit. So I go in to work, but I should have gone home. It's Wednesday, it's a payroll extract day (we did our own payroll process which produced a file for the City's mainframe for their payroll process). Made one slight mistake while running my pre-extract verify and wiped out the database.
I was working for the police department. Lots of guys with guns. :w00t:
Fortunately I maintained good backups and we only lost maybe half an hour.
I think everyone needs to get burned like that at least once to really get the "measure twice, cut once" approach burned into your brain.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
November 6, 2007 at 11:34 am
Steve Jones - Editor (11/6/2007)
Matt, I actually like the detach idea. That might make you think of it a bit more.The idea wasn't so much to focus on deleting a database as how we might prevent ourselves from causing problems for ourselves. Maybe we should have a confirm for times when we're missing WHERE clauses in updates/deletes?
I had a former boss who needed to clear some squirrelly records out of the production database and his consultant/friend in California, proven not to know SQL Server very well, gave him the code. He ran it, expecting to delete something like 35 records, instead it took 100 or a 1000 times that many.
Fortunately I had good backups and was able to do a point-in-time restore and get everything back.
I then taught him how to do a SELECT * with his delete's WHERE clause before doing the actual delete. He liked that.
😀
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
November 6, 2007 at 12:01 pm
It's already been touched on, but whatever happened to backup before delete?
If M$ wants to be like the Federal Government and makes sure we buckle up, then then they can code in an automatic backup when you hit the delete button. No option, must do.
Besides, who ever deletes databases in error? :Whistling:
I hate that sinking feeling in the gut just after the finger comes off the enter key....
November 6, 2007 at 2:25 pm
The delete is fine. Changing it to just a detach unless you click a check box or say something similar to Oracle "Including datafiles" is Ok with me too.
whereas
drop database @dbname
would be the same as
sp_detach_db @Dbname
but
Drop database @dbname including datafiles
would be the same as the current drop would be ok with me.
November 6, 2007 at 3:22 pm
Hey, let's not forget that it's not always "our" fault. Harken back to the days of SQL 2000 Enterprise Mangler where Microsoft conveniently put "Delete" and "Refresh" right next to each other in the GUI.
ME:
EM: "Are you sure you want to do that?"
ME: "Um...sure? How nice of you to ask."
EM: "Have a nice day."
ME: "NOOOOO!!!"
At least with Manglement Studio there is a line separating the "Delete" and "Refresh" functions. And yes, I know...GUI's are for whimps. Get over it. This ain't Oracle 🙂
James Stover, McDBA
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply