了解SQL Server事务日志备份和完整备份的日志序列号

This article explores the SQL Server Transaction log backups and log sequence number (LSN) in combination with the Full backups.

本文探讨了SQL Server事务日志备份和日志序列号（LSN）与完整备份的组合。

SQL Server备份简介 (SQL Server Backup Introduction)

The database backups are crucial for database recovery and disaster planning. It is the primary duty of a DBA to define the backup policy for each database based on the criticality, Recovery time object (RTO) and Recovery Point Objective (RPO). The database backups are useful even if you implemented the disaster recovery solutions like HADR SQL Server Always On. To meet these requirements, we schedule native or third-party backup tools to take database backups. We have the following database backups in SQL Server.

数据库备份对于数据库恢复和灾难计划至关重要。 DBA的主要职责是根据重要性，恢复时间对象（RTO）和恢复点目标（RPO）为每个数据库定义备份策略。 即使您实施了灾难恢复解决方案（例如HADR SQL Server Always On），数据库备份也很有用。 为了满足这些要求，我们安排本机或第三方备份工具进行数据库备份。 我们在SQL Server中具有以下数据库备份。

• Full backup: It is a complete database backup and allows to restore database till the time backup was completed. It is the most straightforward form of database backup 完全备份：这是完整的数据库备份，可以还原数据库直到备份完成。 这是最简单的数据库备份形式
• Differential backup: It contains changes from the last full backup. These are cumulative backups 差异备份：它包含自上次完整备份以来的更改。 这些是累积备份
• Log backup: It takes SQL Server transaction log backup and contains data from the last log backup or first full database backup 日志备份：需要执行SQL Server事务日志备份，并包含上次日志备份或首次完整数据库备份中的数据

We are not going to talk in detail about these backup types. You can refer to the article Understanding SQL Server Backup Types to gather details about them.

我们不会详细讨论这些备份类型。 您可以参考文章了解SQL Server备份类型以收集有关它们的详细信息。

DBA combines these database backups to have a backup policy of a database. Usually, for large databases, we take a weekly full backup and the combination of differential and log backups in between. These database backups build a log chain, and it is very critical to maintain the log chain for database backups. We should also be aware of the actions that can break the log sequence. If the LSN chain is broken, it is difficult to restore the database, and in case of any disaster, if we cannot restore the database, it might create a problematic scenario for the DBA.

DBA组合了这些数据库备份以具有数据库的备份策略。 通常，对于大型数据库，我们每周进行一次完整备份，并在两者之间进行差异备份和日志备份的组合。 这些数据库备份会建立一个日志链，因此维护数据库备份的日志链非常关键。 我们还应该意识到可能破坏日志序列的操作。 如果LSN链断开，则很难还原数据库，如果发生任何灾难，如果我们无法还原数据库，则可能会给DBA带来问题。

Suppose we have the following backup policy for a critical database.

假设我们对关键数据库具有以下备份策略。

• Weekly Full backup
  每周完整备份
• Daily differential backup
  每日差异备份
• Hourly SQL Server transaction log backup
  每小时SQL Server事务日志备份

In the above scenario, let’s say someone took a Full database backup after the SQL Server Transaction Log backup.

在上述情况下，假设某人在SQL Server事务日志备份之后进行了完整数据库备份。

问题 (Questions)

Now let me ask a few questions here:

现在让我在这里问几个问题：

• Will the full backup break the LSN chain?
  完整备份会中断LSN链吗？
• The transaction log backup after the full backup contains data from the full backup or not?
  完全备份后的事务日志备份是否包含来自完全备份的数据？
• If the database size is huge and full backup takes 4-5 hours to complete, what happens to hourly log backup?
  如果数据库很大，并且完全备份需要4到5个小时才能完成，那么每小时日志备份会怎样？
• Would log backup work while the full backup is in progress?
  正在进行完整备份时，日志备份会工作吗？

If you know the answers to these questions, you can skip this article. I am sure most of the DBA would be confused and fail to answer these questions.

如果您知道这些问题的答案，则可以跳过本文。 我确信大多数DBA都会感到困惑，并且无法回答这些问题。

SQL Server备份和LSN概述 (Overview of SQL Server backups and LSN)

Let’s prepare the environment to explore answers to these questions.

让我们准备一个环境来探索这些问题的答案。

示例1：完整数据库备份