Safely delete rows in sqlserver so you can rollback a commit

Question

I asked this question yesterday Funky Delete Issue

Now I'd like to now how to delete safely.

So how do I use commits and transactions and that syntactical sugar to cover my @$$ because yesterday I dumbly deleted 51,000 rows. I had a backup but I still thought HOLY ^%$# that was too easy.

So how do I safely:

    DELETE FROM bBoxHeader
    WHERE bBoxHeader.bHeaderId <> '1099' 
    -- Ooops meant that to be equal. How do I roll back?

How do I wrap that so I don't blow away 51000 rows

Answer 1

Whenever you're doing unverified, ad hoc DML against production data, you should always wrap it in a BEGIN TRANSACTION with a subsequent COMMIT and ROLLBACK. If you run it without checking and then realize you messed it up, you can roll it back. Otherwise you can commit it.

BEGIN TRANSACTION;

DELETE ... WHERE ...

-- COMMIT TRANSACTION;
---^^^^^^ if the number of rows affected is correct, highlight this & execute

-- ROLLBACK TRANSACTION;
---^^^^^^^^ otherwise highlight this and execute

Note that this can ALSO cause you to say HOLY whatever because if you forget to run either the rollback or the commit, then go to lunch or go home for the weekend, you might come back to work looking at your pink slip.