SQL Server中事务日志管理的阶梯级别5：以完全恢复模式管理日志

SQL Server中事务日志管理的阶梯级别5：以完全恢复模式管理日志

由托尼·戴维斯，2012/01/27
该系列

本文是SQL Server中“Stairway系列：事务日志管理的阶梯”的一部分

当事情进展顺利时，不需要特别意识到事务日志的作用或工作原理。你只需要确信每个数据库都有正确的备份机制。当事情出错时，对事务日志的理解对于采取纠正措施是非常重要的，特别是在需要时间点恢复数据库的情况下，迫切需要！托尼·戴维斯（Tony Davis）给出了每个DBA应该知道的正确的细节级别。

在这个级别中，我们将回顾为什么以及如何在完全恢复模式下进行日志备份，以及如何使用这些日志备份文件与完整数据库备份一起执行数据库还原。完全恢复模式支持数据库恢复到可用的日志备份中的任何时间点，并假定可以进行尾日志备份，直到发生故障之前的最后一次提交事务的时间。
记录什么？

在完全恢复模式下，所有操作都完全记录。对于INSERT，UPDATE和DELETE操作，这意味着对于每一个被修改的行，都会有一个日志记录来描述执行语句的事务的ID，当事务开始和结束时，哪些页面被改变，数据改变那是做了，等等。

在FULL恢复模式下工作时，可以进行最低限度记录的操作SELECT INTO，BULK INSERT和CREATE INDEX，仍然会完全记录下来，但是执行方式稍有不同。受这些操作影响的行不会单独记录;而是只有数据库页面被记录下来，因为它们被填充。这样可以减少对这些操作的监听，同时确保仍然存在执行回滚，重做和时间点恢复所需的所有相同的信息。 Kalen Delaney已经发布了对SELECT INTO（http://sqlblog.com/blogs/kalen_delaney/archive/2011/03/15/what-gets-logged-for-select-into.aspx）和索引重建（ http://sqlblog.com/blogs/kalen_delaney/archive/2011/03/08/what-gets-logged-for-index-rebuilds.aspx）在FULL和BULK_LOGGED恢复模式下进行操作。在BULK_LOGGED模式下工作时，记录最小日志记录操作的差异将在第6级 - 管理BULK LOGGED恢复模式中的日志中进行更详细的讨论。
为什么要备份交易记录？

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

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

假定自上次备份以来已经发生了检查点，并且没有其他因素正在延迟截断（如数据备份或还原操作），只要进行日志备份，就会截断日志。有关可能延迟可恢复VLF截断的因素的完整列表，以及保留大量活动日志的因素，例如流氓，长期运行的未提交事务或数据库镜像或复制进程，请参阅：http://msdn.microsoft.com/en-gb/library/ms345414.aspx。
事务日志的COPY_ONLY备份

COPY_ONLY事务日志的备份不会截断事务日志。 COPY_ONLY日志备份与正常日志备份方案“独立”存在;它不会中断日志备份链。

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

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

使用msdb;
SELECT backup_set_id，
         backup_start_date，
         backup_finish_date，
         backup_size，
         recovery_model，
         [类型]
FROM dbo.backupset
WHERE database_name ='TestDB'

清单5.1：正在进行日志备份吗？

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

请注意，由于可以在不影响备份和恢复行为的情况下操作此备份集表中的数据，因此您可能需要通过查询sys.database_recovery_status来查看last_log_backup_lsn（请参见清单3.5）或sys .databases表中查看log_reuse_wait_desc的值（如果需要备份，将返回LOG_BACKUP）。
如何备份事务日志

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

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

BACKUP DATABASE DatabaseNameTO DISK ='FileLocation \ DatabaseName.bak';

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

BACKUP DATABASE DatabaseNameTO DISK ='FileLocation \ DatabaseName.bak'WITH INIT;

然而，通常情况下，每个后续备份都有一个唯一的名称;在即将到来的章节中，还原到失败点。

在每次定期（例如每日）完整备份之后，将频繁（例如每小时）日志备份，其基本命令非常类似：

BACKUP LOG DatabaseNameTO DISK ='FileLocation \ DatabaseName_Log.bak';
存储日志备份

显然，备份的数据和日志文件不应该存储在托管实时文件的同一个驱动器上。如果该驱动器遭受硬件故障，则所有副本都将随实时文件一起丢失，备份将徒劳无功。应将文件备份到单独的设备，或备份到本地镜像驱动器。
日志备份的频率

正如前面的级别所指出的那样，您可能每15分钟进行一次日志备份，或者更频繁一次。在这种情况下，为了避免需要恢复大量的事务日志文件，您可以选择采用包含散布差异备份的完整备份的备份方案，散布事务日志备份。

在现实中，备份方案通常更多的是在理想和实际之间，在数据丢失的真实风险的评估，公司将花费什么和降低风险所涉及的成本之间进行折中。许多非常重要的业务应用程序使用比较简单但严格的备份方案，可能定期进行夜间完整备份以及每小时事务日志备份。

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

计算进行日志备份的频率并不容易。大多数数据库管理员会对日志备份的频率做出最佳评估，然后观察文件的增长特性，然后根据需要调整备份方案，以防止它们过大。

日志链和如何打破它

如前所述，如果不先进行至少一次完整备份，则无法执行事务日志备份。为了将数据库恢复到某个时间点，无论是特定日志备份的结尾，还是特定日志备份中的某个时间点，都必须存在从所采取的第一个日志备份开始的完整的连续日志记录完成（或差异备份）后，直至故障点。这被称为日志链。

有很多方法可以打破日志链，如果这样做，意味着您只能将数据库恢复到事件发生之前进行日志备份的时间。总之，如果你关心恢复数据的能力，打破这个链条不是一个好主意。打破连锁店的两种最常见的方式包括：

    事务日志备份文件丢失或损坏 - 您将只能恢复到最近一次正确的日志备份。日志链将在下一个完整或差异备份时重新开始。
    切换到SIMPLE恢复模式 - 如果您从FULL切换到SIMPLE恢复模式，这将打破日志链，因为检查点将被激发，事务日志可以被立即截断。如果您返回完整模式，则需要进行另一次完整备份才能重新启动日志链。事实上，在进行完整备份之前，数据库将保持自动截断模式，并且不能备份日志文件。

在SQL Server 2008之前，有一些命令，即BACKUP LOG WITH NO_LOG或BACKUP LOG WITH TRUNCATE_ONLY（它们在功能上是等效的），当发布时，将强制日志文件截断并因此破坏日志链。您不应该在任何版本的SQL Server中发布这些命令，但是我在这里提到它们，因为在试图处理“失控日志文件”时，它们仍然被粗心大意所使用，而不理解其对于恢复其数据库。请参阅8级 - 帮助，我的日志已满，了解更多详情。
尾巴日志备份

只要您有最近的完整备份和完整的日志链，就可以将数据库恢复到最终日志备份结束时存在的状态，然后再执行任何故障。但是，假设您每小时进行一次事务日志备份，并在1:45 PM发生故障。您可能会损失45分钟的数据;事实上，如果失败是如此灾难性的，以至于实时交易日志是不可挽回的，那么这就是你将失去的数据量。

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

如果由于数据库故障导致数据文件不可用，并且日志的尾部包含最少记录的操作，则不可能执行尾部日志备份，因为这需要访问数据库中已更改的数据盘区数据文件。这将在第6层，以批量记录模式管理事务日志中有更详细的介绍。

如果您要恢复的数据库处于联机状态，则日志的尾部将按如下所示进行备份：

备份日志DatabaseNameTO DISK ='FileLocation \ DatabaseName_Log.bak'WITH NORECOVERY

NORECOVERY选项使数据库处于恢复状态，并假定您希望执行的下一个操作是RESTORE。如果数据库处于脱机状态并且不能启动，那么您应该仍然尝试像刚才所述那样备份日志的尾部（尽管NORECOVERY选项可以省略，因为没有事务正在进行）。

如果您确定日志文件已损坏，则文档建议您作为最后手段尝试执行尾部日志备份：

备份日志DatabaseNameTO DISK ='FileLocation \ DatabaseName_Log.bak'WITH CONTINUE_AFTER_ERROR

如果主数据库和数据文件已损坏，但日志可用，则Microsoft建议重新构建主数据库，然后备份最后一个活动日志。不过，这些主题不在本阶段的范围之内，我会把你的文档转交给你。请参阅http://msdn.microsoft.com/en-us/library/ms190952.aspx。
执行还原和恢复

如果可能，执行尾部日志备份（如果可能的话），下一步是恢复最后一次完整备份（如果适用，接着进行差异备份），然后恢复完整的日志备份文件序列，包括尾部日志备份。这一系列还原操作的基本语法如下：

使用RECOVERY恢复数据库DatabaseName

常见的要求是将数据库还原到其他位置，在这种情况下，您可以简单地将这些文件作为还原过程的一部分进行移动，如下所述：http://msdn.microsoft.com/zh-cn/library/ms190255的.aspx。
数据库失败后恢复

以下示例介绍了如何恢复数据库以响应故障，从而使数据库数据文件不再可访问。
完全恢复到故障点

假设数据库发生故障（可能是由硬件故障引起的）之后可以达到“实时”事务日志，那么从理论上讲，通过使用以下步骤，应该可以恢复和恢复数据库到故障点：

    备份日志的尾部
    恢复最近的完整备份（如果适用，还要加上差分）
    依次还原在完整（或差异）备份之后采取并在发生故障之前完成的每个事务日志备份
    恢复尾部日志备份
    恢复数据库

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

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

清单5.2：备份到备份集并从中恢复;不建议

但是，使用备份集似乎是将数据库备份到磁带时的遗留问题。当备份到磁盘时，使用这个方案是一个坏主意，因为当然备份文件将会很快增长很多。

在实践中，每个完整的备份和事务日志备份文件将被单独命名，并可能盖上备份的时间和日期。例如，大多数第三方备份工具，社区生成的流行脚本以及SSMS中的维护计划向导/设计器都将创建单独的日期标记文件，例如， AdventureWorks_FULL_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恢复到某个时间点

由于我们将来会指定一个STOPAT时间，所以这个代码将会把所有已完成的事务处理直到第二个事务日志的末尾。

或者，可以指定落在特定日志文件中记录的事务的时间范围内的STOPAT时间。在这种情况下，数据库将在指定的时间恢复到上次提交的事务。当您知道要恢复到什么时间时，这非常有用，但不清楚该日志备份包含的时间。

还可以恢复到特定的标记事务。例如，当您需要将由特定应用程序访问的多个数据库恢复到逻辑上一致的点时，这非常有用。这个主题不在这里进一步讨论，但是你可以在Books Online上找到更多（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。
在“不良交易”后恢复

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

你对这种情况的回应将取决于问题的性质。如果可能的话，你可以把所有用户从数据库中断开（在通知他们之后），并评估刚刚发生的事情。在某些情况下，您可能需要估计发生问题的时间，然后使用时间点恢复完成数据库和日志的完全恢复。恢复完成后，您必须通知用户有些交易可能已经丢失，并要求原谅。

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

    在STANDBY模式下还原数据库的备份以及实时数据库
    将日志转发到发生错误事务之前的点，并且数据丢失。
    将丢失的数据复制到实时数据库并删除已恢复的副本

当然，这个过程不一定简单明了，而且可能相当耗时。除非您购买了专门的日志读取工具，并且可以直接询问日志备份，否则向前滚动日志可能意味着一系列艰巨的步骤，包括恢复日志，检查数据，进一步恢复等等，直到您已经确定了不良交易发生的地点。步骤3也很困难，因为您将要将数据引入到与数据库当前状态不一致的实时系统中，因此可能存在参照完整性问题。

我们来看一个实现上面第一步和第二步的例子。首先，让我们从头开始重新运行CreateAndPopulateTestDB.sql脚本来重新创建TestDB数据库，并将10行测试数据插入到一个新的LogTest表中。在清单5.6中，我们只是做一个完整的数据库备份（覆盖任何以前的备份文件）。您需要创建“备份”目录，如果您还没有这样做，或者根据需要调整路径。

- 数据库的完整备份
BACKUP DATABASE TestDB
TO DISK ='C：\ Backups \ TestDB.bak'
用INIT;
走

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

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

代码5.6：TestDB的完全备份

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

使用TestDB
走
INSERT INTO [TestDB]。[dbo]。[LogTest]
           （[SomeInt]
           [SomeLetters2]）
     VALUES
           （66666，
           'ST'）
           
SELECT * FROM dbo.LogTest

清单5.7：在TestDB中插入第11行

所以现在我们在LogTest表中有11行的TestDB数据库，还有10行的备份版本。现在我们来捕获日志备份中的额外修改，如清单5.8所示。

使用大师
走
备份日志TestDB
TO DISK ='C：\ Backups \ TestDB_log.bak'
用INIT;
走

代码5.8：TestDB的日志备份

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

使用TestDB
走
DROP TABLE dbo.LogTest;

使用大师
走
备份日志TestDB
TO DISK ='C：\ Backups \ TestDB_log2.bak'
用INIT;
走

清单5.9：灾难！

为了在不中断正常业务操作的情况下尝试检索丢失的数据，我们将以STANDBY模式恢复TestDB数据库的副本。备用数据库的数据和日志文件（称为ANewTestDB）被移动到“备用”目录（您需要事先创建该目录）。

- 恢复调用TestDB数据库的副本
- ANewTestDB，处于STANDBY模式
使用大师;
走
RESTORE DATABASE ANewTestDB
   FROM DISK ='C：\ Backups \ TestDB.bak'
   WITH STANDBY ='C：\ Backups \ ANEWTestDB.bak'，
   将'TestDB_dat'移动到'C：\ Standby \ ANewTestDB.mdf'，
   MOVE'TestDB_log'TO'C：\ Standby \ ANewTestDB.ldf'
走

清单5.10：在STANDBY模式下恢复TestDB的副本

现在我们有一个名为ANewTestDB的新数据库，它处于“Standby / Read-Only”模式，如图5.1所示。

图5.1：备用数据库

对ANewTestDB数据库中LogTest表的查询将显示10行。但是，我们希望将表格恢复到错误放置之前的状态。因此，下一步是执行恢复日志备份到备用数据库。

使用大师
走
恢复日志ANewTestDB
FROM DISK ='C：\ Backups \ TestDB_log.bak'
   WITH STANDBY ='C：\ Backups \ ANewTestDB_log.bak'

 

列表5.11：以STANDBY模式将日志备份恢复到ANewTestDB数据库

此时，针对ANewTestDB的查询显示了11行，现在我们可以准备将​​这些数据复制到实时数据库中。如果我们更进一步，恢复第二个日志备份，我们会意识到我们已经走得太远了，备份数据库中的表也会丢失。

备用还原的替代方案是考虑使用第三方工具，如Red Gate的SQL虚拟还原，它提供了一种将备份安装为实时，功能齐全的数据库的方法，而无需进行物理还原。

不管数据库管理员喜欢与否，开发人员通常都可以访问生产数据库来执行临时数据加载和更改。 DBA和开发人员的共同责任是确保这些工作顺利进行，因此不会引起需要上述行动的问题。稍后我们将在6级 - 处理批量操作中回到这个话题。

当然，所要求的赔偿行为的确切性质取决于不良交易的性质。如果一个表被“意外丢弃”，那么很有可能你将会沿着RESTORE WITH STANDBY路线前行。在其他时候，您可能会简单地创建一个脚本来“排除”流氓修改。

如果损坏只影响到单列或有限的行数，那么也可以使用诸如SQL Data Compare之类的工具，它可以直接与备份文件进行比较，并且可以进行行级恢复。

或者，如果运行SQL Server 2005（或更高版本）Enterprise Edition，并且可以使用最近的数据库快照，则可以运行查询快照以检索数据，因为它查看数据库快照的拍摄时间，然后编写UPDATE或INSERT命令将数据库快照中的数据导入到实时源数据库中。

最后，作为最后的手段，专门的日志读取器工具可能会帮助您将事务的影响撤销，尽管我并不清楚SQL Server 2005及更高版本中的任何可靠工作。
概要

在这个级别，我们已经介绍了备份和恢复在全恢复模式下运行的数据库的日志文件的基础知识，这将是许多生产数据库的常态。

对于大多数数据库管理员来说，执行时间点还原的需求是一件罕见的事情，但是这是必要的任务之一，它的完成和完成是绝对关键的。 DBA的声誉取决于它。

在发生损坏，驱动器故障等情况下，如果您幸运的话，时间点恢复可能涉及备份事务日志的尾部并恢复到失败点的权利。如果事务日志不可用，或者如果要恢复到某个时间点之前发生“不良事务”，则情况会变得更加棘手，但希望此步骤中涵盖的某些技术将有所帮助。
 
资源：
TransactionLogStairway_Level5_Code.zip

本文是SQL Server Stairway中“事务日志管理”阶层的一部分