x

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 '10 at 08:17 AM in Default

Brimstedt gravatar image

Brimstedt
226 3 3 4

Will having your answer to this question accepted enhance your reputation? Discuss. ;)
Feb 23 '10 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 '10 at 08:27 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.8k 43 49 76

+1 Nice feature how do you set that up?
Feb 23 '10 at 08:59 AM Ian Roke
Feb 23 '10 at 09:03 AM Kev Riley ♦♦
SSMS 2008 only though. Blast! I need to submit a plea to get my company to upgrade SSMS 2005.
Feb 23 '10 at 09:56 AM Ian Roke
Ian, you can use SSMS 2008 against a 2005 server/db and it will use the colour/color (for our US friends) coding
Feb 23 '10 at 10:25 AM sp_lock
Ian - also, try Mladen Prajdic's SSMS Tools Pack... http://www.ssmstoolspack.com/
Apr 07 '12 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 '10 at 08:43 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

+1 - simply for the size involved!
Feb 23 '10 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 '10 at 05:09 PM Tom Staab
:) You have to love operations guys!
Feb 23 '10 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 '10 at 10:03 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
91k 19 21 74

I've done that!
Feb 23 '10 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 '10 at 11:09 AM

David Wimbush gravatar image

David Wimbush
4.7k 28 29 31

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 '10 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 '10 at 08:42 AM

Alex gravatar image

Alex
66 3 3 5

when i read through this comment.. my heart skipped a beat :)
Feb 23 '10 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x61

asked: Feb 23 '10 at 08:17 AM

Seen: 2462 times

Last Updated: Apr 11 '12 at 07:39 AM