如何撤消 SQL Server UPDATE 查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4691574/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to undo a SQL Server UPDATE query?
提问by Stefan Steiger
In SQL Server Management Studio, I did the query below.
Unfortunately, I forgot to uncomment the WHEREclause.
1647 rows were updated instead of 4.
在 SQL Server Management Studio 中，我做了以下查询。
不幸的是，我忘记取消注释该WHERE条款。
更新了 1647 行而不是 4。
How can I undo the last statement?
如何撤消最后一条语句？
Unfortunately, I've only just finished translating those 1647 rows and was doing final corrections , and thus don't have a backup.
不幸的是，我刚刚翻译完那 1647 行，正在做最后的更正 ，因此没有备份.
UPDATE [dbo].[T_Language]
SET
[LANG_DE] = 'Mietvertrag' --<LANG_DE, varchar(255),>
,[LANG_FR] = 'Contrat de bail' -- <LANG_FR, varchar(255),>
,[LANG_IT] = 'Contratto di locazione' -- <LANG_IT, varchar(255),>
,[LANG_EN] = 'Tenancy agreement' -- <LANG_EN, varchar(255),>
--WHERE [LANG_DE] like 'Mietvertrag'
There is a transaction protocol, at least I hope so.
有一个交易协议，至少我希望如此。
采纳答案by a_horse_with_no_name
A non-committed transaction can be reverted by issuing the command ROLLBACK
可以通过发出命令来恢复未提交的事务 ROLLBACK
But if you are running in auto-commit mode there is nothing you can do....
但是，如果您在自动提交模式下运行，则您无能为力....
回答by Newbi
If you already have a full backup from your database, fortunately, you have an option in SQL Management Studio. In this case, you can use the following steps:
如果您已经拥有数据库的完整备份，幸运的是，您可以在 SQL Management Studio 中进行选择。在这种情况下，您可以使用以下步骤：
Right click on database -> Tasks -> Restore -> Database.
In General tab, click on Timeline -> select Specific date and time option.
Move the timeline slider to before update command time -> click OK.
In the destination database name, type a new name.
In the Files tab, check in Reallocate all files to folder and then select a new path to save your recovered database.
In the options tab, check in Overwrite ... and remove Take tail-log... check option.
Finally, click on OK and wait until the recovery process is over.
右键单击数据库 -> 任务 -> 还原 -> 数据库。
在常规选项卡中，单击时间轴 -> 选择特定日期和时间选项。
将时间线滑块移动到更新命令时间之前 -> 单击确定。
在目标数据库名称中，键入一个新名称。
在“文件”选项卡中，选中“将所有文件重新分配到文件夹”，然后选择一个新路径来保存恢复的数据库。
在选项选项卡中，检查 Overwrite ... 并删除 Take tail-log... check 选项。
最后，单击“确定”并等待恢复过程结束。
I have used this method myself in an operational database and it was very useful.
我自己在一个操作数据库中使用过这种方法，它非常有用。
回答by Frank Baker
Considering that you already have a full backup I'd just restore that backup into separate database and migrate the data from there.
考虑到您已经有了完整备份，我只是将该备份还原到单独的数据库中并从那里迁移数据。
If your data has changed after the latest backup then what you recover all data that way but you can try to recover that by reading transaction log.
如果您的数据在最新备份后发生了更改，那么您可以通过这种方式恢复所有数据，但您可以尝试通过读取事务日志来恢复该数据。
If your database was in full recovery mode than transaction log has enough details to recover updates to your data after the latest backup.
如果您的数据库处于完全恢复模式，则事务日志有足够的详细信息来恢复最新备份后的数据更新。
You might want to try with DBCC LOG, fn_log functions or with third party log reader such as ApexSQL Log
您可能想尝试使用 DBCC LOG、fn_log 函数或第三方日志阅读器，例如ApexSQL Log
Unfortunately there is no easy way to read transaction log because MS doesn't provide documentation for this and stores the data in its proprietary format.
不幸的是，没有简单的方法来读取事务日志，因为 MS 没有为此提供文档并以其专有格式存储数据。
回答by Will Strohl
If you can catch this in time and you don't have the ability to ROLLBACKor use the transaction log, you can take a backup immediately and use a tool like Redgate's SQL Data Compareto generate a script to "restore" the affected data. This worked like a charm for me. :)
如果您能及时发现，而您没有能力ROLLBACK或使用事务日志，您可以立即进行备份并使用Redgate 的 SQL Data Compare 之类的工具生成脚本来“恢复”受影响的数据。这对我来说就像一种魅力。:)
回答by Raj More
Since you have a FULL backup, you can restore the backup to a different server as a database of the same name or to the same server with a different name.
由于您有完整备份，您可以将备份还原到不同的服务器作为同名的数据库或具有不同名称的同一服务器。
Then you can just review the contents pre-update and write a SQL script to do the update.
然后，您可以查看更新前的内容并编写 SQL 脚本来进行更新。
