是从SQL Server 2008 R2中的事务日志撤消更改? [英] Reverse changes from transaction log in SQL Server 2008 R2?

问题描述

我们有一个SQL Server 2008 R2数据库，该数据库不时备份事务日志.今天，大约在12am左右导致数据库中出现了一个大错误...我的事务日志可能是在8am之前，然后是12am-16pm-等等.

We have a SQL Server 2008 R2 database that backs up transaction logs every now and then. Today there was a big error in the database caused at around 12am... I have transaction logs up to 8am and then 12am - 16pm - etc.

我的问题是:我可以将这些事务日志反向合并到数据库中，以便在上午8点回到数据库状态吗?

My question is: can I sort of reverse-merge those transaction logs into database, so that I return to the database state at 8am?

或者我是恢复旧的完整备份并还原所有事务日志的最佳机会，直到上午8点?

Or is my best chance to recover an older full backup and restore all transaction logs up to 8am?

第一种选择是可取的，因为前一阵子已经执行了完全备份，并且我担心从那里恢复和应用trn日志的事情会变得更加棘手.我是否为此错误地感到震惊?如果在这种情况下(还原完整备份并应用trn日志)，实际上是否有可能发生任何不良情况?

The first option is preferable since full backup has been performed a bit of a while ago and I am afraid to f*ck things up restoring from there and applying trn logs. Am I falsely alarmed about that? Is it actually possible for anything bad to happen if going by that scenario (restoring the full backup and applying trn logs)?

推荐答案

您不创建常规事务日志备份这一事实不会影响恢复过程的成功.只要您的数据库处于完全恢复模型中，事务就会存储在联机事务日志中，并保留在其中，直到进行事务日志备份为止.如果备份事务日志的时间比平常晚，则仅意味着联机事务日志可能会增长，并且备份可能会更大.不会导致任何交易历史记录丢失.

The fact that you don’t create regular transaction log backups doesn’t affect the success of the recovery process. As long as your database is in the Full recovery model, the transactions are stored in the online transaction log and kept in it until a transaction log backup is made. If you make a transaction log backup later than usual, it only means that the online transaction log may grow and that the backup might be bigger. It will not cause any transaction history to be lost.

借助完整的事务日志备份链可以追溯到上午8点，您可以成功地将整个数据库回滚到某个时间点.

With a complete chain of transaction log backups back to 8 AM, you can successfully roll back the whole database to a point in time.

关于还原完整备份和应用trn日志–应该没有问题，但是始终建议您首先在测试服务器上测试方案，而不是直接在生产环境中

As for restoring the full backup and applying trn logs – nothing should go wrong, but it’s always recommended to test the scenario on a test server first, and not directly in production

要还原到某个时间点:

1. 在SSMS中，展开数据库
2. 右键单击数据库，选择任务|恢复|数据库
3. 在常规"选项卡的备份"集中，将列出可用的备份.点击时间轴
4. 选择特定日期和时间，更改时间间隔以显示更大的时间范围，然后将滑块移至要回滚到的时间

1. In SSMS expand Databases
2. Right-click the database, select Tasks | Restore| Database
3. In the General tab, in the Backup sets the available backups will be listed. Click Timeline
4. Select Specific date and time, change the Time interval to show a wider time range, and move the slider to the time you want to roll back to

您可以在此处找到更详细的说明:如何:还原到某个时间点(SQL Server Management Studio)

You can find more detailed instructions here: How to: Restore to a Point in Time (SQL Server Management Studio)

请记住，此过程将回滚对数据库所做的所有更改.如果您只想回滚特定的更改(例如，仅恢复某些已删除的数据或撤消错误的更新)，建议您使用第三方工具，例如ApexSQL Log

Keep in mind that this process will roll back all changes made to the database. If you want to roll back only specific changes (e.g. only recover some deleted data, or reverse wrong updates), I suggest a third party tool, such as ApexSQL Log

还原您的SQL Server数据库回到特定的时间点

将数据库还原到某个时间点

免责声明:我是ApexSQL的支持工程师

这篇关于是从SQL Server 2008 R2中的事务日志撤消更改?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！