差异和事务日志恢复 [英] Diff and Transaction Log restores
问题描述
全部，
我需要澄清一下。
我们设置完整和差异数据库备份以及事务日志备份以及以下内容时间表：
We setup Full and Differential database backups along with Transaction Log backup with the following schedule:
完整数据库备份 - 每天晚上10点
Full Database Backup – 10 PM daily
差异数据库备份 - 每3小时发生
Differential Database Backup – Occurs every 3Hrs
交易日志备份 - 每15分钟发生一次
Transaction Log Backup - Occurs every 15 Mins
由于这是一个新的设置，我们试图确保备份确实可以恢复，我们尝试了不同的恢复方法，如下所示：
Since this a new setup, we were trying to make sure that backups are indeed restorable and we tried different ways to restore as follows:
完整备份和事务日志备份（第5次）： - 我们能够恢复完整数据库备份，然后将所有事务日志备份一直应用到第5个备份。
Full backup and Transaction Log backups (5th) :- We were able to restore Full database backup and then apply all the transaction log backups all the way upto the 5th one.
完全备份，Diff（第3个） 和事务日志备份（第5个）： - 我们能够恢复完整数据库备份和差异数据库备份
但是当我们将事务日志备份一直应用到第5个时，它为第4个事务日志b的恢复提供了错误ackup。
Full backup, Diff (3rd) and Transaction Log backups (5th) :- We were able to restore Full database backup and Differential database backup but when we applied the transaction log backups all the way upto the 5th it gave an error on the restore for 4th Transaction Log backup.
<<此备份集中的日志终止于LSN 44000011141400001，现在申请数据库为时尚早。可以恢复包含LSN 44000011141900001的更新的日志备份。>>
但是我们可以跳过第4个事务日志并在Diff之后应用第5个事务日志。所以我的问题是：
第二个的last_lsn（交易） Log）是第4个的first_lsn（事务日志），diff的last_lsn是
44000011141900001 小于last_lsn （ 44000011239500001）
第4个（交易日志） 这意味着有仍然在第4个事务日志中保留的事务不在Diff中。那么，即使我们正在跳过
恢复第4个事务日志 备份并应用第5个？
The last_lsn for 2nd (Transaction Log) is the first_lsn for 4th (Transaction Log) and last_lsn for diff is 44000011141900001 which is less than the last_lsn(44000011239500001) for 4th (Transaction Log) which means there are still transactions left in the 4th Transaction log that is not in the Diff. So, how is that the restores go through even though we are skipping the restore of the 4th Transaction Log backup and applying the 5th one?
谢谢，
rajee
序列
Sequence
backup_start_date
backup_start_date
backup_finish_date
backup_finish_date
BackupType
BackupType
first_lsn
first_lsn
last_lsn
last_lsn
1
2019-05-20 23：45：01.000
2019-05-20 23：45：01.000
日志
44000010996500001
44000011070200001
2
2019-05-21 00：00：01.000
2019-05-21 00:00:01.000
2019-05-21 00：00：01.000
2019-05-21 00:00:01.000
日志
Log
44000011070200001
44000011070200001
44000011141400001
44000011141400001
3
2019-05-21 00：00：01.000
2019-05-21 00:00:01.000
2019-05-21 00：00：01.000
2019-05-21 00:00:01.000
DIFF
DIFF
44000011070200001
44000011070200001
44000011141900001
44000011141900001
4
2019-05-21 00：15：01.000
2019-05-21 00:15:01.000
2019-05-21 00：15：01.000
2019-05-21 00:15:01.000
日志
Log
44000011141400001
44000011141400001
44000011239500001
44000011239500001
5
2019-05-21 00：30：01.000
2019-05-21 00:30:01.000
2019-05-21 00：30：01.000
2019-05-21 00:30:01.000
日志
Log
44000011239500001
44000011239500001
44000011311800001
44000011311800001
6
2019-05-21 00：45：01.000
2019-05-21 00：45：01.000
日志
44000011311800001
44000011390000001
推荐答案
嗨grajee，
>>那么，即使我们正在跳过第4个交易记录备份的恢复并应用第5个备份，恢复是如何进行的呢？
根据您的描述，您是否要按照序列Full（第1）> Diff（3th）> log（5th）恢复数据库？如果我误解了，请让我知道。
事务日志备份自上次日志备份以来的所有日志，事务日志LSN链不受完整或差异数据库备份的影响。
Hi grajee,
>>So, how is that the restores go through even though we are skipping the restore of the 4th Transaction Log backup and applying the 5th one?
From your description, did you want to restore the database follow the sequence Full(1th)>Diff(3th)>log(5th)? If I misunderstood, please let me know.
Transaction log backup all logs since the last log backup, and the transaction log LSN chain is not affected by a full or differential database backup.
事务日志包含一系列日志记录，这些日志记录在逻辑结束时记录到日志文件的末尾。 rd有一个独特的LSN。如果日志中的LSN2> LSN1指示LSN2位于LSN1之后。
The transaction log consists of a series of log records that are logged to the end of the log file at the end of the logic. Each log record has a unique LSN. If LSN2>LSN1 in the log indicate that LSN2 is located after LSN1.
当您计划在还原差异备份后使用哪个日志备份前滚时，通常从差异备份后的第一次日志备份。在完整数据库备份或差异
备份（如果还原一个）之后以及特定事务日志备份之前执行的所有事务日志备份。日志备份必须按照创建它们的顺序应用，日志链中没有任何间隙。
您可以获得有关备份和日志序列号的更多信息。从下面的文档中恢复SQL数据库的不同方法。请参阅
了解SQL备份的服务器日志序列号和
恢复SQL Server数据库的不同方法。
希望这可以帮到你。
祝你好运，
Cathy Ji
When you are planning which log backup to use to roll forward after the restore of differential backup, you generally start with the first log backup after differential backup. All transaction log backups taken after the full database backup or the differential
backup (if you restore one) and before the particular transaction log backup. Log backups must be applied in the sequence in which they were created, without any gaps in the log chain.
You can get more information about log sequence numbers for backups and different ways to restore SQL a database from below documents. Please refer to
Understanding SQL Server Log Sequence Numbers for Backups and
Different Ways to Restore a SQL Server Database.
Hope this could help you.
Best regards,
Cathy Ji
这篇关于差异和事务日志恢复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
