事务日志备份与还原

1、14.1 事务日志备份与恢复原理  、本章要点 事务日志备份与恢复原理 尾日志备份 产生备份集 将数据库恢复到故障点 备份恢复中的疑难问题一个不懂事务日志的DBA，是很难掌握数据库的精髓的。事务日志忠实地记录了数据库的活动，所以基于这些记录的活动就可以随心所欲地将数据库的状态恢复到特定的即时点或恢复到故障点。然而，不是每个DBA都能够正确完成这些操作的。其中的奥秘在哪里呢？本章深入研究事务日志备份与恢复操作。14.1  事务日志备份与恢复原理下面我们首先来学习事务日志备份与恢复的原理。  事务日志备份与恢复原理事务日志备份只能与完全恢复模型和大容量日志记录恢复模型

2、一起使用。在简单模型下，事务日志有可能被破坏，所以事务日志备份可能不连续，不连续的事务日志备份没有意义，因为基于日志的恢复要求日志是连续的。可以使用事务日志备份将数据库恢复到特定的即时点（如输入多余数据前的那一点）或恢复到故障点。恢复事务日志备份时，SQL Server 2005重做事务日志中记录的所有更改。当SQL Server 2005到达事务日志的最后时，已重新创建了与开始执行备份操作的那一刻完全相同的数据库状态。如果数据库已经恢复，则SQL Server 2005将回滚备份操作开始时尚未完成的所有事务。一般情况下，事务日志备份比数据库备份使用的资源少。因此可以比数据库备份更经常地创建事

3、务日志备份。经常备份将减少丢失数据的危险。图14-1所示为基于完全恢复模型下的1个完全备份N个连续的事务日志备份的策略。如果中间的日志备份02删除或者损坏，则数据库只能恢复到日志备份01的即时点。图14-1  事务日志备份与恢复原理假如日志备份01、02和03都是完整的，那么在恢复时，先恢复数据库完全备份，然后依次恢复日志备份01、02和03。如果要恢复到故障点，就需要看数据库的当前日志是否完整，如果是完整的，可以做一个当前日志的备份，然后依次恢复日志备份04就可以了。基于事务日志的备份还可以恢复到某个日志备份中间的时刻，称为时点恢复。比如我们可以在恢复数据库完全备份后，恢复数据库在

4、完全备份和日志备份01中间的某个时刻，这就是时点恢复。这里的时点必须是合法的（看日志备份的时间），而不能超出日志备份的时间序列，否则系统不会执行。比如现在只有日志备份01，其时刻为12:11，假如我们指定恢复到12:12，那么这样的时点是非法的。  事务日志备份连续的奥秘连续的事务日志备份是备份和恢复事务日志的基本要求。那么，什么样的事务日志备份是连续的呢？LSN（日志序列号）是用于衡量事务日志备份是否连续的基本方法。1连续的事务日志备份当我们通过备份操作形成备份后，我们可以执行restore headeronly语句来查看备份集中的事务日志备份，判断其是否连续。restore he

5、aderonly from disk='c:test2.bak'查看的结果如图14-2所示。我们可以得出结论：该事务日志备份序列是连续的！为什么呢？因为这些事务日志备份的LSN首尾连接，后一个日志备份的FirstLSN等于前一个日志备份的LastLSN。 日志备份（编号2）：FirstLSN：29000000035800179，LastLSN：29000000047000001。 日志备份（编号3）：FirstLSN：29000000047000001，LastLSN：30000000001900001。 日志备份（编号4）：FirstLSN：30000000001900001

6、，LastLSN：30000000008100001。图14-2  实例的事务日志备份序列因为根据这3个日志备份序列，它记录的事务日志起点是第1个日志备份的FirstLSN：29000000035800179。终点是最后一个日志备份的LastLSN：30000000008100001。光盘视频：视频1402.exe（连续的事务日志备份）。2不连续的事务日志备份不连续的日志备份就是指在产生的日志备份序列中，出现了前后首尾不能续接的情况。这种情况主要发生在初学或者刚开始做DBA的读者身上，不断切换数据库的恢复模型，比如从简单恢复模型切换到完全恢复模型，或者从完全恢复模型切换到大容量日志记

7、录模型，这都是DBA的大忌！假如你的日志备份出现下列情况。那么，这样的日志序列LSN首尾不能衔接，无法连接起来执行恢复操作！ 日志备份（编号2）：FirstLSN：29000000035800179，LastLSN：29000000047000001。 日志备份（编号3）：FirstLSN：30000000001900001，LastLSN：30000000008100001。提示：DBA一定要千方百计确保当前日志和日志备份序列的安全，同时还要保证日志序列的完整，判断是否完整的方法就是执行restore headeronly语句。  恢复到即时点的奥秘正是因为有了连续的、完整的事务日

8、志备份序列，配合一个完整数据库备份，我们可以将数据库的状态恢复在日志序列中间的任意一个即时点。但是，这样做是有前提条件的。1正确的完整数据库备份首先，必须要有一个正确的完整数据库备份，为什么这里要强调“正确”二字呢？如果读者已经对事务日志的连续性概念有正确认识和理解的话，那么这里的“正确”二字就代表完整数据库备份必须是在第1个日志备份序列的时间点之间完成的。本书配套光盘收录了一个bak备份文件，包括了1个完整数据库备份和3个连续的事务日志备份，我们看到编号为1的是完整数据库备份，其余3个是事务日志备份。如图14-3所示。图14-3  正确的完整数据库备份完整数据库备份的日志区间是：2

9、900000003580017929000000043400001。第1个事务日志备份的区间是：2900000003580017929000000047000001。所以，这里的完整数据库备份就是正确的，因为恢复完整数据库备份，其状态会停留在事务日志备份1中间的某个即时点。然后可以连续执行事务日志备份来进行恢复。什么是不正确的完整数据库备份呢？就是完整数据库备份完成的即时点处于日志备份序列之外。如图14-4所示。图14-4  事务日志备份与恢复原理提示：永远也不能指望停留在日志备份之外，即时点的完整数据库备份和日志备份能够配合起来进行恢复，因为完整数据库备份和日志备份的日志序列之间产

10、生了中断。我们可以把这个过程理解为火车的工作机制。火车头（完整数据库备份）和车厢（日志备份序列）之间首尾相接，所以我们可以从头走到尾。如果车头和车厢之间发生断裂（不正确的完整数据库备份），我们就只能开走火车头了（将数据库恢复到完整数据库备份完成的即时点）！同样，如果车厢之间发生断裂（事务日志备份序列断裂），我们就只能走到相连接的部分（恢复连续的日志备份序列），其余部分没有任何意义！2正确的即时点这句话的含义是，永远不要指望将数据库的状态恢复到日志序列记录的LSN区间之外！这很好理解，因为LSN没有记录的数据库活动，数据库的恢复机制无凭无据，如何恢复？  恢复到故障点的奥秘无论我们翻阅

11、SQL Server 2005的联机丛书，还是我们查阅有关的资料，都会告诉我们：SQL Server 2005拥有将数据库恢复到故障点的能力！然而，很遗憾的是，没有一本图书好好地告诉我们作者是如何将数据库恢复到故障点的！我们首先从原理上来理解恢复到故障点的奥秘，如图14-5所示。图14-5  恢复到故障点的奥秘从图中我们可以看出，要将数据库恢复到故障点，必须满足3个条件。1正确的完整数据库备份这个很好理解，而且DBA一般都会做。2连续的事务日志备份序列除非存储设备出现故障，否则这个问题也很好解决。3正确备份最后一个日志备份和故障点之间的日志这一点在目前的SQL Server 2005

12、的图书中几乎没有提到！稍微有点实际经验的DBA（这里是指真正从事大型数据库管理的DBA），肯定会意识到这个问题的严重性！如果我们按照目前的市面上的其他图书去操作，当某个故障发生的时候，我们永远无法将数据库恢复到故障点，因为我们最多只能将数据库恢复到最后一个日志备份完成的时刻，那么，在最后一个日志备份完成的时刻到故障点之间的日志呢？等待DBA的将是被老板炒鱿鱼的悲惨命运！提示：要想将数据库恢复到故障点，就必须深刻理解SQL Server 2005的尾日志备份的原理。很显然，我们必须将这一段特殊的日志（最后日志备份完成时刻故障点发生时刻）备份下来，从而使从完整数据库备份时刻到故障点时刻的所有日志备

13、份序列是完整的！如图14-6所示。图14-6  尾日志备份  尾日志备份因此，要想完成故障点的恢复，就必须完成尾日志的备份。接下来我们就来学习尾日志的相关话题。1尾日志的存储首先一个问题，尾日志存储在哪里？很显然，答案是当前的日志文件中。当前日志文件保存的内容包括了最后一个成功的日志备份到当前故障点所有的事务。所以，一旦最后一个日志文件备份和故障点之间数据库的日志文件不幸发生介质故障，比如存放日志文件的硬盘损坏，那么这种情况下，上帝也无法挽救一个DBA的命运！由此可以看出，日志文件对数据库，对DBA的重要性！所以如果无法完成尾日志备份，则只能将数据库恢复到创建最后一个事务日

14、志备份时的点。自上一次事务日志备份后对数据库所做的更改将丢失，必须手工重做。2与正常日志备份的区别与正常日志备份相似，尾日志备份将捕获所有尚未备份的事务日志记录。但尾日志备份与正常日志备份在下列几个方面有所不同。 如果数据库损坏或离线，则可以尝试进行尾日志备份。仅当日志文件未损坏且数据库不包含任何大容量日志更改时，尾日志备份才会成功。如果数据库包含要备份的、在记录间隔期间执行的大容量日志更改，则仅在所有数据文件都存在且未损坏的情况下，尾日志备份才会成功。 尾日志备份可使用COPY_ONLY选项独立于定期日志备份进行创建。仅复制备份不会影响备份日志链。事务日志不会被尾日志备份截断，并且捕获的日志

15、将包括在以后的正常日志备份中。这样就可以在不影响正常日志备份过程的情况下进行尾日志备份，例如，为了准备进行在线还原。 如果数据库损坏，则尾日志可能会包含不完整的元数据，这是因为某些通常可用于日志备份的元数据在尾日志备份中可能会不可用。使用CONTINUE_AFTER_ ERROR进行的日志备份可能会包含不完整的元数据，这是因为此选项将通知进行日志备份而不考虑数据库的状态。14.2  尾日志备份对于将数据库恢复到即时点，很好理解也很好操作。下面我们重点来研究将数据库恢复到故障点时必不可少的操作，即尾日志备份。但是，需要注意的是，如果在Management Studio中按照默认设置是永

16、远无法完成尾日志备份的。  图形化尾日志备份操作图14-7所示为选择日志备份的数据库的【选项】选项卡。默认情况下选择的是【截断事务日志】单选按钮，这样将永远无法备份尾日志。提示：要完成尾日志备份，需要在图14-7中选择“备份日志尾部，并使数据库处于还原状态”选项。图14-7  【选项】选项卡  用Backup Log语句完成尾日志备份也可以直接执行Backup Log语句来完成日志备份。下面介绍该语句的语法形式。1语法形式Backup Log语句的语法形式如下。BACKUP LOG database_name | database_name_var  &

17、#160;   TO <backup_device> ,.n   MIRROR TO <backup_device> ,.n .next-mirror       WITH       BLOCKSIZE = blocksize | blocksize_variable       , CHECKSUM | NO_CHECKSUM     &#

18、160; , STOP_ON_ERROR | CONTINUE_AFTER_ERROR       , DESCRIPTION = 'text' | text_variable       , EXPIREDATE = date | date_var      | RETAINDAYS = days | days_var       , PASSWORD = password |

19、password_variable       , FORMAT | NOFORMAT       , INIT | NOINIT       , NOSKIP | SKIP       , MEDIADESCRIPTION = 'text' | text_variable       , MEDIANAME = media_nam

20、e | media_name_variable       , MEDIAPASSWORD = mediapassword | mediapassword_variable       , NAME = backup_set_name | backup_set_name_var       , NO_TRUNCATE       , NORECOVERY | STANDBY = undo_file

21、_name       , NOREWIND | REWIND       , NOUNLOAD | UNLOAD       , RESTART       , STATS = percentage       , COPY_ONLY       2主要参数对于其他参数读者可以参阅联机丛书的有关说

22、明。与备份尾日志有关的主要参数如下。  NO_TRUNCATE：只与BACKUP LOG一起使用。指定不截断日志，并使数据库引擎尝试执行备份，而不考虑数据库的状态。该选项允许在数据库损坏时备份日志。  BACKUP LOG的NO_TRUNCATE选项相当于同时指定COPY_ONLY和CONTINUE_AFTER_ERROR。  NO_LOG | TRUNCATE_ONLY：通过放弃活动日志以外的所有日志，无须备份复制日志即可删除不活动的日志部分，并截断日志。该选项会释放空间。因为并不保存日志备份，所以没有必要指定备份设备。NO_LOG和TRUNCATE_ONLY是

23、同义的。使用NO_LOG或TRUNCATE_ONLY截断日志后，记录在日志中的更改不可恢复。为了进行恢复，请立即执行BACKUP DATABASE以执行完整备份或完整差异备份。3使用方法要备份尾日志，主要使用Truncate_Only参数就可以。本书的实例代码如下。BACKUP LOG db_test TO  DISK = N'C:test2.bak' WITH  NO_TRUNCATE , NOFORMAT, NOINIT,  NAME = N'db_test-事务日志 备份', SKIP, NOREWIND, NOUNLOAD,

24、  NORECOVERY ,  STATS = 10GO14.3  产生备份集通过前面的学习，我们已经知道SQL Server 2005数据库提供了将数据库的状态恢复到故障发生点的功能。但是这些功能的顺利执行需要有一些前提条件，比如联机日志不能损坏，否则将丢失最后一次日志备份完成时刻到故障点的事务。很多DBA不了解这其中的奥秘，往往会想当然地认为利用已有的备份日志就可以将数据库恢复到故障点，忘记实际上还需要做一次日志备份才能恢复的奥秘。接下来我们通过一个具体的实例来完成将数据库恢复到故障点的功能。  案例设计案例的设计和完成的思路如下。1案例步骤（1）新

25、建数据库db_test，数据库工作在完全恢复模型下，新建表t_clusterindextest，向表中录入1001条数据。查询得到数据库的日志文件记录的日志区间。（2）做一次完整数据库备份。查询得到备份后的数据库的日志区间和备份集中的日志区间。（3）删除99条数据，产生事务日志，查询得到数据库的日志区间。（4）第1次完成事务日志备份，按照默认设置就可以，即不是尾日志备份。查询备份后的数据库日志区间和备份集的日志区间。（5）删除101条数据，产生事务日志，查询得到数据库的日志区间。（6）第2次完成事务日志备份，按照默认设置就可以，即不是尾日志备份。查询备份后的数据库日志区间和备份集的日志区间。（

26、7）删除表中的1条记录，删除完毕后模拟故障发生。（8）备份尾日志，然后尝试进行恢复操作。案例的步骤可以用图14-8来表示。图14-8  案例步骤2验证思路在备份过程形成的最后的备份集中，我们可以这样来进行验证。（1）利用完整数据库备份日志备份1，可以将数据库恢复到删除99条记录的状态。（2）利用完整数据库备份日志备份1日志备份2，可以恢复到将数据库删除200条记录的状态，而无法将数据库恢复到删除201条记录的状态。（3）由于有尾日志备份，所以利用完整数据库备份日志备份1日志备份2尾日志备份来将数据库恢复到删除201条记录的状态。3结论通过上述实验步骤，说明尾日志备份在将数据库恢复到故

27、障点时的重要性。读者可以深刻理解联机日志千万不能出故障的根本原因。  产生备份集接下来介绍如何形成备份集。1产生数据库按照与前面章节同样的办法，创建新的db_test数据库。创建表t_clusterindextest，生成1001条数据。执行dbcc log命令查询此时数据库的日志情况如图14-9所示。 第1条日志记录的Current LSN：0000001d:0000001a:0001。 最后1条日志记录的Current LSN：0000001d:00000137:00a2。图14-9  产生数据库后的日志2产生完整数据库备份（1）按照图14-10所示界面产生完整数据库备

28、份。图14-10  产生完整数据库备份（2）执行dbcc log命令查询数据库的日志情况如图14-11所示。图14-11  产生完整数据库备份后的日志 第1条日志记录的Current LSN：0000001d:00000166:00b3。 最后1条日志记录的Current LSN：0000001d:000001b5:0003。（3）执行restore headeronly命令查询备份集中的日志情况如图14-12所示。图14-12  产生完整数据库备份后的备份集日志Ø    FirstLSN：29000000035800179。

29、6;    LastLSN：29000000043400001。3产生第1次日志备份（1）执行下列代码删除99条记录。Where t_t_id<=99光盘代码：代码1402.sql。（2）执行dbcc log命令查询数据库的日志情况，如图14-13所示。图14-13  删除99条数据库后的数据库日志 第1条日志记录的Current LSN：0000001d:00000166:00b3。 最后1条日志记录的Current LSN：0000001d:000001ba:0067。（3）按照图14-14所示默认设置备份数据库的日志。也可以执行下列代码完成同样的功能，

30、注意，这里不是完成尾日志备份，而是产生了截断。BACKUP LOG db_test TO  DISK = N'C:test2.bak'WITH NOFORMAT, NOINIT,  NAME = N'db_test-事务日志备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10GO光盘代码：代码1403.sql。（4）执行dbcc log命令查询备份后的数据库日志如图14-15所示。 第1条日志记录的Current LSN：0000001d:00000166:00b3。 最后1条日志记录的Current

31、 LSN：0000001d:000001ba:0067。（5）执行restore headeronly命令查询备份集中的日志如图14-16所示。图14-14  备份事务日志图14-15  第1次日志备份后的数据库日志图14-16  产生第1次日志备份后的备份集日志4产生第2次日志备份（1）执行下列代码删除101条记录。Where t_t_id>99 AND t_t_id<=200光盘代码：代码1404.sql。（2）执行dbcc log命令查询删除后的数据库日志如图14-17所示。图14-17  删除101条记录后的数据库日志 第1条日志记录

32、的Current LSN：0000001d:00000166:00b3。 最后1条日志记录的Current LSN：0000001e:00000010:0008。（3）第2次备份日志，不备份尾日志。（4）执行dbcc log命令查询备份后的数据库的日志，如图14-18所示。 第1条日志记录的Current LSN：0000001e:00000013:0001。 最后1条日志记录的Current LSN：0000001e:00000027:0001。图14-18  第2次事务日志备份后的数据库日志（5）执行restore headeronly命令查询备份集中的日志区间如图14-19所示

33、。图14-19  第2次日志备份后的备份集日志5模拟故障发生（1）执行下列代码删除1条记录。where t_t_id=555光盘代码：代码1405.sql。（2）执行dbcc log命令查询数据库的日志，如图14-20所示。图14-20  模拟故障发生时的日志 第1条日志记录的Current LSN：0000001e:00000013:0001。 最后1条日志记录的Current LSN：0000001e:0000004c:0005。6尾日志备份（1）选择备份日志，在如图14-21所示的选项卡中选择进行尾日志备份。（2）也可以通过执行1401.sql来完成同样的过程，执行情

34、况如图14-22所示。7数据库日志所有的操作执行完毕后，执行dbcc log命令查询数据库的日志如图14-23所示。图14-21  备份尾日志图14-22  执行尾日志备份的情况图14-23  执行备份完毕后的数据库日志 第1条日志记录的Current LSN：0000001e:00000013:0001。 最后1条日志记录的Current LSN：0000001e:00000050:0001。8最终的备份集日志查询最终的备份集的日志如图14-24所示。图14-24  最后的备份集日志14.4  在线恢复到故障点如果数据库没有损坏，我们就可以在

35、线将数据库恢复到故障点。在线恢复将使用系统数据库msdb中存储的数据库备份信息，但使用的日志还是备份集中的日志。  存储备份信息的系统表SQL Server 2005将备份信息统一保存在msdb系统数据库中，要查询数据库的备份集信息，可以通过执行下列语句来完成。光盘代码：代码1406.sql。执行结果如图14-25所示。图14-25  保存备份集信息的系统表  在线恢复到故障点在Management Studio中选择还原数据库，出现如图14-26所示的【常规】选项卡。图14-26  【常规】选项卡选择恢复完整数据库备份和连续的3个日志备份，这样就可以将

36、数据库的状态恢复到故障点。14.5  用Bak文件恢复到故障点的奥秘如果数据库被损坏，我们就只能利用备份集文件（通常扩展名为BAK）来恢复数据库，如果备份集中包含了尾日志备份，我们同样能将数据库恢复到故障点。前面我们已经介绍了使用restore headeronly命令可以查看备份集文件的头部信息。这里的信息和msdb系统数据库中保存的信息是一致的。区别在于在删除数据库时，我们可以选择是否同时删除msdb系统数据库中的备份信息，而备份集文件的备份信息是存储在其头部的，不会随着msdb系统数据库的备份信息的删除而被删除。  发现的问题在Management Studio中选择

37、还原数据库，选择从设备还原，设置设备为bak文件，出现如图14-27所示的【常规】选项卡。图14-27 【常规】选项卡然而，令我们吃惊的是，尽管备份集中有3个日志备份（2个日志备份1个尾日志备份），而且这3个日志备份的LSN是前后续接的，但是在图14-26中我们只能发现2个日志备份的序列，尾日志备份序列不可见，经过笔者的反复实验，这个问题始终存在。因为不能应用尾日志备份，所以肯定不能将数据库恢复到故障点！那么是不是尾日志备份就不能使用了呢？  解决的办法经过若干次反复的实验，发现始终不能在图形化操作中解决这个问题。尽管尾日志的备份序列和前面的日志备份序列首尾连接，但是在图形化界面中确

38、实无法选择。作者将目光投向了RESTORE DATABASE和RESTORE LOG语句上。最后成功解决了这个问题。1成功的实例最后成功完成尾日志恢复的语句实例如下。RESTORE DATABASE db_test FROM  DISK = N'C:test2.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 10GORESTORE LOG db_test FROM  DISK = N'C:test2.bak' WI

39、TH  FILE = 2,  NORECOVERY, NOUNLOAD, STATS = 10GORESTORE LOG db_test FROM  DISK = N'C:test2.bak' WITH  FILE = 3, NORECOVERY,  NOUNLOAD,  STATS = 10GORESTORE LOG db_test FROM  DISK = N'C:test2.bak' WITH  FILE = 4,  NOUNLOAD, STATS = 10GO光盘代

40、码：代码1407.sql。2解决思路下面的语句为恢复尾日志的语句。RESTORE LOG db_test FROM  DISK = N'C:test2.bak' WITH  FILE = 4,  NOUNLOAD, STATS = 10可以看出，上述恢复尾日志的语句和恢复日志序列语句是不同的。RESTORE LOG db_test FROM  DISK = N'C:test.bak' WITH  FILE = 3, NORECOVERY,  NOUNLOAD,  STATS = 10最本质的不

41、同，是尾日志恢复少了一个参数NORECOVERY。3NORECOVERY参数的奥秘那么，为什么NORECOVERY参数就可以恢复尾日志呢？RECOVERY参数指示还原操作回滚任何未提交的事务。在恢复进程后即可随时使用数据库。如果既没有指定NORECOVERY和RECOVERY，也没有指定STANDBY，则默认为RECOVERY。NORECOVERY参数指示还原操作不回滚任何未提交的事务。如果稍后必须应用另一个事务日志，则应指定NORECOVERY或STANDBY选项。使用NORECOVERY选项执行脱机还原操作时，数据库将无法使用。4使用方法还原数据库备份和一个或多个事务日志时，或者需要多个R

42、ESTORE语句（例如还原一个完整的数据库备份并随后还原一个完整的差异备份）时，RESTORE需要对所有语句使用WITH NORECOVERY选项，但最后的RESTORE语句除外。  验证是否恢复到故障点（1）执行1407.sql，执行结果如图14-28所示。图14-28  执行恢复（2）执行dbcc log语句，查询恢复后的数据库的日志情况如图14-29所示。 第1条日志记录的Current LSN：0000001e:00000013:0001。 最后1条日志记录的Current LSN：0000001e:00000064:000a。图14-29  恢复后的数据库日志在图14-23中，我们知道发生尾日志备份后的数据库日志的最后一条日志记录的Curee