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

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

托尼·戴维斯，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-事务日志在周期性检查点期间不会自动截断，因此可以备份并用于将数据恢复到以前的时间点以及数据库恢复和回滚。日志文件只在发生日志备份时被截断。

还有第三种模式，BULK_LOGGED，其中通常会产生大量写入事务日志的某些操作执行较少的日志记录，以免压倒事务日志。
可以被最低限度记录的操作

可以被最小化记录的操作的例子包括批量导入操作（例如，使用bcp或BULK INSERT），SELECT / INTO操作以及某些索引操作（例如索引重建）。完整列表可以在这里找到：http://msdn.microsoft.com/en-us/library/ms191244.aspx。

一般而言，以完全恢复模式运行的数据库可能会暂时切换到BULK_LOGGED模式，以最小的日志记录运行此类操作，然后切换回FULL模式。在BULK_LOGGED模式下永久运行并不是减少事务日志大小的可行方法。我们将在管理大容量日志恢复模式中的日志中更详细地讨论这个问题。
选择正确的恢复模式

在FULL恢复模式和SIMPLE模式下操作数据库之间选择的首要条件如下：您愿意承担多少数据丢失的风险？

在SIMPLE恢复模式下，只能进行完全备份和差异备份。假设您完全依赖完全备份，每天凌晨2点执行一次备份，并且服务器在一天凌晨1点发生致命的崩溃。在这种情况下，您将能够在上午2点恢复完整的数据库备份，并将丢失23小时的数据。

可以在完整备份之间执行差异备份，以减少有丢失风险的数据量。所有备份都是I / O密集型进程，但对于完全备份和差异较小的备份来说尤其如此。它们可能会影响数据库的性能，所以不应该在用户访问数据库的时候运行。实际上，如果您以SIMPLE恢复模式工作，您面临数据丢失风险的时间将是几个小时。

如果数据库包含关键业务数据，并且您希望在数分钟而不是以小时为单位的情况下测量数据丢失，则需要在完全恢复模式下操作数据库。在这种模式下，您需要进行完整的数据库备份，然后进行一系列频繁的事务日志备份，然后进行另一次完整备份，依此类推。

在这种情况下，从理论上讲，您可以恢复最近一次有效的完全备份（加上最近的差异备份，如果采取的话），然后是自上次完全备份或差异备份以来的可用日志文件备份链。然后，在恢复过程中，备份日志文件中记录的所有操作都将前滚，以便将数据库恢复到非常接近灾难时间的时间点。

备份日志文件频率的问题将取决于您准备丢失多少数据以及服务器上的工作负载。在重要的财务或会计应用程序中，对数据丢失的容忍度大致为零的情况下，您可能每15分钟进行一次日志备份，或者更频繁地进行备份。在我们之前的例子中，这意味着你可以恢复凌晨2点的完整备份，然后依次应用每个日志文件，假设你有一个完整的日志链，从你用作数据库基础的完整备份在事故发生前15分钟，在上午12时45分采取的措施。事实上，如果当前日志在崩溃后仍然可以访问，那么允许您执行尾部日志备份，那么您可以将数据丢失量降至最低。
日志链和尾部日志备份...

...将在第5级 - 管理日志恢复模式中详细讨论

当然，对于创建和监视运行非常频繁的事务日志备份所需的作业的额外工作量，这些备份所需的I / O资源（尽管在短时间内）以及存储大量备份文件所需的磁盘空间。在选择给定数据库的恢复模式之前，需要在业务层面给予应有的考虑。
设置和切换恢复模式

恢复模式可以使用清单3.1中显示的以下简单命令之一来设置。

使用大师;

- 将恢复模式设置为FULL
ALTER DATABASE TestDB
SET RECOVERY FULL;

- 将恢复模式设置为SIMPLE
ALTER DATABASE TestDB
设置恢复简单;

- 将恢复模式设置为BULK_LOGGED
ALTER DATABASE TestDB
SET RECOVERY BULK_LOGGED;

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

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

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

SELECT名称，
         recovery_model_desc
FROM sys.databases
WHERE name ='TestDB';
走

清单3.2：查询sys.databases以获得恢复模型

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

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

/ *步骤1：创建数据库* /
使用大师;

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'
）登录
（
  NAME = TestDB_log，
  FILENAME ='C：\ Program Files \ Microsoft SQL Server \ MSSQL10.MSSQLSERVER \ MSSQL \ Data \ TestDB.ldf'
）;

/ *第2步：将一百万行插入表* /
使用TestDB
走
如果OBJECT_ID（'dbo.LogTest'，'U'）不为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
从sys.all_columns ac1
        CROSS JOIN sys.all_columns ac2;

SELECT名称，
         recovery_model_desc
FROM sys.databases
WHERE name ='TestDB';
走

名称recovery_model_desc
-------------------------------------------
TestDB FULL

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

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

DBCC SQLPERF（LOGSPACE）;
- DBCC SQLPERF报告大约90％已满的110 MB日志文件

CHECKPOINT
走

DBCC SQLPERF（LOGSPACE）;
- DBCC SQLPERF报告大约6％的100 MB日志文件已满

清单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'）;
走

DatabaseName last_log_backup_lsn
-----------------------------------------------
TestDB空

清单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等等。

 
资源：
TLogStairway_Level3.sql

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