安全删除 sqlserver 中的行，以便您可以回滚提交 [英] Safely delete rows in sqlserver so you can rollback a commit

问题描述

我昨天问了这个问题 Funky 删除问题

现在我想知道如何安全删除.

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

那么我如何使用提交和事务以及语法糖来覆盖我的@$$，因为昨天我愚蠢地删除了 51,000 行.我有一个备份，但我仍然认为 HOLY ^%$# 这太容易了.

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.

那么我该如何安全:

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

我如何包装它，这样我才不会吹走 51000 行

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

推荐答案

每当您对生产数据进行未经验证的临时 DML 时，您应该始终将其包装在 BEGIN TRANSACTION 中，并带有后续的 COMMIT 和 ROLLBACK.如果你不检查就运行它，然后然后意识到你搞砸了，你可以回滚它.否则你可以提交它.

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

请注意，这也可能导致您说 HOLY 无论如何，因为如果您忘记运行回滚或提交，然后去吃午饭或回家过周末，您可能会回到工作中查看您的粉红单.

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.

这篇关于安全删除 sqlserver 中的行，以便您可以回滚提交的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！