Nervous Running Queries (SQL Server): What do you think about this?

Question

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.

Answer 1

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.

Answer 2

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...

Answer 3

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

Answer 4

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

Answer 5

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.

Answer 6

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

Answer 7

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.

Answer 8

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).