如何撤消对数据库所做的更新语句
这是一个测试环境，我需要一些数据来测试更新查询，但不小心更新了所有行中的一列以获得错误的数据。我必须使用备份将数据恢复到以前的实例，还是有一些我可以利用的事务日志秘密？
提前致谢。
It's a test environment, I needed some data to test an Update query, but accidentally updated a column in all rows to have wrong data. Must I use a backup to restore the data back to the previous instance, or is there some secret with transaction log that I can take advantage of?
Thanks in advance.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
有一种称为事务日志的非秘密事务日志，您可以将其恢复到某个时间点。 方法如下... 那个带有 ldf 扩展名的烦人的小文件是事务日志，而不是普通数据库数据的 .mdf 文件。
除非您截断了事务日志 (ldf) 或以其他方式弄乱了它，否则您应该能够准确地执行您正在寻找的恢复（撤消）类型。
There is a non-secret transaction log called transaction log that you can recover from to a point in time. Here's how... That annoying little file with the ldf extension is the transaction log, as opposed to the .mdf file that is your normal db data.
Unless you have truncated the transaction log (ldf) or otherwise mucked with it, you should be able to do exactly the kind of restore (undo) that you're looking for.
如果您的数据库处于完全恢复模式，那么您可以尝试使用第三方工具读取事务日志，例如这个 或者您可以尝试使用 DBCC LOG 命令自行执行此操作。
如果数据库处于完全恢复状态，那么大量数据将存储在事务日志中，但它不容易读取，因为 MS 从未为此完善过官方文档，而且它的目的不是恢复，而是确保事务正确提交。
然而，有一些解决方法可以读取它，例如使用上面的工具（不幸的是付费工具，但有试用版）或自己解码 DBCC LOG 的结果。
If your database was in full recovery mode then you can try reading transaction log using third party tool such as this one or you can try doing this yourself with DBCC LOG command.
If db is in full recovery then a lot of data is stored in transaction log but it’s not easily readable because MS never polished official documentation for this and because it’s purpose is not recovery but making sure transaction is committed correctly.
However there are workarounds to reading it like using the tool above (paid tool unfortunately but has a trial) or decoding the results of DBCC LOG yourself.
除非您将 sql 包装在事务块中 - 开始事务、回滚、提交。这是 SQL Server 的危险之一。使用 Oracle，您必须物理提交每个事务，恕我直言，这更安全。
Not unless you wrapped your sql in a transaction block - begin transaction, rollback, commit. That one of the dangerous things about sql server. With Oracle you have to physically commit each transaction which is much safer imho.