如何在 SQL Server 2005 中撤消删除操作？
我们的测试数据库突然缺少行。 我们希望他们回来。
有没有办法筛选今天数据库发生的所有事情？ 每条SQL语句？ 我认为这种东西在事务日志中，但不知道如何查看它。
有没有办法撤消删除操作？
顺便说一句：是的，我们确实有备份，但也更愿意找到删除的原因......
Our Test DB is suddenly missing rows. We want them back.
Is there a way to sift through everything that has happened to the database today? Each SQL statement? I presume this kind of stuff is in the transaction log, but am not sure how to view it.
Is there a way to undo delete operations?
BTW: Yes, we do have a backup, but would prefer to find the cause of the deletion as well...
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
[迟到的答案，但希望有用]
有一种方法可以使用事务日志恢复已删除的行，但前提是您使用第 3 方工具并且只有数据库处于完全恢复模式。
Dell（以前称为 Quest）有 Toad for SQL Server，ApexSQL 有ApexSQL 日志 和 ApexSQL Recover，还可以读取 t-log 并恢复数据。 不幸的是，Red Gate 的 Log Rescue 只能读取 SQL Server 2000 上的日志。
还有一种方法可以使用未记录的 dbcc log 命令读取 t-log。 在此处查看更多详细信息。
考虑到您已经有数据库备份，您可以在单独的数据库中恢复这些备份，然后使用市场上存在的多种数据比较工具之一将丢失的数据插入生产数据库。 当然这只能恢复创建备份之前删除的数据。
[late answer but hopefully useful]
There is a way to recover deleted rows using transaction log but only if you use 3rd party tools and only of your database is in full recovery mode.
Dell (formerly Quest) has Toad for SQL Server and ApexSQL has ApexSQL Log and ApexSQL Recover that can also read t-log and recover data. Unfortunately Log Rescue from Red Gate can only read logs on SQL Server 2000.
There is also a way to read t-log using undocumented dbcc log command. See more details here.
Considering you already have database backups you can restore these in separate database and then use one of many data comparison tools that exist on the market to insert missing data into production database. Of course this can only recover data delete prior to creating a backup.
您可以使用 Red Gate 的一些工具来完成此操作，但需要付费。 查看SQL 日志救援。
否则，我会很想进行恢复。
You can do this with some of Red Gate's tools, but it costs. Take a look at SQL Log Rescue.
Otherwise, I'd be tempted to do a restore.
您需要第三方工具来执行此操作。 该工具必须能够进入事务日志并查看日志条目，以便您可以了解发生了什么。 我没有使用过这些工具，但我会尝试使用 Red Gate 的 SQL Log Rescue 作为初学者。 尝试一下：
http://www.red-gate.com/products /SQL_Log_Rescue/index.htm
You need a third-party tool to do this. The tool has to be able to go into the transaction logs and view the log entries so you can see what happened. I haven't used any of these tools, but I'd try Red Gate's SQL Log Rescue for starters. Give it a try:
http://www.red-gate.com/products/SQL_Log_Rescue/index.htm
我会尝试 ChronicDB，它有一个免费版本，而不是付费购买 Red Gate
http://chronicdb.com/blogs /undelete_from_whoops
I'd give ChronicDB a try which has a free version rather than pay for Red Gate
http://chronicdb.com/blogs/undelete_from_whoops
您可以使用 SQL Server 中的
Rollback命令撤消事务。 但是，您需要知道如果事务是在Begin事务内执行的，则该事务可以回滚。You can undo a transaction by using
Rollbackcommand in SQL Server. But, you need to know the transaction can be rolledback if the transaction were performed withinBegintransaction.