数据库中事务日志管理的阶梯，级别5：以完全恢复模式管理日志

原文链接：http://www.sqlservercentral.com/articles/Stairway+Series/73785/

作者：, 2012/01/27

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

当事情进展顺利时，没有必要特别注意事务日志的作用或它是如何工作的。您只需要确认每个数据库都有正确的备份机制。当事情出错时，对事务日志的理解对于采取纠正措施是很重要的，特别是当需要对数据库进行实时恢复时。托尼·戴维斯给出了每个DBA都应该知道的正确的细节水平。

在这个级别上，我们将回顾为什么以及如何完整备份，如何使用这些日志备份文件与完整数据库备份一起执行数据库还原。完整备份支持数据库还原到可用日志备份中的任何时间点，并且可以进行尾日志备份，直到上次提交的事务发生之前。

什么会被记录？

在完整备份中，所有操作都会被完全记录下来。包括INSERT, UPDATE和DELETE操作，这意味着对于被修改的每一行，都会有一个日志记录，描述执行语句的事务的ID、事务开始和结束时间、哪些页面、数据更改等等。

可以最少记录的操作有SELECT INTO, BULK INSERT和CREATE INDEX，在完全备份中，但略有不同。受这些操作影响的行不会单独记录；相反，只有数据库页在填充时才会被记录。这减少了此类操作的日志记录，同时确保仍然存在执行回滚、重做和点时间恢复所需的所有相同信息。Kalen Delaney发表了一些关于伐木的调查SELECT INTO()和索引重建()业务，均为完整备份和完整日志备份。工作时，差异日志记录操作时日志记录的不同之处。日志备份，将在第6级 – 管理登录 大容量日志 恢复

为什么备份事务日志？

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

因此，完整备份时，它会进行定期的事务日志备份，除了完全备份之外，还可以选择差异备份。许多新手或兼职DBA在其数据库上执行完全备份，但不执行事务日志备份。因此，事务日志不会被截断，它会不断增长，直到它所在的驱动器耗尽磁盘空间，从而导致数据库停止工作。

日志的截断将在日志备份后立即发生，前提是自上一次备份以来就发生了检查点，并且没有其他因素延迟截断，例如数据备份或还原操作。有关可能延迟截断可恢复VLFS的因素的完整列表，以及保持日志中大量活动(否则不需要)的因素，如流氓、长时间运行的未提交事务或数据库镜像或复制进程，请参见：.

仅复制事务日志的备份

仅复制事务日志的备份不会截断事务日志。一个仅复制日志备份与常规日志备份方案“独立”存在；它不会破坏日志备份链。简而言之，事务日志备份具有双重功能，即允许恢复和恢复到以前的时间点，以及控制事务日志的大小。可能与事务日志相关的问题最常见的原因是完整备份，只是不进行日志备份，或者没有频繁地进行日志备份以控制事务日志文件的大小。

如果不确定给定数据库是否正在进行事务日志备份，则只需查询回溯表中的MSDB数据库，使用类似于清单5.1所示的查询。

USE msdb ;SELECT   backup_set_id ,         backup_start_date ,         backup_finish_date ,         backup_size ,         recovery_model ,         [type]FROM     dbo.backupsetWHERE    database_name = 'TestDB'

清单5.1：是否进行日志备份？

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

注意，由于本文中的数据回溯表可以在不影响备份和还原行为的情况下进行操作，您可能希望通过查询验证此查询的结果。sys.database_recovery_status的价值last_log_backup_lsn(参见清单3.5)，或sys.databases表以查看log_reuse_wait_desc(将返回日志备份如果需要备份)。

如何备份事务日志

如前所述，如果不首先进行至少一次完全备份，就不可能执行事务日志备份。实际上，如果您的数据库位于完整备份，但是从来没有备份过，那么它实际上不会在完整备份执行第一次完全备份之前，数据库将处于自动截断模式。所有数据库备份，包括完整备份、日志备份或其他备份，都将使用备份命令。该命令接受许多选项，这些选项记录在这里：。但是，在其最基本的(通常是如何使用它)中，执行磁盘完整备份的命令如下所示：

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

如果这是要执行的第一个备份，则DatabaseName.bak文件将在指定目录中创建。如果此类文件已经存在，则默认行为是将后续备份附加到该文件。若要重写此行为，并规定应覆盖任何现有文件，我们可以使用初始化备选方案如下：

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

但是，最常见的情况是，随后的每个备份都有一个惟一的名称；在接下来的部分中将对此进行更详细的介绍，恢复到故障点.

在每次定期(例如每天)完全备份之后，将有频繁(例如每小时)的日志备份，其基本命令非常相似：

BACKUP LOG DatabaseNameTO DISK ='FileLocation\DatabaseName_Log.bak';

存储日志备份

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

日志备份频率

如前所述，您可能每15分钟进行一次日志备份，或者甚至更频繁地进行日志备份。在这种情况下，为了避免需要还原大量事务日志文件，您可以选择采用一种备份方案，该备份方案由包含差异备份的完整备份组成，这些备份与事务日志备份交织在一起。

在现实中，备份方案通常更多地是在理想和实际之间，在评估数据丢失的真实风险和公司将付出的代价之间，以及在减轻风险所涉及的成本之间达成妥协。许多非常重要的业务应用程序使用了一些简单但严格的备份方案，可能涉及定期的夜间完整备份和小时事务日志备份。

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

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

日志链及其破解

如前所述，如果不首先进行至少一次完全备份，就不可能执行事务日志备份。为了将数据库恢复到某个时间点，无论是某个特定日志备份的结束，还是某个特定日志备份中的某个时间点，必须存在一个完整的日志记录链，从完整(或差异备份)之后的第一次日志备份到故障点。这被称为日志链.打破日志链的方法有很多种，如果这样做，就意味着只能将数据库恢复到在中断链的事件发生之前进行日志备份的时间。总之，如果您不关心恢复数据的能力，打破链是个好主意。打破链条的两种最常见的方式包括：

• 事务日志备份文件的丢失或损坏-您只能恢复到上次良好的日志备份。日志链将在下一个良好的完整备份或差异备份时再次启动。
• 切换到简单恢复模式-如果你从完整到简单恢复模式，这将打破日志链，因为检查点将被激活，事务日志可以立即被截断。什么时候，如果你回到完整模式下，需要进行另一次完全备份才能重新启动日志链。实际上，在进行完全备份之前，数据库将保持自动截断模式，并且无法备份日志文件。

在数据库2008之前，有两个命令，即当BACKUP LOG WITH NO_LOG或BACKUP LOG WITH TRUNCATE_ONLY(它们在功能上是等价的)发出时，将强制日志文件截断，从而破坏日志链。您不应该在任何版本的数据库中发出这些命令，但我在这里提到它们，因为在试图处理“失控日志文件”时，它们仍然会被粗心大意者使用，而不了解它们对恢复数据库能力的影响。看见8级-帮助，我的日志有更多细节。

尾日志备份

只要您有最近的完整备份和完整的日志链，您就可以在任何失败之前将数据库恢复到它在最终日志备份结束时所处的状态。但是，假设您每小时进行事务日志备份，并且在下午1：45发生故障。您可能会损失45分钟的数据；实际上，如果故障非常严重，以至于活动事务日志是无法恢复的，那么这就是您将丢失的数据量。

但是，有时即使数据文件不可用，也仍然可以使用活动事务日志，特别是如果事务日志包含在单独的专用驱动器中。如果是这样的话，您应该备份活动事务日志，即对上次日志备份后生成的日志记录执行最终备份。这将捕获活动日志文件中的其余日志记录，直到失败为止。这被称为尾日志备份是在开始还原和恢复操作之前应该执行的最后一个操作。

尾日志备份和最小日志记录的操作

如果数据文件由于数据库故障而不可用，而且尾日志包含最小日志记录的操作，则无法执行尾日志备份，因为这将需要访问数据文件中更改的数据区段。这将在第6级，管理 大容量日志模式下的事务日志.

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

BACKUP LOG DatabaseNameTO DISK ='FileLocation\DatabaseName_Log.bak'WITH NORECOVERY

这个NORECOVERY选项将数据库置于还原状态，并假定您希望执行的下一个操作是RESTORE。如果数据库离线且无法启动，则仍应尝试备份刚才描述的日志尾(尽管NORECOVERY选项可以省略，因为没有事务正在进行)。

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

BACKUP LOG DatabaseNameTO DISK ='FileLocation\DatabaseName_Log.bak'WITH CONTINUE_AFTER_ERROR

如果主数据库和数据文件损坏，但日志可用，则建议。但是，这些主题超出了本阶梯的范围，有关详细信息，请参阅文档。看见.

执行恢复和恢复

在执行了尾日志备份之后，如果可能的话，下一步是恢复最后一个完整备份(如果适当的话，接着是差异备份)，然后恢复日志备份文件的完整序列，包括尾日志备份。此还原操作序列的基本语法如下：

RESTORE {DATABASE | LOG} DatabaseNameFROM DISK ='FileLocation\FileName.bak'WITH NORECOVERY;

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

RESTORE DATABASE DatabaseName WITH RECOVERY

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

数据库故障后恢复

下面的示例描述如何在发生故障时恢复数据库，从而无法再访问数据库数据文件。

完全恢复到故障点

假设“活动”事务日志可以在数据库故障(可能是由硬件故障引起的)之后到达，那么理论上应该可以通过使用以下步骤恢复和恢复数据库直到故障点：

1. 备份尾日志
2. 恢复最近的完整备份(如果适用，再加上差异备份)
3. 依次还原在完整(或差异)备份之后并在故障发生前完成的每个事务日志备份。
4. 恢复尾日志备份
5. 恢复数据库

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

因此，例如，清单5.2中所示的简单示例使用了由一个完整备份和一个事务日志备份组成的备份集，并演示了如何执行完全恢复。为了运行这段代码，首先需要重新创建TestDB数据库，然后插入几行数据示例(为方便起见，脚本要这样做，CreateAndPopulateTestDB.sql，包含在此级别的代码下载中)。您还需要在本地创建一个“备份”目录C:驱动数据库服务器，或酌情修改文件路径。

-- Perform a full backup of the Test database-- The WITH FORMAT option starts a new backup set-- Be careful, as it will overwrite any existing sets-- The full backup becomes the first file in the setBACKUP DATABASE TestDBTO DISK = 'C:\Backups\TestDB.bak'WITH FORMAT;GO -- Perform a transaction log backup of the Test database-- This is the second file in the setBACKUP Log TestDBTO DISK = 'C:\Backups\TestDB.bak'GO -- ....
      
       .... -- The RESTORE HEADERONLY command is optional.-- It simply confirms the files that comprise-- the current setRESTORE HEADERONLYFROM DISK = 'C:\Backups\TestDB.bak'GO -- Back up the tail of the log to prepare for restore-- This will become the third file of the bakup setBACKUP Log TestDBTO DISK = 'C:\Backups\TestDB.bak'WITH NORECOVERY;GO -- Restore the full backupRESTORE DATABASE TestDBFROM DISK = 'C:\Backups\TestDB.bak'WITH FILE=1, NORECOVERY; -- Apply the transaction log backupRESTORE LOG TestDBFROM DISK = 'C:\Backups\TestDB.bak'WITH FILE=2, NORECOVERY; -- Apply the tail log backupRESTORE LOG TestDBFROM DISK = 'C:\Backups\TestDB.bak'WITH FILE=3, NORECOVERY; -- Recover the databaseRESTORE DATABASE TestDBWITH RECOVERY;GO
      

清单5.2：备份到备份集并从中恢复；

不建议，但是，使用备份集似乎是数据库备份到磁盘时的遗物。当备份到磁盘时，使用此方案是个坏主意，因为当然，备份文件会很快变得非常大。

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

因此，更常见的备份和还原方案将使用唯一命名的备份，如清单5.3所示。

USE master;BACKUP DATABASE TestDBTO DISK ='C:\Backups\TestDB.bak'WITH INIT;GO -- Perform a transaction log backup of the Test databaseBACKUP Log TestDBTO DISK ='C:\Backups\TestDB_log.bak'WITH INIT;GO -- ....
      
       .... -- Back up the tail of the log to prepare for restoreBACKUP Log TestDBTO DISK ='C:\Backups\TestDB_taillog.bak'WITH NORECOVERY, INIT;GO -- Restore the full backupRESTORE DATABASE TestDBFROM DISK = 'C:\Backups\TestDB.bak'WITH NORECOVERY; -- Apply the transaction log backupRESTORE LOG TestDBFROM DISK = 'C:\Backups\TestDB_log.bak'WITH NORECOVERY; -- Apply the tail log backupRESTORE LOG TestDBFROM DISK = 'C:\Backups\TestDB_taillog.bak'WITH NORECOVERY; -- Recover the databaseRESTORE DATABASE TestDBWITH RECOVERY;GO
      

清单5.3：备份和恢复唯一名称的备份文件

时间点恢复到上次良好日志备份

有时，遗憾的是，可能无法执行完全恢复；例如，如果由于故障而导致实时事务日志不可用。在这种情况下，我们需要恢复到最近的日志备份结束。需要为这种情况做好准备，即包含事务日志的驱动器出现故障，该事务日志规定了日志备份的频率。如果您每15分钟进行一次备份，则会面临15分钟数据丢失的风险。

假设我们执行了清单5.4所示的备份序列。为了这个演示，我们正在覆盖以前的备份文件，而且备份序列显然比现实中的短得多。

-- FULL BACKUP at 2AMUSE master ;BACKUP DATABASE TestDBTO DISK = 'C:\Backups\TestDB.bak'WITH INIT ;GO -- LOG BACKUP 1 at 2.15 AMUSE master ;BACKUP LOG TestDBTO DISK = 'C:\Backups\TestDB_log.bak'WITH INIT ;GO -- LOG BACKUP 2 at 2.30 AMUSE master ;BACKUP LOG TestDBTO DISK = 'C:\Backups\TestDB_log2.bak'WITH INIT ;GO

 

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

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

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

--RESTORE Full backupRESTORE DATABASE TestDBFROM DISK = 'C:\Backups\TestDB.bak'WITH NORECOVERY; --RESTORE Log file 1RESTORE LOG TestDBFROM DISK = 'C:\Backups\TestDB_log.bak'WITH NORECOVERY, STOPAT = 'Jan 01, 2020 12:00 AM'; --RESTORE Log file 2RESTORE LOG TestDBFROM DISK = 'C:\Backups\TestDB_Log2.bak'WITH NORECOVERY, STOPAT = 'Jan 01, 2020 12:00 AM'; --Recover the databaseRESTORE DATABASE TestDBWITH RECOVERY;GO

清单5.5：使用STOPAT恢复到某个时间点

由于我们将来指定了STOPAT时间，因此该代码将向前滚动所有已完成的事务，直到第二个事务日志结束。

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

还可以恢复到特定的标记事务。例如，当您需要将由某个应用程序访问的多个数据库还原到逻辑一致的点时，这是非常有用的。本主题不在此进一步讨论，但您可以在联机丛书上找到更多信息()，并在这里提供了一个很好的示例：http：/weblogs.sqlWG.com/mladenp/Archive/2010/10/20/sql-server-Transaction-mark-MultiDatabaseto-a-Common.aspx。

在“坏事务”之后恢复

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

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

当然，通常您将无法以这种方式中断正常的业务操作，以修复意外的数据丢失。由于活动数据库仍然处于启动和运行状态，并且正在被访问，因此可以尝试将数据库的备份还原到STANDBY模式。这允许恢复进一步的日志备份，但与使用NORECOVERY时不同，数据库仍然是可读的。恢复方案可能如下所示：

1. 在STANDBY模式下，与实时数据库一起恢复数据库的备份。
2. 将日志前滚到错误事务发生之前的点，数据丢失。
3. 将丢失的数据复制到实时数据库并删除已还原的副本。

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

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

-- full backup of the databaseBACKUP DATABASE TestDBTO DISK ='C:\Backups\TestDB.bak'WITH INIT;GO

清单5.6：TestDB的完整备份

然后，我们将新的一行数据插入到LogTest表

USE TestDBGOINSERT INTO [TestDB].[dbo].[LogTest]           ([SomeInt]           ,[SomeLetters2])     VALUES           (66666,           'ST')          SELECT * FROM dbo.LogTest

 

清单5.7：将第11行插入到TestDB

所以现在我们在LogTest表中有一个包含11行的实时TestDB数据库，以及一个包含10行的备份版本。 现在让我们在日志备份中捕获其他修改，如清单5.8所示。

USE masterGOBACKUP Log TestDBTO DISK ='C:\Backups\TestDB_log.bak'WITH INIT;GO

清单5.8：TestDB的日志备份

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

USE TestDBGODROP TABLE dbo.LogTest ; USE masterGOBACKUP Log TestDBTO DISK ='C:\Backups\TestDB_log2.bak'WITH INIT;GO

清单5.9：灾难！

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

-- restore a copy of the TestDB database, called-- ANewTestDB, in STANDBY modeUSE master ;GORESTORE DATABASE ANewTestDB   FROM DISK ='C:\Backups\TestDB.bak'   WITH STANDBY='C:\Backups\ANEWTestDB.bak',   MOVE 'TestDB_dat' TO 'C:\Standby\ANewTestDB.mdf',   MOVE 'TestDB_log' TO 'C:\Standby\ANewTestDB.ldf'GO

清单5.10：在STANDBY模式下还原TestDB的副本

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

 

图5.1：备用数据库

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

USE masterGORESTORE LOG ANewTestDBFROM DISK = 'C:\Backups\TestDB_log.bak'   WITH STANDBY='C:\Backups\ANewTestDB_log.bak'

清单5.11：在STANDBY模式下将日志备份还原到ANewTestDB数据库

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

执行备用还原的另一种方法是考虑使用第三方工具，例如Red Gate的SQL Virtual Restore，它提供了一种将备份挂载为实时，功能齐全的数据库而无需物理还原的方法。

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

当然，所需修复行动的确切性质取决于不良交易的性质。如果一个表被“不小心不见了”，那么您很可能会从RESTORE WITH STANDBY入手。在其他时候，您可以简单地创建一个脚本来“反转”恶意修改。

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

或者，如果您运行SQL Server 2005(或更高版本)EnterpriseEdition，并且可以使用最近的数据库快照，您就可以对快照运行一个查询，以便在获取数据库快照时检索数据，然后编写一个UPDATE或INSERT命令将数据从数据库快照中提取到实时的源数据库中。

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

摘要

在这个级别上，我们已经介绍了备份和还原数据库日志文件的基础知识。这将是许多生产数据库的标准。

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

在损坏，驱动器故障等情况下，如果幸运的话，时间点恢复可能涉及备份事务日志的尾部并恢复到故障点的权限。 如果事务日志不可用，或者您正在恢复以便在发生“错误事务”之前恢复到某个时间点，则情况变得更加棘手，但希望此步骤中涉及的一些技术将有所帮助。