第十四周翻译

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

什么被日志记录？

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

可以最小化记录的操作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恢复模式中的日志中详细讨论。

为什么要备份事务日志？

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

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

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

COPY_ONLY备份事务日志

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

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

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

清单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到disk=‘filelocation\databasename.bak’；
如果这是要执行的第一次备份，则将在指定目录中创建databasename.bak文件。如果这样的文件已经存在，那么默认行为是将后续备份附加到该文件。要覆盖此行为并规定应覆盖任何现有文件，可以使用init选项，如下所示：
备份数据库databasename到disk='filelocation\databasename.bak’with init；
但是，最常见的情况是，每个后续备份都有一个唯一的名称；在接下来的部分中，将详细介绍恢复到故障点。
在每次常规（如每日）完全备份之后，都会有频繁（如每小时）的日志备份，其基本命令非常类似：
backup log databasename to disk=‘filelocation\databasename_log.bak’；
存储日志备份
显然，备份的数据和日志文件不应存储在承载实时文件的同一驱动器上。如果该驱动器出现硬件故障，那么你的所有副本以及活动文件都将丢失，备份将是徒劳的。文件应备份到单独的设备，或备份到本地镜像驱动器。
日志备份频率
如前几级所述，你可能每15分钟进行一次日志备份，甚至可能更频繁。在这种情况下，为了避免需要恢复大量的事务日志文件，你可以选择采用一种备份方案，该方案包括完整备份和差异备份，以及事务日志备份。

在现实中，备份方案往往是在理想和实际之间、对数据丢失的真实风险的评估与公司将付出的代价以及降低风险所涉及的成本之间进行折衷。许多非常重要的业务应用程序使用的备份方案都比较简单，但也比较严格，可能包括定期的夜间完整备份和每小时事务日志备份。
日志备份的频率也可能由数据库所属的事务数决定。对于非常繁忙的数据库，可能需要经常备份以控制日志的大小。
没有简单的方法来计算日志备份的频率。大多数DBA将对日志备份的频率进行最佳估计，然后观察文件的增长特性，然后根据需要调整备份方案，以防止文件过大。
木链和如何打破它
如前所述，不首先进行至少一次完整备份就无法执行事务日志备份。为了将数据库恢复到某个时间点，或者恢复到特定日志备份的末尾，或者恢复到特定日志备份中的某个时间点，必须存在完整的完整的日志记录链，从完整（或差异备份）之后的第一个日志备份到故障点。这就是所谓的日志链。
有很多方法可以破坏日志链，如果这样做，则意味着你只能将数据库恢复到破坏日志链的事件发生之前的日志备份时间。简而言之，如果你关心恢复数据的能力，那么断开链并不是一个好主意。断链最常见的两种方法包括：
事务日志备份文件丢失或损坏–你只能恢复到上一次良好日志备份。日志链将在下一次良好的完整备份或差异备份时重新启动。
切换到简单恢复模式–如果你从完全恢复模式切换到简单恢复模式，这将破坏日志链，因为将触发检查点，并且事务日志可以立即截断。当且如果你返回到完整模式，则需要进行另一个完整备份以重新启动日志链。事实上，在进行完整备份之前，数据库将保持自动截断模式，你将无法备份日志文件。
在SQL Server 2008之前，有两个命令，即backup log with no_log或backup log with truncate_only（它们在功能上是等效的），在发出命令时会强制截断日志文件，从而中断日志链。在任何版本的SQL Server中都不应该发出这些命令，但我在这里提到这些命令，因为在试图处理“失控的日志文件”时，粗心的人仍然会使用它们，而不理解它对他们恢复数据库的能力的影响。请参阅第8级-帮助，我的日志已满，了解更多详细信息。
尾日志备份
只要你有一个最近的完整备份和一个完整的日志链，你就可以将数据库恢复到在任何失败之前的最终日志备份结束时的状态。但是，假设你每小时进行一次事务日志备份，并且在下午1:45发生故障。你可能会丢失价值45分钟的数据；实际上，如果失败是如此灾难性，以至于实时事务日志无法恢复，那么这就是你将丢失的数据量。
但是，有时即使数据文件不在，实时事务日志仍然可用，特别是当事务日志包含在单独的专用驱动器上时。如果是这种情况，则应备份实时事务日志，即对自上次日志备份以来生成的日志记录执行最终备份。这将捕获活动日志文件中的剩余日志记录，直到出现故障为止。这称为尾日志备份，是在开始恢复和恢复操作之前应执行的最后一个操作。
尾日志备份和最小日志操作
如果由于数据库故障而导致数据文件不可用，并且日志的尾部包含最少的日志操作，那么将无法执行尾部日志备份，因为这将需要访问数据文件中更改的数据扩展数据块。这将在第6级中更详细地介绍，以大容量日志模式管理事务日志。
如果要还原的数据库处于联机状态，则日志的尾部将按以下方式进行备份：

备份日志databasename到disk='filelocation\databasename_log.bak’with norecovery
norecovery选项将数据库置于还原状态，并假定要执行的下一个操作是还原。如果数据库处于脱机状态且无法启动，则仍应尝试按照刚才描述的方式备份日志的尾部（尽管可以省略norecovery选项，因为不会进行任何事务）。
如果你确定日志文件已损坏，文档建议作为最后一种方法，你尝试使用以下方法进行尾日志备份：
backup log databasename to disk='filelocation\databasename_log.bak’with continue_after_错误
如果主数据库和数据文件已损坏，但日志可用，Microsoft建议重建主数据库，然后备份上一个活动日志。但是，这些主题不在这个楼梯的范围内，我将参考文档了解更多详细信息。请参阅http://msdn.microsoft.com/en-us/library/ms190952.aspx。
执行恢复和恢复
在执行了尾日志备份之后，如果可能的话，下一步是还原最后一次完整备份（如果适当，然后是差异备份），然后还原日志备份文件的完整序列，包括尾日志备份。此还原操作序列的基本语法如下：
使用norecovery从disk='filelocation\filename.bak’还原数据库日志数据库名称；
如果还原时省略WITH NORECOVERY选项，则默认情况下，还原命令将继续进行恢复。换句话说，SQL Server将尝试协调数据和日志文件，前滚已完成的事务，然后根据需要回滚未完成的事务。通过使用norecovery指定，我们将指示SQL Server在执行任何回滚之前，我们正在输入一个还原序列，并且必须前滚更多操作。在还原顺序中还原最后一个备份后，可以按以下方式恢复数据库：
恢复数据库databasename
一个常见的要求是将数据库还原到不同的位置，在这种情况下，你可以简单地将文件作为还原过程的一部分移动，如下所述：http://msdn.microsoft.com/en-us/library/ms190255.aspx。
数据库故障后恢复
下面的示例描述了如何恢复数据库以响应故障，从而使数据库数据文件不再可访问。
完全恢复到故障点
假设“实时”事务日志可以在数据库故障（可能由硬件故障引起）后访问，那么理论上，应该可以使用以下步骤恢复和恢复数据库，直至故障点：
1.备份日志的尾部
2.恢复最近的完整备份（如果适用，还包括差异备份）
3.依次还原在完全（或差异）备份之后执行并在失败之前完成的每个事务日志备份。
4.恢复尾日志备份
5.恢复数据库
联机丛书中的许多示例演示了从“备份集”恢复和恢复，换句话说，是存储所有备份的单个“设备”。实际上，这意味着当备份到磁盘时，备份设备是位于该磁盘上某个位置的单个.bak文件。
因此，例如，清单5.2中所示的简单示例使用由一个完整备份和一个事务日志备份组成的备份集，并演示如何执行完整还原。为了运行此代码，首先需要重新创建testdb数据库，然后插入一些示例数据行（为了方便起见，此级别的代码下载中包含执行此操作的脚本createandpooltestdb.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 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
– ……
– 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_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
– ……
– 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
listing 5.3：支持IP和恢复两种，从uniquely命名备份文件…
在两个时间点恢复日志备份好货
不幸的是，有时，它可能不会是可能的两个全perform a恢复；例如，如果活的事务日志unavailable冰作为一个结果的原因。在这个案例中，我们将需要恢复完全是两个最新的日志备份。它的需要，准备为这一eventuality即失效的操作系的事务日志，这dictates技术往往是由日志备份。如果你把备份每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恢复到时间点
因为我们已经在将来指定了停止时间，所以此代码将把所有已完成的事务前滚到第二个事务日志的末尾。
或者，可以指定一个在特定日志文件中记录的事务的时间范围内的停止时间。在这种情况下，数据库将在指定的时间还原到上一个提交的事务。当你知道要恢复到什么时间，但不确切知道日志备份包含的时间时，这很有用。
还可以恢复到特定的标记事务。例如，当你需要将由某个应用程序访问的多个数据库恢复到逻辑上一致的点时，这很有用。本主题在这里没有进一步讨论，但是你可以在联机丛书（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。
“不良交易”后恢复

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

你对这种情况的反应将取决于问题的性质。如果可能，你可以断开所有用户与数据库的连接（在通知他们之后），并评估刚刚发生的事情的影响。在某些情况下，你可能需要估计问题发生的时间，然后使用时间点还原对数据库和日志进行完全恢复。一旦恢复完成，你就必须通知用户一些事务可能已经丢失，并请求原谅。
当然，你通常无法以这种方式中断正常的业务操作，以修复意外的数据丢失。由于实时数据库仍在运行和访问中，你可以尝试在待机模式下还原数据库的备份。这允许进一步还原日志备份，但与使用norecovery时不同，数据库仍然是可读的。还原方案可能如下所示：
1.在备用模式下，在实时数据库旁边还原数据库的备份

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

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

当然，这个过程不一定简单，而且可能非常耗时。除非你购买了一个专门的日志读取工具，并且可以直接查询日志备份，否则向前滚动日志可能意味着一系列艰苦的步骤，包括恢复日志、检查数据、进一步恢复等等，直到你确定错误事务发生的确切位置。步骤3也可能很困难，因为你将向实时系统引入不一定与数据库当前状态一致的数据，因此可能存在引用完整性问题。
让我们来看一个实现上述步骤1和2的示例。首先，让我们从头开始，运行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：灾难！
为了在不中断正常业务操作的情况下尝试检索丢失的数据，我们将在待机模式下还原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：在待机模式下还原testdb的副本
我们现在有了一个名为anewtestdb的新数据库，它处于“待机/只读”模式，如图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：在待机模式下将日志备份还原到anewtestdb数据库
此时，对anewtestdb的查询会显示11行，现在我们可以准备将数据复制回活动数据库了。如果我们更进一步，恢复第二个日志备份，我们就会意识到我们做得太过分了，而且备用数据库中的表也会丢失。
与执行备用恢复相比，另一种方法是考虑使用第三方工具，如Red Gate的SQL虚拟恢复，它提供了一种将备份作为活动的、完全功能的数据库进行装载的方法，而无需物理恢复。
无论DBA喜欢与否，开发人员通常都可以访问生产数据库来执行特殊的数据加载和更改。DBA和开发人员的共同责任是确保这些过程顺利进行，因此不会导致需要刚才描述的那种操作的问题。我们稍后将在第6级中讨论这个主题——处理批量操作。
当然，所需修复操作的确切性质取决于坏事务的性质。如果一个表被“意外地丢弃”，那么很可能你将使用备用路由进行恢复。在其他时候，你可能只需要创建一个脚本来“逆转”恶意修改就可以了。
如果损坏只影响单个列或有限的行数，那么作为替代方法，可以使用诸如SQL数据比较之类的工具，该工具可以直接与备份文件进行比较，并且可以执行行级还原。
或者，如果运行SQL Server 2005（或更高版本）Enterprise Edition，并且提供了最新的数据库快照，则可以对快照运行查询，以便在获取数据库快照时检索数据，然后编写更新或插入命令，将数据从数据库快照拉入live，源数据库。
最后，作为最后的手段，一个专门的日志阅读器工具可以帮助你逆转事务的影响，尽管我不知道在SQL Server 2005和更高版本中有任何可靠的工作。
总结
在这个级别中，我们已经介绍了备份和恢复以完全恢复模式运行的数据库的日志文件的基本知识，这将是许多生产数据库的标准。
对于大多数DBA来说，执行时间点恢复的需要是一个罕见的事件，但如果有必要的话，这是其中一个任务，它的完成和完成是绝对关键的；DBA的声誉取决于它。
在损坏、驱动器故障等情况下，如果幸运的话，时间点恢复可能涉及备份事务日志的尾部并恢复到故障点的权限。如果事务日志不可用，或者为了在发生“坏事务”之前恢复到某个时间点而进行恢复，那么情况会变得更加棘手，但希望本步骤中介绍的某些技术会有所帮助。
本文是SQL Server中事务日志管理的父级阶梯的一部分。