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

By Tony Davis, 2012/01/27

转载自：http://www.sqlservercentral.com/articles/Stairway+Series/73785/

该系列

本文是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数据库中的备份集表。

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

Listing 5.1: Are log backups being taken?

In the type column, a D represents a database backup, L a log backup and I a differential backup.

Note that since the data in this backupset table could be manipulated without affecting backup and restore behavior, you might want to verify your findings from this query, by querying sys.database_recovery_status to see the value of last_log_backup_lsn (see Listing 3.5), or the sys.databases table to see the value of log_reuse_wait_desc (will return LOG_BACKUP if a backup is required).

如何备份事务日志

正如前面所讨论的，如果不先进行至少一次完整备份，则不可能执行事务日志备份。实际上，如果您有一个处于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恢复模式，这将打破日志链，因为检查点将被激发，事务日志可以成为

-- 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 set
BACKUP DATABASE TestDB
TO DISK = 'C:\Backups\TestDB.bak'
WITH FORMAT;
GO

-- Perform a transaction log backup of the Test database
-- This is the second file in the set
BACKUP Log TestDB
TO DISK = 'C:\Backups\TestDB.bak'
GO

-- ....<FAILURE OCCURS HERE>....

-- The RESTORE HEADERONLY command is optional.
-- It simply confirms the files that comprise 
-- the current set
RESTORE HEADERONLY
FROM 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 set
BACKUP Log TestDB
TO DISK = 'C:\Backups\TestDB.bak'
WITH NORECOVERY;
GO

-- Restore the full backup
RESTORE DATABASE TestDB
FROM DISK = 'C:\Backups\TestDB.bak'
WITH FILE=1, NORECOVERY;

-- Apply the transaction log backup
RESTORE LOG TestDB
FROM DISK = 'C:\Backups\TestDB.bak'
WITH FILE=2, NORECOVERY;

-- Apply the tail log backup
RESTORE LOG TestDB
FROM DISK = 'C:\Backups\TestDB.bak'
WITH FILE=3, NORECOVERY;

-- Recover the database
RESTORE DATABASE TestDB
WITH RECOVERY;
GO


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

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

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

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

USE master;
BACKUP DATABASE TestDB
TO DISK ='C:\Backups\TestDB.bak'
WITH INIT;
GO

-- Perform a transaction log backup of the Test database
BACKUP Log TestDB
TO DISK ='C:\Backups\TestDB_log.bak'
WITH INIT;
GO

-- ....<FAILURE OCCURS HERE>....

-- Back up the tail of the log to prepare for restore
BACKUP Log TestDB
TO DISK ='C:\Backups\TestDB_taillog.bak'
WITH NORECOVERY, INIT;
GO

-- Restore the full backup
RESTORE DATABASE TestDB
FROM DISK = 'C:\Backups\TestDB.bak'
WITH NORECOVERY;

-- Apply the transaction log backup
RESTORE LOG TestDB
FROM DISK = 'C:\Backups\TestDB_log.bak'
WITH NORECOVERY;

-- Apply the tail log backup
RESTORE LOG TestDB
FROM DISK = 'C:\Backups\TestDB_taillog.bak'
WITH NORECOVERY;

-- Recover the database
RESTORE DATABASE TestDB
WITH RECOVERY;
GO

清单5.3：备份和还原唯一命名的备份文件

时间点恢复到上次良好的日志备份
有时，不幸的是，可能无法进行完全恢复。 例如，如果实时事务日志由于失败而不可用。 在这种情况下，我们需要恢复到最近的日志备份结束。 需要为这种可能性做准备，即包含事务日志的驱动器的故障，该事务日志决定多久进行一次日志备份。 如果每隔15分钟进行一次备份，则会面临数据丢失15分钟的风险。

想象一下，我们已经执行了清单5.4中所示的备份序列。 为了这个演示，我们覆盖了以前的备份文件，而且备份顺序显然比实际情况要短得多。

-- FULL BACKUP at 2AM
USE master ;
BACKUP DATABASE TestDB
TO DISK = 'C:\Backups\TestDB.bak'
WITH INIT ;
GO

-- LOG BACKUP 1 at 2.15 AM
USE master ;
BACKUP LOG TestDB
TO DISK = 'C:\Backups\TestDB_log.bak'
WITH INIT ;
GO

-- LOG BACKUP 2 at 2.30 AM
USE master ;
BACKUP LOG TestDB
TO DISK = 'C:\Backups\TestDB_log2.bak'
WITH INIT ;
GO

清单5.4：一系列短日志备份
如果在凌晨2:30之后发生灾难性故障，我们可能需要将数据库恢复到日志备份2结束时的凌晨2:30。
这个例子中的恢复序列与我们前面在5.3中看到的恢复序列非常相似，但是由于尾部备份是不可能的，我们只能恢复到某一点，所以我们需要使用STOPAT选项 ，如代码清单5.5所示。

--RESTORE Full backup
RESTORE DATABASE TestDB
FROM DISK = 'C:\Backups\TestDB.bak'
WITH NORECOVERY;

--RESTORE Log file 1
RESTORE LOG TestDB
FROM DISK = 'C:\Backups\TestDB_log.bak'
WITH NORECOVERY, STOPAT = 'Jan 01, 2020 12:00 AM';

--RESTORE Log file 2
RESTORE LOG TestDB
FROM DISK = 'C:\Backups\TestDB_Log2.bak'
WITH NORECOVERY, STOPAT = 'Jan 01, 2020 12:00 AM';

--Recover the database
RESTORE DATABASE TestDB
WITH RECOVERY;
GO

清单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中，我们只是做一个完整的数据库备份（覆盖任何以前的备份文件）。您需要创建“备份”目录，如果您还没有这样做，或者根据需要调整路径。

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

代码5.6：TestDB的完全备份

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

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

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

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

USE master
GO
BACKUP Log TestDB
TO DISK ='C:\Backups\TestDB_log.bak'
WITH INIT;
GO

代码5.8：TestDB的日志备份

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

USE TestDB
GO
DROP TABLE dbo.LogTest ;

USE master
GO
BACKUP Log TestDB
TO 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 mode
USE master ;
GO
RESTORE 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的新数据库，它处于“Standby / Read-Only”模式，如图5.1所示。

图5.1：备用数据库

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

USE master
GO
RESTORE LOG 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及更高版本中的任何可靠工作。

Summary

In this Level, we've covered the basics of backing up and restoring log files for databases operating in FULL recovery mode, which will be the norm for many production databases.

For most DBAs, the need to perform a point-in-time restore is a rare event, but it's one of those tasks where, if it is necessary, it is absolutely critical that it is done and done well; the DBA's reputation depends on it.

In the case of corruption, drive failure, and so on, point-in-time recovery might involve, if you're lucky, backing up the tail of the transaction log and restoring right to point of failure. If the transaction log is not available, or if you're restoring in order to revert to some point in time before a "bad transaction" occurred, then the situation becomes trickier, but hopefully some of the techniques covered in this step will help.

资源：
TransactionLogStairway_Level5_Code.zip
本文是SQL Server Stairway中“事务日志管理”阶段的一部分

侵权必删！