SQL Server中事务日志管理的步骤，第5级：完全恢复模式管理日志（译）

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

SQL Server中事务日志管理的步骤，第5级：完全恢复模式管理日志

作者：Tony Davis，2012/01/27

系列

本文是进阶系列的一部分：SQL Server中事务日志管理的步骤

当事情进展顺利时，无需特别注意事务日志的作用或工作方式。您只需要确信每个数据库都有正确的备份机制。当出现问题时，了解事务日志对于采取纠正措施很重要，特别是在需要紧急恢复数据库的时间点时！Tony Davis给出了每个DBA都应该知道的正确的细节级别。

在此级别中，我们将回顾在完全恢复模式下工作时进行日志备份的原因和方法，以及如何使用这些日志备份文件以及完整的数据库备份执行数据库还原。完全恢复模式支持将数据库还原到可用日志备份中的任何时间点，并且假设可以进行尾日志备份，直到发生故障之前最后一次提交事务的时间为止。

什么会被记录？

在完全恢复模式下，将完全记录所有操作。对于插入、更新和删除操作，这意味着对于修改的每一行，都会有一个日志记录，描述执行语句的事务的ID、事务开始和结束时、哪些页已更改、所做的数据更改等。

在完全恢复模式下工作时，可以最低限度地记录的操作（选择进入、大容量插入和创建索引）仍然完全记录，但执行方式略有不同。受这些操作影响的行不是单独记录的；而是在数据库页被填充时只记录它们。这减少了对此类操作的日志记录，同时确保仍然存在执行回滚、重做和时间点还原所需的所有相同信息。Kalen Delaney已经发布了一些关于select into（）和index rebuild（）操作的日志调查，包括完整和批量日志恢复模式。在大容量日志记录模式下工作时，最小日志记录操作的日志记录差异将在第6级-在大容量日志记录恢复模式下管理日志中进行更详细的讨论。http://sqlbog.com/blogs/kalen_delaney/archive/2011/03/15/what-gets-logged-for-select-into.aspxhtp://sqlbog.com/blogs/kalen_delaney/archive/2011/03/08/what-gets-logged-for-index-rebuilds.aspx

为什么备份事务日志？

在完全恢复模式下，只有日志备份才能导致日志截断。因此，事务日志将保存自上次备份事务日志以来执行的事务的完整记录。由于所有操作都已完全记录，因此在繁忙的系统中，日志文件可能会增长得非常大、非常快。

因此，在完全恢复模式下工作时，除了执行完全备份和（可选）差异备份之外，还必须执行常规事务日志备份。许多新手或兼职DBA在其数据库上执行完整备份，但不执行事务日志备份。因此，事务日志不会被截断，并且会不断增长，直到它所在的驱动器耗尽磁盘空间，导致SQL Server停止工作。

在执行日志备份后，会立即截断日志，前提是自上次备份以来已发生检查点，并且没有其他因素会延迟截断，例如数据备份或还原操作。有关可能延迟截断可恢复VLF的因素的完整列表，以及保持大量日志活动的因素（否则不需要），如恶意、长时间运行的未提交事务或数据库镜像或复制进程，请参阅：http://msdn.microsoft.com/en-gb/library/ms345414.aspx。

仅复制事务日志的备份

只复制事务日志的备份不会截断事务日志。仅复制日志备份与正常日志备份方案“独立”存在；它不会中断日志备份链。

简而言之，事务日志备份的双重目的是允许恢复和恢复到以前的时间点，并控制事务日志的大小。与事务日志相关的问题最常见的原因可能是在完全恢复模式下工作，而不是简单地进行日志备份，或者不经常进行日志备份以控制事务日志文件的大小。

如果您不确定是否在给定的数据库上执行事务日志备份，那么您可以使用类似于列表5.1所示的查询，简单地查询msdb数据库中的backupst表。

列表5.1：是否正在进行日志备份？

在类型列中，D表示数据库备份，L表示日志备份，I表示差异备份。

请注意，由于可以在不影响备份和还原行为的情况下操作此backupset表中的数据，因此您可能希望通过查询sys.database_recovery_status来验证此查询的结果，以查看最后一个log_backup_lsn的值（请参见列表3.5），或查看sys.databases表以查看log_reuse_wait_desc的值（将返回如果需要备份，则返回urn log_backup）。

如何备份事务日志

如前所述，不首先进行至少一次完整备份，就不可能执行事务日志备份。实际上，如果您有一个处于完全恢复模式但从未备份过的数据库，那么它实际上不会在完全恢复模式下工作。在执行第一次完全备份之前，数据库将处于自动截断模式。

所有数据库备份（完整、日志或其他）都使用backup命令执行。该命令接受许多选项，这些选项记录在下面：http://msdn.microsoft.com/en-us/library/ms186865.aspx。但是，在最基本的情况下（通常是这样使用的），执行磁盘完全备份的命令如下：

如果这是要执行的第一次备份，则将在指定目录中创建databasename.bak文件。如果这样的文件已经存在，那么默认行为是将后续备份附加到该文件。要覆盖此行为并规定应覆盖任何现有文件，可以使用init选项，如下所示：

但是，最常见的情况是，每个后续备份都有一个唯一的名称；在接下来的部分中，将详细介绍恢复到故障点。

在每次常规（如每日）完全备份之后，都会有频繁（如每小时）的日志备份，其基本命令非常类似：

存储日志备份

显然，备份的数据和日志文件不应存储在承载实时文件的同一驱动器上。如果该驱动器出现硬件故障，那么您的所有副本以及活动文件都将丢失，备份将是徒劳的。文件应备份到单独的设备，或备份到本地镜像驱动器。

日志备份频率

如前几级所述，您可能每15分钟进行一次日志备份，甚至可能更频繁。在这种情况下，为了避免需要恢复大量的事务日志文件，您可以选择采用一种备份方案，该方案包括完整备份和差异备份，以及事务日志备份。

事实上，备份方案往往是在理想和实际之间、对数据丢失的真实风险的评估与公司将付出的代价以及降低风险所涉及的成本之间进行折衷。许多非常重要的业务应用程序使用的备份方案都比较简单，但也比较严格，可能包括定期的夜间完整备份和每小时事务日志备份。

日志备份的频率也可能由数据库所属的事务数决定。对于非常繁忙的数据库，可能需要经常备份以控制日志的大小。

没有简单的方法来计算日志备份的频率。大多数DBA将对日志备份的频率进行最佳估计，然后观察文件的增长特性，然后根据需要调整备份方案，以防止文件过大。

如何打破日志链

如前所述，如果不首先进行至少一次完整备份，就不可能执行事务日志备份。为了将数据库恢复到某个时间点，或者恢复到特定日志备份的末尾，或者恢复到特定日志备份中的某个时间点，必须存在一个完整的完整的日志记录链，从完整（或差异备份）之后的第一个日志备份到故障点。这就是所谓的日志链。

有很多方法可以破坏日志链，如果这样做，则意味着您只能将数据库恢复到破坏日志链的事件发生之前的日志备份时间。简而言之，如果您关心恢复数据的能力，那么断开链并不是一个好主意。断链最常见的两种方法包括：

•事务日志备份文件丢失或损坏

您只能恢复到上一次良好日志备份。日志链将在下一次良好的完整备份或差异备份时重新启动。

•切换到简单恢复模式

如果您从完全恢复模式切换到简单恢复模式，这将破坏日志链，因为将启动检查点，并且可以立即截断事务日志。当且如果您返回到完整模式，则需要进行另一个完整备份以重新启动日志链。实际上，在进行完整备份之前，数据库将保持自动截断模式，并且您将无法备份日志文件。

在SQL Server 2008之前，有两个命令，即backup log with no_log或backup log with truncate_only（它们在功能上是等效的），在发出命令时，会强制截断日志文件，从而中断日志链。在任何版本的SQL Server中都不应该发出这些命令，但我在这里提到这些命令，因为在尝试处理“失控日志文件”时，粗心的人仍然会使用这些命令，而不理解它对他们恢复数据库的能力的影响。请参阅第8级-帮助，我的日志已满，了解更多详细信息。

末尾日志备份

只要您有一个最近的完整备份和一个完整的日志链，您就可以将数据库恢复到在任何失败之前的最终日志备份结束时的状态。但是，假设您每小时进行一次事务日志备份，并且在下午1:45发生故障。您可能会丢失价值45分钟的数据；实际上，如果失败是如此灾难性，以至于实时事务日志无法恢复，那么这就是您将丢失的数据量。

但是，有时即使数据文件不在，实时事务日志仍然可用，特别是当事务日志包含在单独的专用驱动器上时。如果是这种情况，则应备份实时事务日志，即对自上次日志备份以来生成的日志记录执行最终备份。这将捕获活动日志文件中的剩余日志记录，直到出现故障为止。这称为尾日志备份，是在开始恢复和恢复操作之前应执行的最后一个操作。

末尾日志备份和最小日志操作

如果由于数据库故障而导致数据文件不可用，并且日志的尾部包含最少的日志操作，那么就无法进行尾部日志备份，因为这需要访问数据文件中更改的数据扩展数据块。这将在第6级中更详细地介绍，以大容量日志模式管理事务日志。

如果要还原的数据库处于联机状态，则日志的尾部将按以下方式进行备份：

NORECOVERY选项将数据库置于还原状态，并假定要执行的下一个操作是还原。如果数据库处于脱机状态且无法启动，则仍应尝试按照刚才描述的方式备份日志的尾部（尽管可以省略norecovery选项，因为不会进行任何事务）。

如果您确定日志文件已损坏，文档建议作为最后一种方法，您尝试使用以下方法进行尾日志备份：

如果主数据库和数据文件已损坏，但日志可用，Microsoft建议重建主数据库，然后备份上一个活动日志。但是，这些主题不在这个进阶的范围内，我将参考文档了解更多的细节。请参阅http://msdn.microsoft.com/en-us/library/ms190952.aspx。

执行还原和恢复

在执行了尾日志备份之后，如果可能的话，下一步是还原最后一次完整备份（如果合适，在后面是差异备份），然后还原日志备份文件的完整序列，包括尾日志备份。此还原操作序列的基本语法如下：

如果还原时省略WITH NORECOVERY选项，则默认情况下，还原命令将继续进行恢复。换句话说，SQL Server将尝试协调数据和日志文件，前滚已完成的事务，然后根据需要回滚未完成的事务。通过使用norecovery指定，我们将指示SQL Server在执行任何回滚之前，我们正在输入一个还原序列，并且必须前滚更多操作。在还原顺序中还原最后一个备份后，可以按以下方式恢复数据库：

RESTORE DATABASE DatabaseName WITH RECOVERY

一个常见的要求是将数据库还原到不同的位置，在这种情况下，您可以简单地将文件作为还原过程的一部分移动，如下所述：http://msdn.microsoft.com/en-us/library/ms190255.aspx。

数据库故障后恢复

下面的示例描述了如何恢复数据库以响应故障，从而使数据库数据文件不再可访问。

完全恢复到故障点

假设“实时”事务日志可以在数据库故障（可能由硬件故障引起）后访问，那么理论上，应该可以使用以下步骤恢复和恢复数据库，直至故障点：

1  备份日志的尾部

2  恢复最新的完整备份（如果适用，还包括差异备份）

3  依次还原在完全（或差异）备份之后执行并在失败之前完成的每个事务日志备份。

4  恢复尾日志备份

5  恢复数据库

联机丛书中的许多示例演示了从“备份集”恢复和恢复，换句话说，是存储所有备份的单个“设备”。实际上，这意味着当备份到磁盘时，备份设备是位于该磁盘上某个位置的单个.bak文件。

因此，例如，列表5.2中所示的简单示例使用由一个完整备份和一个事务日志备份组成的备份集，并演示如何执行完整恢复。为了运行此代码，首先需要重新创建数据库，然后插入一些示例数据行（为了方便起见，此级别的代码下载中包含执行此操作的脚本CreateAndPopulateTestDB.sql）。您还需要在数据库服务器的本地C盘上创建一个“Backups”目录，或者根据需要修改文件路径。测试数据库

列表5.2：备份到备份集并从中恢复；不推荐

然而，使用备份集似乎是数据库备份到磁带时的遗留问题。当备份到磁盘时，使用这个方案是一个坏主意，因为，当然，备份文件将迅速增长非常大。

在实践中，更常见的情况是，每个完整备份和事务日志备份文件都将分别命名，并可能标记备份的时间和日期。例如，大多数第三方备份工具、流行的社区生成脚本以及SSMS中的维护计划向导/设计器都将创建单独的带日期戳的文件，例如AdventureWorks_20080904_000001.bak。

因此，更常见的备份和恢复方案将使用唯一命名的备份，如列表5.3所示。

列表5.3：备份和恢复唯一命名的备份文件

时间点恢复到最后一次良好的日志备份

不幸的是，有时可能无法执行完全还原；例如，如果由于失败而导致实时事务日志不可用。在这种情况下，我们只需要恢复到最新日志备份的末尾。需要为这种情况做准备，即包含事务日志的驱动器发生故障，这决定了日志备份的频率。如果您每15分钟进行一次备份，那么您将面临15分钟数据丢失的风险。

假设我们已经执行了列表5.4所示的备份序列。为了这个演示，我们正在覆盖以前的备份文件，备份顺序明显比实际情况缩短了很多。

列表5.4：一系列简短的日志备份

如果灾难性故障发生在凌晨2:30之后不久，我们可能需要将数据库恢复到日志备份2结束时2:30的状态。

这样一个例子中的恢复顺序与我们在列表5.3中看到的非常相似，但是由于尾部备份是不可能的，我们只能恢复到某个点，所以我们需要使用STOPAT选项，如列表5.5所示。

列表5.5：使用STOPAT恢复到时间点

因为我们已经在将来指定了停止时间，所以此代码将把所有已完成的事务前滚到第二个事务日志的末尾。

或者，可以指定一个在特定日志文件中记录的事务的时间范围内的停止时间。在这种情况下，数据库将在指定的时间还原到上一个提交的事务。当您知道要恢复到什么时间，但不确切知道日志备份包含的时间时，这很有用。

还可以恢复到特定的标记事务。例如，当您需要将由某个应用程序访问的多个数据库恢复到逻辑上一致的点时，这很有用。本主题在这里没有进一步讨论，但是您可以在联机丛书（http://msdn.microsoft.com/en-us/library/ms187014.aspx）上找到更多信息，而Mladen Prajdic在这里提供了一个很好的例子：http://weblogs.sqlteam.com/mladenp/archive/2010/10/20/sql-server-transaction-marks-restoring-multiple-databases-to-a-common.aspx。

“事务破坏”后恢复

在任何数据库故障的上下文之外，可能需要还原数据库备份和事务日志，以便在错误的数据修改（例如删除或截断表）之前将数据库返回到特定的时间点。

你对这种情况的反应将取决于问题的性质。如果可能，您可以断开所有用户与数据库的连接（在通知他们之后），并评估刚刚发生的事情的影响。在某些情况下，您可能需要估计问题发生的时间，然后使用时间点还原对数据库和日志进行完全恢复。一旦恢复完成，您就必须通知用户一些事务可能已经丢失，并请求原谅。

当然，您通常无法以这种方式中断正常的业务操作，以修复意外的数据丢失。由于实时数据库仍在运行和访问中，您可以尝试在待机模式下还原数据库的备份。这允许进一步还原日志备份，但与使用norecover时不同的是，数据库仍然是可读的。还原方案可能如下所示：

1  在备用模式下，在实时数据库旁边还原数据库的备份

2  将日志前滚到坏事务发生之前的点，数据丢失。

3  将丢失的数据复制到实时数据库，并删除还原的副本

当然，这个过程不一定简单，而且可能非常耗时。除非您购买了专门的日志读取工具，并且可以直接查询日志备份，否则向前滚动日志可能意味着一系列艰苦的步骤，包括恢复日志、检查数据、进一步恢复等等，直到您确定错误事务发生的确切位置。步骤3也可能很困难，因为您将向实时系统引入不一定与数据库当前状态一致的数据，因此可能存在引用完整性问题。

让我们来看一个实现上述步骤1和2的示例。首先，让我们从头开始，运行CreateAndPopulateTestDB.sql脚本重新创建数据库，并将10行测试数据插入到新的LogTest表中。在列表5.6中，我们只需执行完整的数据库备份（覆盖任何以前的备份文件）。如果尚未创建“备份”目录，则需要创建该目录，或者根据需要调整路径。测试数据库

列表5.6:TestDB的完整备份

然后我们在LogTest表中插入一行新数据。

列表5.7：在TestDB中插入第11行

现在我们有了一个在LogTest表中包含11行的数据库TestDB，以及一个包含10行的备份版本。现在让我们在日志备份中捕获额外的修改，如列表5.8所示。测试数据库

列表5.8:TestDB的日志备份

现在，我们要模拟一个错误的“坏事务”，只需删除LogTest表，然后进行最后的日志备份。

列表5.9：灾难！

为了在不中断正常业务操作的情况下尝试检索丢失的数据，我们将在待机模式下还原数据库的副本。备用数据库的数据和日志文件（称ANewTestDB）被移动到一个“备用”目录（您需要预先创建这个目录）。测试数据库

列表5.10：在待机模式下还原TestDB的副本

我们现在有了一个名为ANewTestDB的新数据库，它处于“待机/只读”模式，如图5.1所示。

图5.1：备用数据库

对ANewTestDB数据库中的LogTest表的查询将显示10行。但是，我们希望将表恢复到它被错误地丢弃之前的状态。因此，下一步是将日志备份还原到备用数据库。

列表5.11：在待机模式下将日志备份还原到ANewTestDB数据库

此时，对ANewTestDB的查询将显示11行，现在我们可以准备将数据复制回实时数据库。如果我们更进一步并恢复第二个日志备份，我们就会意识到我们做得太过分了，而且备用数据库中的表也会丢失。

与执行备用恢复相比，另一种方法是考虑使用第三方工具，如Red Gate的SQL虚拟恢复，它提供了一种将备份作为活动的、完全功能的数据库进行装载的方法，而无需进行物理恢复。

无论DBA喜欢与否，开发人员通常都可以访问生产数据库来执行特殊的数据加载和更改。DBA和开发人员的共同责任是确保这些过程顺利进行，因此不会导致需要刚才描述的那种操作的问题。我们稍后将在第6级中讨论这个主题——处理批量操作。

当然，所需修复操作的确切性质取决于坏事务的性质。如果一个表被“意外地丢弃”，那么很可能您将使用备用路由进行恢复。在其他时候，您可能不需要简单地创建一个脚本来“逆转”恶意修改。

如果损坏只影响单个列或有限的行数，那么作为替代方法，可以使用诸如SQL数据比较之类的工具，该工具可以直接与备份文件进行比较，并且可以执行行级还原。

或者，如果运行SQL Server 2005（或更高版本）Enterprise Edition，并且提供了最新的数据库快照，则可以对快照运行查询，以便在获取数据库快照时检索数据，然后编写更新或插入命令，将数据从数据库快照拉入实时源数据库。

最后，作为最后的手段，一个专门的日志阅读器工具可以帮助您逆转事务的影响，尽管我不知道在SQL Server 2005和更高版本中有任何可靠的工作。

总结

在这个级别中，我们介绍了备份和恢复以完全恢复模式运行的数据库的日志文件的基本知识，这将是许多生产数据库的标准。

对于大多数DBA来说，执行时间点恢复的需要是一个罕见的事件，但如果有必要的话，这是其中一个任务，它的完成和完成是绝对关键的；DBA的声誉取决于它。

在损坏、驱动器故障等情况下，如果幸运的话，时间点恢复可能涉及备份事务日志的尾部并恢复到故障点的权限。如果事务日志不可用，或者为了在发生“坏事务”之前恢复到某个时间点而进行恢复，那么情况会变得更加棘手，但希望本步骤中介绍的某些技术会有所帮助。

资源：

交易记录进阶级别代码.zip

本文是SQL Server阶梯中事务日志管理阶梯的一部分。

原文链接：Stairway to Transaction Log Management in SQL Server, Level 5: Managing the Log in Full Recovery Mode