翻译：SQL Server中事务日志管理的阶梯级别3：事务日志，备份和恢复

原文链接:http://www.sqlservercentral.com/articles/Stairway+Series/73779/，原文作者：Tony Davis ，2011/09/07

该系列

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

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

不能太频繁地说，除非你的数据库在SIMPLE恢复模式下运行，否则在事务日志上执行定期备份是非常重要的。这将控制事务日志的大小，并确保在发生灾难时，可以在灾难发生前不久将数据库恢复到某个点。这些事务日志备份将与常规的完整数据库（数据文件）备份一起执行

如果您正在使用不需要恢复到以前时间点的测试系统，或者很乐意只能恢复到最后一次完整数据库备份，则应该以SIMPLE模式运行数据库。

让我们更详细地讨论这些问题。
备份的重要性

例如，考虑可能由于硬件故障导致SQL Server数据库“崩溃”，以及“活动”数据文件（mdf和ndf文件）以及事务日志文件（ldf文件）的情况不再可访问。

在最坏的情况下，如果这些文件的备份（副本）不存在于其他地方，那么您将遭受100％的数据丢失。为了确保您可以恢复数据库，并在服务器崩溃之前的某个时刻恢复数据，或者由于其他原因导致数据丢失或损坏之前，数据库管理员需要定期备份数据和日志文件。

DBA可以执行三种主要的备份类型（尽管在SIMPLE恢复模式下仅适用前两种）：

    完整数据库备份 - 备份数据库中的所有数据。这本质上是为给定的数据库制作MDF文件的副本。
    差异数据库备份 - 复制自上次完整备份以来发生更改的所有数据。
    事务日志备份 - 自上次事务日志备份（或数据库检查点，如果以SIMPLE恢复模式工作）之后，创建插入到事务日志中的所有日志记录的副本。当进行日志备份时，日志通常会被截断，以便文件中的空间可以被重用，但有些因素可能会延迟这一点（参见第8级 - 帮助，我的日志已满）

一些初级数据库管理员和许多开发人员，可能被“完整”一词所误导，认为完整的数据库备份可以“备份”所有内容。事务日志的数据和内容。这是不正确的。从本质上说，完全备份和差异备份只备份数据，尽管它们也备份了足够的事务日志以恢复备份的数据，并复制备份进行过程中所做的任何更改。但是，实际上，完整的数据库备份不备份事务日志，因此不会导致事务日志的截断。只有事务日志备份会导致日志截断，因此在生产系统中执行日志备份是控制日志文件大小的唯一正确方法。一些常见但不正确的方法将在第8级 - 帮助中讨论，我的日志已满。
文件和文件组备份

大型数据库有时会组织成多个文件组，并且可以对单个文件组或文件组内的文件执行完全备份和差异备份，而不是整个数据库。这个话题不会在这个阶段进一步讨论。
恢复模型

SQL Server数据库备份和还原操作发生在该数据库的恢复模型的上下文中。恢复模型是一个数据库属性，用于确定是否需要（甚至可以）备份事务日志以及如何记录操作。关于可用的还原操作，还有一些差异，有关精细的页面和文件还原，但我们不会在本系列中介绍这些操作。

在一般操作中，数据库将以SIMPLE或FULL恢复模式运行，两者之间最重要的区别如下：

    简单 - 事务日志仅用于数据库恢复和回滚操作。它会在定期检查点自动截断。它不能被备份，所以不能用来将数据库恢复到过去某个时刻已经存在的状态。
    FULL-事务日志在周期性检查点期间不会自动截断，因此可以备份并用于将数据恢复到以前的时间点以及数据库恢复和回滚。

USE master;

-- set recovery model to FULL
ALTER DATABASE TestDB
SET RECOVERY FULL;

-- set recovery model to SIMPLE
ALTER DATABASE TestDB
SET RECOVERY SIMPLE;

-- set recovery model to BULK_LOGGED
ALTER DATABASE TestDB
SET RECOVERY BULK_LOGGED;

清单3.1：设置数据库恢复模型

数据库将采用由模型数据库指定的默认恢复模型。 在很多情况下，这将意味着数据库的“默认”恢复模式是FULL，但是对于模型数据库，不同版本的SQL Server可能具有不同的默认值。
发现恢复模式

理论上，通过执行清单3.2中所示的查询，我们可以找出给定数据库正在使用的模型。

SELECT   name ,
         recovery_model_desc
FROM     sys.databases
WHERE    name = 'TestDB' ;
GO
清单3.2：查询sys.databases以获得恢复模型

但是，要小心这个查询，因为它可能并不总是说实话。 例如，如果我们创建一个全新的数据库，然后立即运行清单3.2中的命令，则会报告数据库处于FULL恢复模式。 但事实上，在进行完整数据库备份之前，数据库将以自动截断模式（即SIMPLE）运行。

我们可以通过在SQL Server 2008实例上创建一个新的数据库来实现这一点，默认的恢复模式是FULL。 我们用一些测试数据创建一个表格，然后检查恢复模型，如代码清单3.3所示。

/* STEP 1: CREATE THE DATABASE*/
USE master ;

IF EXISTS ( SELECT  name
            FROM    sys.databases
            WHERE   name = 'TestDB' ) 
    DROP DATABASE TestDB ;

CREATE DATABASE TestDB ON
(
  NAME = TestDB_dat,
  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDB.mdf'
) LOG ON
(
  NAME = TestDB_log,
  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDB.ldf'
) ;

/*STEP 2: INSERT A MILLION ROWS INTO A TABLE*/
USE TestDB
GO
IF OBJECT_ID('dbo.LogTest', 'U') IS NOT NULL 
    DROP TABLE dbo.LogTest ;
SELECT TOP 1000000
  SomeID = IDENTITY( INT,1,1 ),
  SomeInt = ABS(CHECKSUM(NEWID())) % 50000 + 1 ,
  SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID())) % 26 + 65)
  + CHAR(ABS(CHECKSUM(NEWID())) % 26 + 65) ,
  SomeMoney = CAST(ABS(CHECKSUM(NEWID())) % 
                     10000 / 100.0 AS MONEY) ,
  SomeDate = CAST(RAND(CHECKSUM(NEWID()))
                   * 3653.0 + 36524.0 AS DATETIME) ,
  SomeHex12 = RIGHT(NEWID(), 12)
INTO    dbo.LogTest
FROM    sys.all_columns ac1
        CROSS JOIN sys.all_columns ac2 ;

SELECT   name ,
         recovery_model_desc
FROM     sys.databases
WHERE    name = 'TestDB' ;
GO

name                    recovery_model_desc
-------------------------------------------
TestDB                  FULL

列表3.3：一个新创建的TestDB数据库，分配了FULL恢复模型

这表明我们处于完全恢复模式，但现在检查日志空间使用情况，强制使用CHECKPOINT，然后重新检查日志使用情况，如清单3.4所示。

DBCC SQLPERF(LOGSPACE) ;
-- DBCC SQLPERF reports a 110 MB log file about 90% full

CHECKPOINT
GO

DBCC SQLPERF(LOGSPACE) ;
-- DBCC SQLPERF reports a 100 MB log file about 6% full

清单3.4：日志文件在Checkpoint上被截断！

请注意，日志文件的大小大致相同，但现在只有6％的大小; 日志已被截断，空间可供重用。 尽管数据库被分配到完全恢复模式，但是直到完成第一次完整数据库备份之前，数据库才会真正以该模式运行。 有趣的是，这意味着我们可以通过运行TestDB数据库的完整备份而不是显式强制CHECKPOINT来达到同样的效果。 完整的备份操作会触发一个CHECKPOINT，并且日志被截断。

为了确定哪些恢复模型正在运行，执行清单3.5所示的查询。

SELECT   db_name(database_id) AS 'DatabaseName' ,
         last_log_backup_lsn
FROM     master.sys.database_recovery_status
WHERE    database_id = db_id('TestDB') ;
GO

DatabaseName                last_log_backup_lsn
-----------------------------------------------
TestDB                      NULL
清单3.5：数据库是否处于FULL恢复模式？

如果在last_log_backup_lsn列中出现NULL值，那么数据库实际上处于自动截断模式，因此在数据库检查点出现时将被截断。执行了完整的数据库备份后，您会发现该列中填充了记录备份操作的日志记录的LSN，此时数据库确实处于FULL恢复模式。从这时起，完整的数据库备份将不会影响事务日志;截断日志的唯一方法是备份日志。
切换模型

如果您将数据库从FULL或BULK LOGGED模式切换到SIMPLE模式，这将会中断日志链，并且只能将数据库恢复到切换前最后一次日志备份的时间点。因此，建议在切换之前立即进行日志备份。如果随后将数据库从SIMPLE切换回FULL或BULK LOGGED模式，请记住，数据库实际上将继续以自动截断模式运行（清单3.5将显示NULL），直到您执行另一个完全备份。

如果从FULL切换到BULK_LOGGED模式，则不会中断日志链。但是，在BULK_LOGGED模式下发生的任何批量操作将不会完全记录在事务日志中，因此无法逐个操作地进行控制，这与完全记录的操作相同。这意味着将数据库恢复到包含批量操作的事务日志中的某个时间点是不可能的。您只能恢复到该日志文件的末尾。为了“重新启用”时间点恢复，批量操作完成后切换回FULL模式，并立即进行日志备份。
自动化和验证备份

临时数据库和事务日志备份可以通过SQL Server Management Studio中的简单T-SQL脚本执行。但是，对于生产系统，DBA将需要一种自动执行这些备份的方法，并验证备份是否有效，并且可用于恢复数据。

本主题的全面介绍超出了本文的范围，但是下面列出了一些可用的选项。由于SSMS维护计划的一些缺点，大多数有经验的数据库管理员会选择编写自己的脚本，然后将其自动化。

    SSMS维护计划向导和设计器 -  SSMS中内置的两个工具，允许您配置和调度一系列核心数据库维护任务，包括完整数据库备份和事务日志备份。 DBA还可以运行DBCC完整性检查，调度作业以删除旧的备份文件等等。这些工具的一个很好的描述，以及它们的局限性，可以在Brad McGhee的书中找到，Brad的SQL Server维护计划
    T-SQL脚本 - 您可以编写自定义T-SQL脚本来自动执行备份任务。 Ola Hallengren提供了一套完善且受人尊敬的维护脚本。他的脚本创建了各种存储过程，每个存储过程执行特定的数据库维护任务，包括备份，以及使用SQL代理作业自动执行。 Richard Waymire的“SQL Server代理的阶梯”是关于这个主题的一个很好的信息来源。
    Powershell / SMO脚本 - 比T-SQL脚本功能更强大，功能更强大，但对于许多DBA来说，学习曲线更陡峭，可以使用Powershell来编写和自动执行几乎任何维护任务。请参阅：http://www.simple-talk.com/author/allen-white/。
    第三方备份工具 - 有几个第三方工具可以自动执行备份，并验证和监视它们。大多数提供备份压缩和加密，以及其他功能，以减轻备份管理，验证备份等等。例子包括红门的SQL备份，Quest的LiteSpeed等等。