SQL Server中的事务日志管理(3/9)：事务日志，备份与恢复

当一切正常时，没有必要特别留意什么是事务日志，它是如何工作的。你只要确保每个数据库都有正确的备份。当出现问题时，事务日志的理解对于采取修正操作是重要的，尤其在需要紧急恢复数据库到指定点时。这系列文章会告诉你每个DBA应该知道的具体细节。

它不会经常提起，除非你的数据库运行在简单（SIMPLE）恢复模式，在事务日志上定期备份非常重要的。这会控制事务日志大小，并且保证，在灾难发生里，你可以恢复你的数据库到灾难发生前的某个时间点。这些日志备份要和定期的完整数据库（数据文件）备份一起。

如果你在测试数据库上工作，不需要恢复到先前的某个时间点，或者会乐意恢复到上次完整备份，那你就可以在简单模式里运行数据库。

我们来详细讨论下这些问题。

备份的重要性

考虑下，例如SQL Server数据库“崩溃”的情况里，可能是硬件故障，“活生生”的数据文件（mdf和ndf文件），和事务日志文件都不能访问了。

最坏的情况，其它地方不存在这些文件备份（副本），那你会遭受100%的数据丢失。为了保证你能恢复数据库，且数据恢复到服务器崩溃前存在的某个时间点，或者恢复到数据因为其他原因丢失或损坏前，DBA需要同时为数据和日志文件做定期备份。

DBA可以进行3个主要类型的备份（但是当在简单（SIMPLE）恢复模式时只有前2个可以应用）

完整数据库备份——在数据库里备份所有的数据。对提供的数据库，这本质是制作MDF文件副本。
差异数据库备份——自上次备份后，制作已改变的任何数据的副本。
事务日志备份——自上次事务日志备份，制作插入到事务日志的所有日志记录的副本（如果在简单（SIMPLE）恢复模式里，是数据库检查点）。当日志备份完成时，通常日志被截断，这样的话文件里的空间可以被重用，但是一些因素可以延迟这个（看第8篇——救命，我的日志满了。）

一些初级的DBA和很多开发者，可能会被“完整”误解，误认为完整备份备份“一切”；数据和事务日志内容同时备份。这是不对的。本质上，完整和差异备份同时只备份数据，尽管它们也备份足够的事务日志来启用备份数据的恢复，当数据库在备份时，重现任何改变。但是，实际上，完整数据库备份不备份事务日志，也不会导致事务日志的截断。只有事务日志备份会造成日志的截断，因此在生产环境里，进行日志备份是唯一控制日志文件大小的正确方法。在第8篇——救命，我的日志满了会讨论一些常见但不正确的方式。

文件和文件组备份

大的数据库有时会由多个文件组组织，是可以在各个文件组、或文件组里的文件上进行完整和差异备份，而不是备份整个数据库。对此以后在以后的文章里不会详谈。

恢复模式

SQL Server数据库备份和恢复操作发生在数据库恢复模式的上下文里。恢复模式是决定你是否需要（或甚至可以）备份事务日志和操作如何记录的数据库属性。对于可用恢复操作，还有页粒度和文件恢复都有一些不同，但这个系列文章不会讨论这些。

一般来说，数据库会运行在简单和完整恢复模式，它们之间的重用区别如下：

简单（SIMPLE）——事务日志只用作数据库恢复和回滚操作。在检查点期间是自动截断。它不会被备份，因此它不能用于还原数据库到过去存在的某个时间点。
完整（FULL）——事务日志在检查点期间不会自动截断，因此可以被备份并用来还原数据到先前的某个时间点，也用作数据库恢复和回滚。只有当日志备份发生时，日志文件会截断。

还有第3个模式，大容量日志（BULK_LOGGED），在这个特定操作里，通常会生成很多写入到事务日志，为了不淹没事务日志而进行很少的记录。

不能最小记录的操作

可以被最小记录的操作包括大容量导入操作（例如使用BCP或BULK INSERT），SELECT/INTO操作和特定索引操作（例如索引重建）。完整列表可以在这里找到：https://msdn.microsoft.com/zh-cn/library/ms191244.aspx

选择正确的恢复模式

在完整恢复模式和简单恢复模式之间选择的最重要标准是：你愿意冒丢失多少数据的风险？

在简单恢复模式里，只有完整和差异备份。比方说你完全依赖完整备份，在每天早上2点进行完整备份，有一天服务器在早上1点的时候服务器经历了一次致命的崩溃。在这个情况里，你只能恢复前一个早上2点的完整数据库备份，会丢失23个小时的数据。

在完整备份之间可以进行差异备份，来减少数据丢失的风险。所有的备份都有密集的I/O流程，对于完整备份更是名副其实，其次是差异备份。他们很可能影响数据库的性能，当用户们正在访问数据库时不应该运行。实际上，如果你运行在简单恢复模式，数据丢失的风险是几个小时。

如果数据库存放关键业务数据，你会更喜欢数据丢失是几分钟而不是几个小时，那样的话你需要运行数据库在完整恢复模式。在这个模式里，你需要进行一次完整数据备份，然后是一系列定期的事务日志备份，再又是另一个完整备份，这样反复进行。

在这个情况下，理论上你可以恢复最近的可靠完整备份（加上最近的差异备份，如果有的话），接下来是可用日志备份链，自上次完整或差异备份后。然后，在恢复过程中，在备份日志里的所有记录的操作会前滚，将数据库恢复到非常接近于灾难时间。

日志文件备份的频率多少会再次取决于你准备丢失的数据，加上你服务器上的工作量。在重要的金融或会计应用上，对于数据丢失的容忍几乎为零，那样的话你可以每15分钟备份一次日志，甚至可以更高频率。在刚才的备份例子里，意味着你可以恢复上午2点的完整备份，然后按顺序应用每个日志文件备份，假设你有自用作数据库恢复基础的完整备份，有完整扩展的日志链（log chain）到上午12点45分，刚好在数据库崩溃前15分钟。事实上，崩溃后当前的日志还是可以访问的，允许你进行尾日志备份（tail log backup），你可以最小化你的数据丢失接近为0。

日志链和尾日志备份……

会在第5篇——完整恢复模式里的日志管理里详细介绍。

当然，使用完整备份会带来更多的维护，创建和监控用来频繁运行事务日志备份的作业，这些都要额外工作，这些备份需要I/O资源（尽管只是短时间），需要磁盘空间来存储大数量的备份文件。对数据库选择合适的恢复模式前，在业务层面，这些都要慎重考虑这些。

设置和切换恢复模式

恢复模式可以使用下列简单的命令进行设置。

 1 USE master; 2  3 -- set recovery model to FULL 4 ALTER DATABASE TestDB 5 SET RECOVERY FULL; 6  7 -- set recovery model to SIMPLE 8 ALTER DATABASE TestDB 9 SET RECOVERY SIMPLE;10 11 -- set recovery model to BULK_LOGGED12 ALTER DATABASE TestDB13 SET RECOVERY BULK_LOGGED;

数据库会调整到model数据库设置的默认恢复模式。在大多数情况下，这会意味着对于数据库的“默认”恢复模式是完整，但SQL Server的不同版本，对于model数据库会有不同的默认配置。

探索恢复模式

理论上，我们可以使用下列查询找出数据库使用的模式。

1 SELECT   name ,2          recovery_model_desc3 FROM     sys.databases4 WHERE    name = 'TestDB' ;5 GO

但是，对这个查询要小心，因为它可能没告诉真相。例如，如果我们创建一个新的数据库，然后立即运行刚才的命令。它会报告这个数据库运行在完整恢复模式下。但事实上，直到完整备份已完成前，数据库会运行在自动-截断模式（即简单模式）。

我们可以在SQL Server实例上创建一个新的数据库来验证这个，默认的恢复模式是完整。我们创建有一些测试数据的表，然后检查下恢复模式。

 1 /* STEP 1: CREATE THE DATABASE*/ 2 USE master ; 3  4 IF EXISTS ( SELECT  name 5             FROM    sys.databases 6             WHERE   name = 'TestDB' )  7     DROP DATABASE TestDB ; 8  9 CREATE DATABASE TestDB ON10 (11   NAME = TestDB_dat,12   FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TestDB.mdf'13 ) LOG ON14 (15   NAME = TestDB_log,16   FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\TestDB.ldf'17 ) ;18 19 /*STEP 2: INSERT A MILLION ROWS INTO A TABLE*/20 USE TestDB21 GO22 IF OBJECT_ID('dbo.LogTest', 'U') IS NOT NULL 23     DROP TABLE dbo.LogTest ;24 SELECT TOP 100000025   SomeID = IDENTITY( INT,1,1 ),26   SomeInt = ABS(CHECKSUM(NEWID())) % 50000 + 1 ,27   SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID())) % 26 + 65)28   + CHAR(ABS(CHECKSUM(NEWID())) % 26 + 65) ,29   SomeMoney = CAST(ABS(CHECKSUM(NEWID())) % 30                      10000 / 100.0 AS MONEY) ,31   SomeDate = CAST(RAND(CHECKSUM(NEWID()))32                    * 3653.0 + 36524.0 AS DATETIME) ,33   SomeHex12 = RIGHT(NEWID(), 12)34 INTO    dbo.LogTest35 FROM    sys.all_columns ac136         CROSS JOIN sys.all_columns ac2 ;37 38 SELECT   name ,39          recovery_model_desc40 FROM     sys.databases41 WHERE    name = 'TestDB' ;42 GO

这表示我们运行在完整恢复模式，但现在我们检查下日式空间使用

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

强制一个检查点（CHECKPOINT），再次检查日志使用率。

1 CHECKPOINT2 GO3 4 DBCC SQLPERF(LOGSPACE) ;5 -- DBCC SQLPERF reports a 100 MB log file about 60% full

注意日志文件近乎一样的大小，但是现在只有61.9%满；日志已被截断，空间可以被重用。虽然数据库被指定为完整恢复模式，实际上这个不应该操作直到第一次完整备份发生后。很有意思，这表示我们可以通过强制检查点（CHECKPOINT），而不是运行TestDB数据库的完整备份。完整备份操作操作触发了检查点（CHECKPOINT），且日志被截断。

为了确定数据库运行在哪个模式里，执行下列查询：

1 SELECT   db_name(database_id) AS 'DatabaseName' ,2          last_log_backup_lsn3 FROM     master.sys.database_recovery_status4 WHERE    database_id = db_id('TestDB') ;5 GO

如果NULL值出现在last_log_backup_lsn列里，那么数据库实际上运行在自动截断模式，因此当数据库检查点发生时会截断。已经进行了完整数据库备份，你会发现那列会填上备份操作记录的日志记录的LSN，在这时，数据库菜真正运行在完整恢复模式。从这一刻开始，完整数据库备份不会在事务日志上影响；唯一截断日志的方法是备份日志。

切换模式

如果你曾从完整或大容量日志模式切换到简单模式，这会中断日志链，你只能恢复数据库到在你切换前，上一次日志备份的时间点。因此，不建议在切换前马上进行日志备份。如果你马上从简单模式切换到完整或大容量日志模式，记住数据库实际上会继续运行在自动截断模式（刚才显示的NULL值），直到你进行了另一个完整备份。

如果你从完整切换到大容量日志，那这不会中断日志链。但是在大容量模式里发生的任何大容量操作不会在事务日志里完整记录，因此不能在操作上控制，同样的方法里完整记录可以。这表示恢复数据到包含大容量操作事务日志里的时间点是不可能的。你只能恢复到日志文件尾。为了“重新启用”到时间点的恢复，在大容量操作完成后切换回完整模式，并立即进行一次日志备份。

备份的自动化和验证

即席数据库和事务日志备份可以通过SSMS里简单的T-SQL脚本进行。但是对于生产系统，DBA需要这些备份的自动化方法，还有验证备份的有效，可以用于还原你的数据。

这个话题的详细讲解已经不是这个系列文章的范围，但会在下面列出一些可用选项。由于SSMMS维护计划的一些问题，大多数DB会选择自己写脚本，然后用作业自动运行它们。

SSMS维护计划向导和设计器——SSMS内建的2个工具，允许你配置和计划一系列的核心数据库维护任务，包括完整数据库备份和事务日志备份。DBA也可以运行DBCC完整性检查，安排作业来删除老的备份文件等等。这些工具的精彩描述，还有它们的限制，可以在Brad McGhee的《Brad的SQL Server 维护计划指导手册》里找到。
T-SQL脚本——你可以写定制的T-SQL脚本来自动化你的备份任务。一个广为流传的维护脚本是Ola Hallengren提供的。它的脚本创建了各种存储过程，每个进行一个特定的数据库维护任务，包括备份，自动地使用SQL代理作业。Richard Waymire的SQL Server代理步步高是关于这个话题的良好信息来源。
Powershell/SMO脚本——比T-SQL脚本更强大，更通用，但对大多数DBA来说学习曲度更陡峭。Powershell可以用作脚本，自动化几乎任何的维护任务。例子可以看下：https://www.simple-talk.com/author/allen-white/
第3方备份工具——很多现存的第三方工具可以自动化备份，也可以验证和监控它们。大多数提供备份压缩和加密等功能让备份管理更加简单。例子包括Red Gate的SQL Backup，Quest的LiteSpeed等等。