views:

140

answers:

8

I'm a frequent SQL Server Management Studio user. Sometimes I'm in situations where I have an update or delete query to run, but I'm afraid some typo or logic error on my part is going to cause me to make undesired, massive changes to a table (like change 1000 rows when I meant to change 2).

In the past, I would just clench my fists and hold my breath, but then I wondered if I could do something like this before a running possibly catastrophic query:

1) Run below

begin transaction
(my update/insert/delete statement I want to run)

2) If I'm satisfied, call:

commit transaction

3) Or, if I've fouled something up, just call:

rollback transaction

Is my idea sound, or am I missing something fundamental? I know I could always restore my database, but that seems like overkill compared to above.

EDITS:

1) I agree with testing on a test site before doing anything, but there's still a chance for a problem happening on the production server. Maybe some condition is true on the test server that's not true on production.

2) I'm also used to writing my where first, or doing a select with my where first to ensure I'm isolating the correct rows, but again, something can always go wrong.

+4  A: 

Yes, you absolutely can do this. Be aware that you are putting a lock on the table(s) in question, which might interfere with other database activity.

peacedog
+2  A: 

The most frequent cause of this fear is being forced to work on Production databases by hand. If that's the case...might be better to get some dev boxes. If not, I think you're fine...

Jason Punyon
+2  A: 

Sounds pretty good to me - I basically use this default try/catch query for most of my heavy-lifting; works just as you sketched out, plus it gives you error info if something does go wrong:

BEGIN TRANSACTION
BEGIN TRY

    -- do your work here

    COMMIT TRANSACTION

END TRY
BEGIN CATCH
    SELECT 
     ERROR_NUMBER() AS ErrorNumber,
     ERROR_SEVERITY() AS ErrorSeverity,
     ERROR_STATE() AS ErrorState,
     ERROR_PROCEDURE() AS ErrorProcedure,
     ERROR_LINE() AS ErrorLine,
     ERROR_MESSAGE() AS ErrorMessage

    ROLLBACK TRANSACTION
END CATCH

Marc

marc_s
That's a great snippet Marc but i think Hythloth is looking for a way to undo a screw up. Let's say he forgets a WHERE clause on an update and set all of a table's column to a single value... lol... that's what he wants to roll back from.
Paul Sasik
well - you can always replace the "COMMIT TRANSACTION" in my snippet with another ROLLBACK and just check the results of your query and then roll it back anyway; sort of an "what-if" query - apply it, see the results, and then still rollback anyway
marc_s
+10  A: 

Run your WHERE statement as SELECT before you run it as UPDATE or DELETE

Manu
+1: this is the most you can do if you are "asked" to do it on production database; in addition, ensure that you have a backup easily accessible/restorable
van
This is what I use, if the SELECT returns the expected amount of rows, the proceed with UPDATE or DELETE.
Joel
+1  A: 

One other thing you can do, though it will take practice: always write your WHERE clause first, so you never have to worry about running an UPDATE or DELETE on all rows.

Tom
I once submitted a Data Change request to the DBA for production Data, my manager didn't really look at the query, he approved it, the DBA executed it and after that every employee at my company was named "Tori M.". I changed the Employee Name and I forgot to include a where clause. Tori just got married and there was a problem with our sync routine with AD so we brute forced it with a data update. That was a bad night. Of course the SQL guys found out they had not setup a backup plan for this particular DB. Good times.
RandomNoob
A friend of mine if fond of saying "An UPDATE without a WHERE clause is a DELETE." I try to remember it every time I run an update on a live database.
Tom
+2  A: 

This particular statement has saved my butt at least twice.

SELECT * INTO Table2_Backup FROM Table1

I also agree wholeheartedly with Manu. SELECT before UPDATE or DELETE

Dayton Brown
A: 

Here is what I do when writing text to be run from a query window which I have done to fix bad data sent to us from the clients in an import (I always do this on dev first)

begin tran

delete mt
--select * 
from mytable mt where <some condition>

--commit tran
--rollback tran

begin tran

update mt
set myfield = replace(myfield, 'some random text', 'some other random text'
--select myid, myfield, replace(myfield, 'some random text', 'some other random text'
from mytable mt where <some condition>

--commit tran
--rollback tran

Note that this way, I can run the select part of the query to first see the records that will be affected and how. Note the where clause is onthe same line as the table (or the last join if I had mulitple joins) This prevents the oops I forgot to highlight the whole thing problem. Note the delete uses an alias so if you run just the first line by accident, you don't delete the whole table.

I've found it best to write scripts so they can be run without highlighting (except when I highlight just the select part to see what records I'm affecting). Save the script in source control. If you have tested the tscript by running on dev and QA, you should be fine to run it on prod without the selects. If you are affecting a large update or delete on a table, I almost always copy those records to a work table first so that I can go back immediately if there is a problem.

HLGEM
A: 

If you want to be (or have to be) really paranoid about this, you should have some kind of log table for old/new vals (table/column/old val /new val; maybe user and a timestamp) and fill that with a trigger on insert/update/delete. It's really a hassle to restore some old values from this, but it may be helpful if all else goes horribly wrong. There is a pretty big performance impact, though.

SAP is using this approach (called change docs in SAP parlance) for changes through its GUI and gives a programmers a way to hook into this for changes done through "programs" (although you have to explicitly call this).

IronGoofy