What was your worst SQL mistake?

We all fear of making the big mistakes. Sometimes we accidently truncate the wrong table, update the wrong customers or make other big mistakes. Often, backups or copies of the database comes to rescue, but sometimes backups are to no avail.

Like when a workmate of mine missed a column in a join and x number of thousand customers got two packages with twice the number of products instead of what they should have got. Happy day for customers and post office, not so happy at the office :-)

What was your big mistake? And what practices do you have to prevent them from happening again?

more ▼

asked Feb 23, 2010 at 08:17 AM in Default

avatar image

226 4 5 7

Will having your answer to this question accepted enhance your reputation? Discuss. ;)

Feb 23, 2010 at 09:07 AM David Wimbush
(comments are locked)
10|1200 characters needed characters left

17 answers: sort voted first

UPDATEs or DELETEs without a WHERE clause.
Good test of backup and restore strategy......
Alternatively, wrap the whole code in a BEGIN TRAN ... ROLLBACK, check the rowcounts and/or data, then change it to a BEGIN TRAN ... COMMIT

Running some T-SQL in SSMS against the wrong server (live vs test).
Now use the customized colour on the status bar for each connection.

more ▼

answered Feb 23, 2010 at 08:27 AM

avatar image

Kev Riley ♦♦
66.1k 48 63 81

Ian - also, try Mladen Prajdic's SSMS Tools Pack... http://www.ssmstoolspack.com/

Apr 07, 2012 at 09:18 PM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left

I can't really think of a time that I made a massive mistake with a database (touch wood), but I have had to recover from some classics from my colleagues.

The best was when a guy truncated a live transaction table instead of the data loading table that he meant to truncate... The look on his face was a picture worth a million pounds! Shortly after that was when I added the 'big yellow test mode' to my editor (does the BEGIN / ROLLBACK mentioned by Kev).

The next best was when an operations guy removed a disk shelf from a rack while it was still on so he could read the serial number from a sticker on the top. The cable wasn't screwed in properly, and the whole database got marked as suspect. Cue a 70 hour week from me recovering over 6 billion rows of data!

more ▼

answered Feb 23, 2010 at 08:43 AM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

  • simply for the size involved!

Feb 23, 2010 at 08:48 AM Fatherjack ♦♦

I once made the mistake of informing a server room support technician that a database server for a different application was plugged into our already-heavily-used PDU (power distribution unit). His response? He walked over to see for himself, and then unplugged it!

Feb 23, 2010 at 05:09 PM Tom Staab ♦

:) You have to love operations guys!

Feb 23, 2010 at 05:21 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

I restored a development database on top of production when I wasn't paying attention to which server I was logged into. That was a long time ago, but it was a doozy.

more ▼

answered Feb 23, 2010 at 10:03 AM

avatar image

Grant Fritchey ♦♦
137k 20 44 81

I've done that!

Feb 23, 2010 at 12:46 PM Steve Jones - Editor ♦♦
(comments are locked)
10|1200 characters needed characters left

We use an app to concatenate all the scripts for a system release into one master script. A few years ago I ran a release containing about 175 scripts on the test server. Or so I thought. Within seconds all hell broke loose. Too many windows open - it was the live server. We were down for about 2 hours while I untangled that one.

To stop it happening ever again, I changed the SQL the app appends at the start of the master script to check @@servername and raise a severity 20 error if it matches the live server's name. I actually have to comment it out to get the script to run on release day. Every now and then I forget and then the result is "A severe error occurred on the current command. SQL Server is terminating this process." After my heart restarts I get a nice warm glow.

The weird thing is, I've never run it by accident on the live server again but I live in hope!

more ▼

answered Feb 23, 2010 at 11:09 AM

avatar image

David Wimbush
10.7k 30 34 43

I did something similar when SQL2005 and SSMS first came out, ran a create table script (drop first) on a test db, so I had thought. The new db drop down list was pointed to production. Luckily just a code table

Feb 24, 2010 at 09:46 AM Scot Hauder
(comments are locked)
10|1200 characters needed characters left

Accidently used DROP DATABASE instead of DROP TABLE on a database which had a name similar to one of the tables... Yes, I had a back-up and it was in a development environment so not a real loss. Just painful since the SQL code was part of a bigger application so it took a while before we discovered the bug. (The application was supposed to create a new database, bulk import data into a single table, then process and divide the data over multiple others and finally clean up the import table again. A bit dumb when you create the database and tables, move the data around and then delete the complete database.)

more ▼

answered Feb 23, 2010 at 08:42 AM

avatar image

66 4 3 7

when i read through this comment.. my heart skipped a beat :)

Feb 23, 2010 at 07:24 PM Katie 1
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 23, 2010 at 08:17 AM

Seen: 3626 times

Last Updated: Apr 11, 2012 at 07:39 AM

Copyright 2017 Redgate Software. Privacy Policy