SQL Server LOG数据恢复
我想知道如何从日志文件中恢复数据。 我正在使用 sql 2005（完整备份）。
问题是昨晚有一项服务清除了我的数据（它不应该清除）。 现在我想恢复之前删除的那些行。
谁能告诉我我该怎么做？
I was wondering how you can recover data from your logfile.
I'm using sql 2005 (full backup).
The problem is that a service cleared my data last night (which it shouldn't have).
and now I want to recover those rows that were deleted before.
can anyone tell me how I can do this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
只要您有删除之前的数据库备份，以及自上次数据库备份以来所做的所有事务日志备份，那么您就能够恢复到某个时间点。
首先要做的是备份事务日志。
然后，您将上次数据库备份以及此后的所有事务日志备份恢复到删除之前的时间点。
请参阅这篇 MSDN 文章了解如何执行此操作。
我建议您将现有数据库保留原样，并将备份恢复到新数据库。然后，您可以编写一些脚本将所需的数据传输回实时数据库。
As long as you have a backup of your database from before the delete and then all transaction log backups that have been made since the last database backup then you will be able to restore to a point in time.
The first thing to do is take a backup of the transaction log.
Then you restore your last database backup and all transaction log backups since then up to the point in time just before the delete.
See this MSDN Article on how to do it.
I would suggest that you leave your existing database in place as it is and restore the backups to a new database. Then you can write some scripts to transfer the required data back into your live database.
首先，您的数据库必须处于完整恢复模式，并且您必须拥有完整的事务日志备份链 - 一系列具有不间断的日志序列号 (LSN) 序列的日志记录。
日志备份链在完全恢复时启动。进行数据库备份或恢复模式从SIMPLE切换到FULL并进行完整备份。之后，定期创建事务日志备份。日志备份链只能通过两种方式中断：
中断日志备份链可能会导致事务信息丢失
您可以使用以下命令恢复到某个时间点：
语法
事务可以在此处找到所有列出的选项的步骤：恢复数据库到某个时间点
免责声明：我在 ApexSQL 担任支持工程师
First of all, your database must be in the full recovery model and you must have a full chain of transaction log backups - a series of log records having an unbroken sequence of log sequence numbers (LSNs)
The log backup chain is started when a full database backup is made or the recovery model is switched from SIMPLE to FULL and a full backup is made. After that, transaction log backups are created on regular basis. The log backup chain can be broken only in two ways:
Breaking the log backup chain can lead to missing transaction information
You can restore to a point in time using:
Syntax
You can find the steps for all listed options here: Restore a database to a point in time
Disclaimer: I work for ApexSQL as a Support engineer
仅在以下情况下才能恢复您的数据：
1）数据库采用完全恢复模式；
2）您有意外删除之前所做的完整备份；
3) 您还没有再次恢复或备份该数据库。
如果这是正确的，您应该：
1) 进行事务日志备份；
2) 使用 NORECOVERY 选项从完整备份恢复数据库；
3) 使用 STOPAT 选项恢复事务日志。
Your data can be recovered only in case:
1) database uses full recovery model;
2) you have full backup that you make before accidental deleting;
3) you have have NOT recover or backup again this database yet.
If this is correct, you should:
1) make transaction log backup;
2) restore database from full backup WITH NORECOVERY option;
3) restore transaction log using STOPAT option.
要将事务日志文件恢复到某个时间点，您的数据库必须在完全恢复模式下运行。因此，首先您必须还原最新的完整数据库备份：
下一步是还原最后一个差异数据库备份：
然后以正确的顺序还原自上次差异备份以来所做的所有事务日志备份
最后一个事务日志备份必须是Restored 是在发生故障后使用
stopat选项进行的事务日志备份。在stopat选项之后，您应该设置要恢复数据库的时间。To restore transaction log files to a point-in-time your database must run under full recovery model. So firstly you have to restore the latest full database backup:
The next step is to restore the last differential database backup:
And then restore all transaction log backups that have made since last differential backup in correct sequence
The last one transaction log backup that must be restored is the transaction log backup that have been made after the failure occurred with
stopatoption. Afterstopatoption, you should set up the time to which you want to restore your database.