原文链接：http://www.sqlservercentral.com/articles/Stairway+Series/73785/
作者：Tony Davis，2012/01/27
关于该系列
本文是阶梯系列的一部分：SQL Server中事务日志管理的阶梯
只要操作顺利进行时，不需要特别注意事务日志是做什么的或者它是如何工作的。你只需要相信每个数据库都有正确的备份机制。当出现问题时，了解事务日志工作并采取纠正措施就非常重要了，特别是需要在某些时间点对数据库进行恢复操作。Tony Davis给出了每个DBA应该知道的纠正层级的细节。
在此级别中，我们将回顾在完全恢复模式工作时进行日志备份的原因和方法，以及如何使用这些日志备份文件与完全数据库备份实现数据库还原。完全恢复模式支持将数据库恢复到可用日志备份中的任何时间点，假设可以在失败发生之前，也就是执行最后一次提交事务的时间之前执行尾日志备份。
要记录什么？
在完全恢复模式下，所有操作都将被完全记录。对于对行进行操作的插入、更新和删除操作，这意味着对于修改的每一行，都会有一个日志记录。日志记录执行该语句的事务的ID，描述该事务开始和结束时，哪些页面被更改，哪些数据被更改，等等。
在完全恢复模式下，最低限度执行SELECT INTO、BULK INSERT和CREATE INDEX的操作仍然是完全记录，但操作方式略有不同。执行操作后受影响的行不会被单独记录;只有在执行的数据库页面在被填满时才会被记录的日志中。这就减少了这些操作的日志记录，同时也能确保执行回滚、重做和时间点恢复所需的所有相同信息都存在。Kalen Delaney发表了一些关于日志记录的调查，用于在批量日志和bulk_logging恢复模式下的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) 。在bulk_logging模式下工作时，在记录最小日志操作上两者会产生差异，这将在第6级—在批量日志恢复模式下管理日志中进行更详细的讨论。
为什么备份事务日志?
在完全恢复模式下，只有日志备份可能导致截断日志。 事务日志将保存自上次备份事务日志以来执行过的完整事务记录。 由于所有操作都已被完全记录，因此在繁忙的系统中，日志文件扩展的非常快。
因此，在完全恢复模式下工作时，除了完全备份和（可选）差异备份之外，还必须执行常规事务日志备份。 许多新手或兼职的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:是否进行日志备份?
在type列中，D表示数据库备份，L表示日志备份，I表示差异备份。
请注意，由于可以在不影响备份和还原行为的情况下操作backupset表中的数据，因此你想要通过查询sys.database_recovery_status查看last_log_backup_lsn（参见清单3.5）或在sys.databases表查看log_reuse_wait_desc的值（如果需要备份，将返回LOG_BACKUP）。
如何备份事务日志？
正如前面所讨论的，如果没有先进行至少一次的完整备份，就无法执行事务日志备份。事实上，如果你的数据库处于完全恢复模式但从未进行过备份，那它不会在完全恢复模式下工作。在执行第一次完整备份之前，数据库将处于自动截断模式。
所有数据库备份，包括完整备份、日志备份或其他备份，都使用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分钟或甚至更频繁地进行日志备份。 在这种情况下，为了避免需要还原大量的事务日志文件，你可以选择采用一种由完全备份组成的备份方案，其中穿插了差异备份和事务日志备份。
实际上，备份方案通常是理想和实际之间的综合，是对数据丢失的真实风险以及它将给公司带来成本的评估，降低这种风险所涉及的成本之间的折衷。许多非常重要的业务应用程序会使用稍微简单但仍然严格的备份方案，可能涉及定期夜间完整备份甚至每小时事务日志备份。
日志备份的频率也可以由数据库所涉及的事务数量决定。对于非常繁忙的数据库，可能需要经常备份以便控制日志的大小。
没有简单的方法来计算日志备份的频率。 大多数DBA会对日志备份的频率进行最佳估计，然后观察文件的增加的特性，然后根据需要调整备份方案，以防止它们变得过大。
日志链和如何打破日志链？
如上所述，如果没有首先进行至少一次完整备份，则无法执行事务日志备份。为了让数据库恢复到某个时间点，或者恢复到在特定日志备份结束时或者在特定日志备份中的某个时间点，就必须有完整的不间断的日志记录链，从第一次日志备份开始在完整（或差异备份）之后，直到故障点。这称为日志链。
有许多方法可以破坏日志链，将数据库恢复到在事件发生之前进行日志备份，就意味着会破坏链。简而言之，如果你关心恢复数据的能力，那么打破这个链条并不是一个好主意。打破锁链最常见的两种方式包括:事务日志备份文件的丢失或损坏——弄将只能恢复到上一次良好的日志备份。日志链将在下一个良好的完全备份或差异备份时再次启动。
切换到简单恢复模式——如果你从完全恢复模式切换到简单恢复模式，这将打破日志链，因为会启动检查点，并且立即截断事务日志。当你返回到完全恢复模式时，则需要另一个完整备份来重新启动日志链。事实上，在进行完整备份之前，数据库将保持自动截断模式，无法备份日志文件。
在SQL Server 2008之前，有一些命令，即BACKUP LOG WITH NO_LOG或BACKUP LOG WITH TRUNCATE_ONLY（它们在功能上等效），当命令发出时，执行日志文件会强制被截断，从而破坏日志链。也就是说不能在任何版本的SQL Server中发出这些命令，但是在这里提到它们，因为在处理“失控的日志文件”时，如果不理解这些命令对恢复数据库的能力的影响，仍然会被粗心的人使用。恢复他们的数据库有关更多详细信息，请参阅第8级 - 帮助，我的日志已满。
尾部日志备份？
只要你有一个最近的完整备份和一个完整的日志链，您就可以将数据库恢复到它在任何失败之前的最后日志备份结束时的状态。但是，假设你每小时进行事务日志备份，然后在下午1:45发生故障。你可能会损失45分钟的数据;事实上，如果这些错误是不可逆的，那么实时事务日志是不可检索的，那么这就是丢失的数据量。
但是，有时即使数据文件不可用，实时事务日志仍然可用，特别是当事务日志包含在单独的专用驱动器上。如果是这种情况，则应备份实时事务日志，即执行自上次日志备份以来生成的日志记录的最终备份。这将捕获实时日志文件中的剩余日志记录，直至故障点。这称为尾部日志备份，是在开始还原和恢复操作之前应执行的最后一个操作。
尾部日志备份和最低日志记录的操作
如果由于数据库故障导致数据文件不可用，并且日志的尾部包含最少日志记录的操作，那么就不能执行尾日志备份，因为这需要访问数据文件中已更改的数据区段。这将在第6级“以批量记录模式管理事务日志”中进行更详细的介绍。
如果要还原的数据库处于联机状态，则会按如下方式备份日志尾部：
BACKUP LOG DatabaseNameTO DISK ='FileLocation \ DatabaseName_Log.bak'WITH NORECOVERY
NORECOVERY选项将数据库置于还原状态，并假定您要执行的下一个操作是恢复。如果数据库处于脱机状态且无法启动，你仍应尝试按照上述描述备份日志尾部（尽管可省略NORECOVERY选项，因为不会进行任何事务处理）。
如果你确定日志文件已损坏，则这里建议你使用最后的方法，尝试使用以下命令执行尾部日志备份：
BACKUP LOG DatabaseNameTO DISK ='FileLocation \ DatabaseName_Log.bak'WITH CONTINUE_AFTER_ERROR
如果主数据库和数据文件已损坏，但日志可用，Microsoft建议重建master数据库，然后备份上一个活动日志。但是，这些主题超出了本级的范围，我建议你参阅文档了解更多细节，见http://msdn.microsoft.com/en-us/library/ms190952.aspx。
执行恢复和恢复？
在执行了尾日志备份之后，如果可能，下一步是恢复最后一个完整备份(如果合适的话，接下来是差异备份)，然后将恢复完整的日志备份文件序列，包括尾日志备份。这个还原操作序列的基本语法如下:
RESTORE {DATABASE | LOG} DatabaseNameFROM DISK ='FileLocation\FileName.bak'WITH NORECOVERY;
如果在恢复时省略了WITH NORECOVERY选项，那么默认情况下，恢复命令将继续恢复。换句话说，SQL Server将尝试协调数据和日志文件，前滚已完成的事务，然后根据需要回滚未完成的事务。通过WITH NORECOVERY，指示SQL Server输入恢复序列，使得在执行任何回滚之前前滚更多操作。在还原序列中还原最后一个备份后，可以按如下方式恢复数据库:
RESTORE DATABASE DatabaseName WITH RECOVERY
常见的要求是将数据库还原到其他位置，在这种情况下，你只需将文件作为还原过程的一部分进行移动，如下所述：http：//msdn.microsoft.com/en-us/library/ms190255.aspx。
数据库故障后的恢复？
下面的示例描述如何在数据库数据文件不再可访问的情况下恢复数据库。
完全恢复到失败点？
假设“实时”事务日志可以在可能是由硬件故障引起的数据库故障之后到达，那么在理论上，应该可以通过以下步骤恢复和恢复数据库，直到出现故障为止:
1.恢复最近的完整备份(加上差异，如果适用)
2.依次恢复在完全（或差异）备份之后并在故障发生之前完成的每个事务日志备份
3.还原尾部日志备份
4.恢复数据库
联机丛书中的许多示例演示了从“备份集”(也就是存储所有备份的单个“设备”)恢复和恢复。在实际操作中，这意味着，当备份到磁盘时，备份设备是位于磁盘的某个地方的单个的.bak文件。
例如，列表5.2中所示的简单示例是使用一个完整备份和一个事务日志备份组成的备份集，示例展示了如何执行完整还原。为了运行这段代码，你首先需要重新创建testdb数据库，然后插入一些数据行(为方便起见，执行此操作的脚本CreateAndPopulateTestDB.sql包含在此级别的代码下载中)。你还需要在数据库服务器的本地C:驱动器上创建一个“备份”目录，根据需要修改文件路径。
联机丛书中的许多示例演示了从“备份集”(也就是存储所有备份的单个“设备”)恢复和恢复。在实际操作中，这意味着，当备份到磁盘时，备份设备是位于磁盘的某个地方的单个的.bak文件。
例如，列表5.2中所示的简单示例是使用一个完整备份和一个事务日志备份组成的备份集，示例展示了如何执行完整还原。为了运行这段代码，你首先需要重新创建testdb数据库，然后插入一些数据行(为方便起见，执行此操作的脚本CreateAndPopulateTestDB.sql包含在此级别的代码下载中)。你还需要在数据库服务器的本地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时间，该时间位于特定日志文件中记录的事务的时间范围内。在这种情况下，数据库将恢复到指定时间时最后提交的事务。当你知道要恢复到什么时间段，但不知道日志备份包含的具体时间时，这种方法非常有用。
也可以恢复到特定的标记事务。例如，当你需要将某个应用程序访问的多个数据库恢复到逻辑一致的点时，这是非常有用的。本文不再进一步讨论这个主题，但你可以在网上找到更多相关书籍(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-multi -database -to-a-common.aspx。
在“不好的交易”之后恢复
除了所有数据库故障的上下文之外，其他还需要还原数据库备份和事务日志，以便在错误的数据修改之前将数据库返回到特定时间点，例如删除或截断表。
你对这种情况的反应将取决于问题的性质。如果可能，你可以断开所有用户与数据库的连接(在通知他们之后)，并评估刚刚发生的事情的影响。在某些情况下，你可能需要估计问题发生的时间，然后使用时间恢复点对数据库和日志进行完全恢复。
恢复完成后，您必须通知用户一些事务可能已经丢失，并请求原谅。
当然，通常你无法以这种方式中断正常的业务操作，以修复意外的数据丢失。由于活动数据库仍在运行和被访问，你可以尝试在备用模式下恢复数据库的备份。这允许恢复进一步的日志备份，但与使用NORECOVERY时不同，数据库仍然是可读的。恢复方案可能是这样的:
- 在备用模式下，在活动数据库旁边恢复数据库的备份
- 将日志前滚到错误事务发生之前的位置，防止数据丢失。
- 将丢失的数据复制到活动数据库中，并删除恢复的副本。
当然，这个过程不一定是直接的，而且可能相当耗时。除非你购买了专门的日志读取工具，可以直接查询日志备份，否则前滚日志可能意味着一系列步骤都消失，包括恢复日志、检查数据、进一步恢复等等，除非你确切地知道错误事务发生在哪里。步骤3也可能很困难，因为你将向活动系统中引入不一定与数据库当前状态一致的数据，因此可能存在引用完整性问题。
让我们看一下实现上面步骤1和2的示例。首先，让我们从头开始运行CreateAndPopulateTestDB。重新创建TestDB数据库的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：灾难！
为了在不中断正常业务操作的情况下检索丢失的数据，我们将以备用模式恢复TestDB数据库的副本。备用数据库的数据和日志文件（称为ANewTestDB）将移至“备用”目录（你需要事先创建此目录）。
列表5.10:在待机模式下恢复TestDB的一个副本
现在我们有了一个新的数据库，名为ANewTestDB，它处于“备用/只读”模式，如图5.1所示。
图5.1:备用数据库
对ANewTestDB数据库中的LogTest表的查询10行。但是，我们希望将表恢复到它被错误删除之前的状态。因此，下一步是将日志备份还原到备用数据库。
列表5.11:在备用模式下，将日志备份恢复到ANewTestDB数据库
现在，已经对ANewTestDB的查询11行，我们可以准备将这些数据复制回活动数据库。如果我们进一步恢复第二次日志备份，那么进程就会太快，这时备用数据库中的表会丢失。
另一种备用恢复方法是考虑使用第三方工具，比如Red Gate的SQL Virtual restore，它提供了一种方法，可以将备份挂载为活动的、功能齐全的数据库，而无需物理恢复。
不管DBA是否喜欢，开发人员通常都可以访问生产数据库来执行特定的数据加载和更改。DBA和开发人员的共同责任是确保这些工作顺利进行，从而避免导致需要采取刚才所述行为的问题。我们将稍后在第6级—处理批量操作中讨论这个主题。
当然，所需修复行动的性质取决于不良交易。如果表“意外丢失”，那么很可能你将使用备用路由执行恢复。在其他时候，你可能只需要创建一个脚本来“反转”恶意修改即可。
如果损坏只影响到单个列或有限数量的行，那么可以使用SQL Data Compare等工具作为替代，它可以直接与备份文件进行比较，并可以进行行级恢复。
或者,如果您运行SQL Server 2005企业版(或更高版本),可以使用最近的数据库快照，则可以对快照运行查询，以便在查看数据库快照时查看数据,然后写一个更新或插入命令将数据从数据库快照提取到实时源数据库。
最后，作为最后一种手段，一个专门的日志读取工具可能帮助你逆转事务的影响，尽管我不知道SQL Server 2005及更高版本中的是否可以进行可靠工作。
总结
在这个级别中，我们介绍了在完全恢复模式下运行的数据库备份和恢复日志文件的基础知识，这将是许多生产数据库的标准。
对于大多数DBA来说，执行时间点恢复的需求很少，但是如果有必要，那么执行时间点恢复非常重要;DBA的声誉也是取决于它。
在损坏、驱动器故障等情况下，如果幸运的话，时间点恢复可能涉及备份事务日志的尾部并恢复故障点的能力。如果事务日志不可用，或者你正在恢复以使得在“不好事务”发生之前恢复到某个时间点，那么情况将变得更加复杂，但希望本步骤中介绍的一些技术将有所帮助。
资源:
TransactionLogStairway_Level5_Code.zip
本文是SQL Server Stairway中的事务日志管理的一部分
注册我们的RSS订阅，当我们在阶级上发布一个新级别时，您就会得到通知!
302
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
