您真的理解了SQLSERVER的日志链了吗？
先感谢宋沄剑给本人指点迷津，还有郭忠辉童鞋今天在QQ群里抛出的问题
这个问题跟宋沄剑讨论了三天，再次感谢宋沄剑
一直以来，SQLSERVER提供了一个非常好的管理工具：SSMS
又因为这个管理工具太好了，所有操作的简单化，以至于使我们中毒太深，
对于SQLSERVER内部的一些概念搞得不清不楚
比如这些概念：日志备份链，备份日志链，日志链，备份链，备份集
大部分都是由于SSMS的界面所导致，有时候有些问题做一下实验就可以验证了，偏偏我们信赖了GUI
阅读下文之前大家可以先看一下宋沄剑的文章
SQL Server误区30日谈-Day20-破坏日志备份链之后，需要一个完整备份来重新开始日志链
先说清楚这些概念吧
SQLSERVER只有日志链，备份记录（有些人也叫备份链）本人觉得叫备份记录更合适
下面三个东西说的都是同一样东西
备份集=备份记录=备份链
备份集：比如备份的集合，比如有对一个数据库的完备1、差备、日备1、完备2、日备2，这些数据库的备份的集合就是备份集
不过我更喜欢叫备份记录
备份记录实际上指 SELECT * FROM [msdb].[dbo].[backupset]
截断日志跟日志链断裂是否是同一样东西？
截断日志跟日志链断裂不是同一样东西
什么是日志链
其实大家可以把bak文件理解成一个压缩包，完整备份和差异备份的时候会把数据和日志一起带进压缩包，
而日志备份的时候只会把日志带进压缩包
我们先从一个实验开始吧
测试环境：SQLSERVER2012 开发版
脚本
为了不产生额外的日志，所以脚本里面没有select into语句，本来想select into进去临时表再对临时表进行排序
但是因为select into会产生额外的日志，只有直接对fn_dblog进行排序了
创建数据库
1 USE master2 GO3 --创建数据库4 CREATE DATABASE LogChainTest;5 GO6 --改为完整恢复模式7 ALTER DATABASE LogChainTest SET RECOVERY FULL;8 GO
查看当前的事务日志
1 USE [LogChainTest]2 GO3 SELECT * FROM [sys].[fn_dblog](NULL,NULL) ORDER BY [Begin Time] ASC
进行完整备份
1 --第一个完整备份2 DECLARE @strbackup NVARCHAR(100)3 --改为日期加时间的4 SET @strbackup = 'C:LogChainTest_full1_'5 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ',6 ''), ':', '') + '.bak'7 BACKUP DATABASE LogChainTest TO DISK =@strbackup WITH INIT,CHECKSUM ;8 GO
查看bak文件中的事务日志
1 SELECT * 2 FROM fn_dump_dblog(NULL, NULL, N'DISK', 1, 3 N'c:LogChainTest_full1_20131206202536.bak', DEFAULT, 4 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, 5 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, 6 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, 7 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, 8 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, 9 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,10 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,11 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,12 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,13 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,14 DEFAULT, DEFAULT)
我们再查看此时的数据库事务日志
1 USE [LogChainTest]2 GO3 SELECT * FROM [sys].[fn_dblog](NULL,NULL) ORDER BY [Begin Time] ASC
发现完整备份之后事务日志比之前少了69-10=59行
我们发现bak文件中只记录AllocUnitId，而不记录表名，可能因为bak文件里的日志给SQLSERVER还原用的
而不是给用户查看事务日志用的，所以SQLSERVER干脆不记录表名了，以节省备份时间
看到这里大家会有问题了，为什麽日志会截断了？完整备份之后事务日志比之前少了69-10=59行
这里只能说明SQLSERVER把一些跟本数据库无关紧要的日志截断了，例如创建数据库时候修改master数据库的表
而不能说完整备份可以截断日志
而paul的文章给出了解释：
If you switch recovery models to FULL or BULK_LOGGED, until you take the first full backup,
you are still essentially in the SIMPLE recovery model, and so the log will truncate on checkpoint.
文章地址：
问题：为什麽bak文件里的日志的最后的三条记录会是
LOP_BEGIN_CKPT
LOP_XACT_CKPT
LOP_END_CKPT
我们用下图来表示吧
这里大家可以看一下宋沄剑的文章：再谈SQL Server中日志的的作用
将CheckPoint标记写入日志（标记中包含当前数据库中活动的事务信息），并将Log Block写入持久化存储
我在开头说过事务日志中会放进去bak文件里，但是并不是整个事务日志文件里的日志记录全部放进去
而是把（1）已经checkpoint了的 （2）LAZY WRITTER （3）EAGER WRITTER
还是看宋沄剑的文章吧，这麽复杂的过程我就不概括了：再谈SQL Server中日志的的作用
还有paul的文章：
Debunking a couple of myths around full database backups（揭穿一系列数据库完备的误区）
More on how much transaction log a full backup includes（数据库完备包含了多少事务日志）
实际上checkpoint和数据库备份有着密切联系，备份的时候SQLSERVER需要将哪些数据存入去bak文件
而在备份期间所新生成的事务和变化的数据要不要存入bak文件，这里面比较复杂，就不详细说了
不过有一点要说的是：在数据库备份之前，数据库引擎会自动执行checkpoint，以便在备份中包含对数据库页的全部更改。
我摘抄了网上的一些资料
1 http://blog.csdn.net/tjvictor/article/details/5209604 2 导致CheckPoint检查点的事件： 1.在数据库备份之前，数据库引擎会自动执行checkpoint，以便在备份中包含对数据库页的全部更改。 3 4 2.日志的活动部分超出了服务器在 recovery interval 服务器配置选项中指定的时间内可以恢复的大小。 5 6 3.日志的 70% 已满，并且数据库处于日志截断模式。 7 8 当下列条件都为 TRUE 时，数据库就处于日志截断模式：数据库使用的是简单恢复模式，并且在执行上一条引用数据库的 BACKUP DATABASE 语句后，发生下列事件之一： 9 10 在数据库中执行一项最小日志记录大容量复制操作或一条最条小日志记录的 WRITETEXT 语句。11 12 执行一个在数据库中添加或删除文件的 ALTER DATABASE 语句。13 14 4.停止服务器也会在服务器上的每个数据库中发出一个检查点命令。下列停止 SQL Server 的方法将为每个数据库执行检查点：15 16 使用 SQL Server 配置管理器。17 18 使用 SQL Server Management Studio。19 20 使用 SHUTDOWN 语句。21 --------------------------------------------------------------------------22 http://www.cnblogs.com/CareySon/p/3315041.html23 5.将恢复间隔设置为1分钟，意味着每1分钟会对所有的数据库做一次CheckPoint24 25 错误。将恢复间隔设置为1分钟不能想成建立一个Agent，每分钟写一个CheckPoint命令，这是两码事。这只是意味着每分钟去检查一次是否需要做CheckPoint，如果期间积累的日志量足够，才会对积累足够日志量的数据库去做CheckPoint。即使中间积累了巨量的日志，不到1分钟也不会做CheckPoint。
那么大家可以将bak文件里的事务日志当作为数据库事务日志
备份脚本
1 USE master 2 GO 3 --创建数据库 4 CREATE DATABASE LogChainTest; 5 GO 6 --改为完整恢复模式 7 ALTER DATABASE LogChainTest SET RECOVERY FULL; 8 GO 9 10 11 12 13 14 15 --第一个完整备份16 DECLARE @strbackup NVARCHAR(100)17 --改为日期加时间的18 SET @strbackup = 'C:LogChainTest_full1_'19 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ',20 ''), ':', '') + '.bak'21 BACKUP DATABASE LogChainTest TO DISK =@strbackup WITH INIT,CHECKSUM ;22 GO23 24 25 26 27 28 --第一个差异备份29 USE LogChainTest30 GO31 CREATE TABLE tt(id INT)32 INSERT INTO tt33 SELECT 134 DECLARE @strbackup NVARCHAR(100)35 --改为日期加时间的36 SET @strbackup = 'C:LogChainTest_diff_'37 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ',38 ''), ':', '') + '.bak'39 BACKUP DATABASE LogChainTest TO DISK = @strbackup WITH INIT, DIFFERENTIAL;40 GO41 42 43 44 --第一个日志备份45 USE LogChainTest46 GO47 INSERT INTO tt48 SELECT 249 DECLARE @strbackup NVARCHAR(100)50 --改为日期加时间的51 SET @strbackup = 'C:LogChainTest_log1_'52 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ',53 ''), ':', '') + '.bak'54 BACKUP LOG LogChainTest TO DISK = @strbackup WITH INIT;55 GO56 57 58 59 60 --第二个完整备份61 USE master62 GO63 DECLARE @strbackup NVARCHAR(100)64 --改为日期加时间的65 SET @strbackup = 'C:LogChainTest_full2_'66 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ',67 ''), ':', '') + '.bak'68 BACKUP DATABASE LogChainTest TO DISK = @strbackup WITH INIT;69 GO70 71 72 --第二个日志备份73 USE LogChainTest74 GO75 INSERT INTO tt76 SELECT 377 DECLARE @strbackup NVARCHAR(100)78 --改为日期加时间的79 SET @strbackup = 'C:LogChainTest_log2_'80 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ',81 ''), ':', '') + '.bak'82 BACKUP LOG LogChainTest TO DISK = @strbackup WITH INIT;83 GO
备份策略：完整备份1-》差异备份-》日志备份1-》完整备份2-》日志备份2
还原脚本
1 --差异备份和日志备份1打乱 2 USE master 3 GO 4 --还原第一个完整备份 5 RESTORE DATABASE LogChainTest FROM DISK='C:LogChainTest_full1_20131206230857.bak' 6 WITH REPLACE ,CHECKSUM, NORECOVERY 7 GO 8 9 --还原第一个日志备份10 RESTORE LOG LogChainTest FROM DISK='c:LogChainTest_diff_20131206230920.bak' 11 WITH NORECOVERY12 GO 13 14 --还原差异备份15 RESTORE DATABASE LogChainTest FROM DISK='c:LogChainTest_diff_20131205222718.bak' 16 WITH NORECOVERY17 GO18 19 消息 3136，级别 16，状态 3，第 1 行20 无法还原此差异备份，因为该数据库尚未还原到正确的早期状态。21 消息 3013，级别 16，状态 1，第 1 行22 RESTORE DATABASE 正在异常终止。23 24 25 26 27 --还原第二个日志备份，没有报错28 RESTORE LOG LogChainTest FROM DISK='C:LogChainTest_log2_20131206230927.bak' 29 WITH RECOVERY30 GO 31 32 33 34 35 --可以查询出id列有三行记录36 USE [LogChainTest]37 GO38 SELECT * FROM [dbo].[tt]
上面的还原脚本，我先还原日志备份1，再还原差异备份结果就报错了
1 消息 3136，级别 16，状态 3，第 1 行2 无法还原此差异备份，因为该数据库尚未还原到正确的早期状态。3 消息 3013，级别 16，状态 1，第 1 行4 RESTORE DATABASE 正在异常终止。
还有，为什麽不用还原完整备份2数据也没有丢失？？
我们每次备份的时候，无论是完备、差备、日备都会把日志拷贝到bak文件里
而拷贝的时候会有一个last lsn确保日志顺序
当我先还原日志备份1，然后还原差异备份的时候因为last lsn的顺序不对所以就报错了
为什麽不用还原完整备份2数据也没有丢失？？
这里先说一下完备、差备、日备的大概方式
完备：复制数据和少量的log到bak
差备：复制有差异的数据和少量的log到bak
日备：不复制数据，如果是第一次日备，会把所有的log复制到bak，如果是第二次日备，会把自上一次日备到这次日备的log复制到bak
paul的文章里有解释：
A log backup is *ALL* the log generated since the last log backup
备份策略：完整备份1-》差异备份-》日志备份1-》完整备份2-》日志备份2
我们没有还原完整备份2（相当于丢失了完整备份2），我们的还原顺序是
还原完整备份1（复制数据，根据redo/undo log保证事务一致性）
还原差异备份（复制差异数据，根据redo/undo log保证事务一致性）
还原日志备份1（数据全靠redo/undo log来恢复，根据redo/undo log保证事务一致性）
还原日志备份2（数据全靠redo/undo log来恢复，根据redo/undo log保证事务一致性）
因为日志备份2里面已经包含了从日志备份1到日志备份2的所有log，所以SQLSERVER可以凭借这些log来把数据恢复
而日志备份1里面已经包含了从完整备份1到日志备份1的所有log
所以，按理说，我们只需要还原完备1，日备1，日备2就可以恢复全部数据
测试：
我们使用下面备份脚本和还原脚本，看一下不还原日志备份1，直接还原日志备份2看有没有问题
备份脚本
1 USE master 2 GO 3 --创建数据库 4 CREATE DATABASE LogChainTest; 5 GO 6 --改为完整恢复模式 7 ALTER DATABASE LogChainTest SET RECOVERY FULL; 8 GO 9 10 11 12 13 14 15 --第一个完整备份16 DECLARE @strbackup NVARCHAR(100)17 --改为日期加时间的18 SET @strbackup = 'C:LogChainTest_full1_'19 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ',20 ''), ':', '') + '.bak'21 BACKUP DATABASE LogChainTest TO DISK =@strbackup WITH INIT,CHECKSUM ;22 GO23 24 25 26 27 28 --第一个差异备份29 USE LogChainTest30 GO31 CREATE TABLE tt(id INT)32 INSERT INTO tt33 SELECT 134 DECLARE @strbackup NVARCHAR(100)35 --改为日期加时间的36 SET @strbackup = 'C:LogChainTest_diff_'37 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ',38 ''), ':', '') + '.bak'39 BACKUP DATABASE LogChainTest TO DISK = @strbackup WITH INIT, DIFFERENTIAL;40 GO41 42 43 44 --第一个日志备份45 USE LogChainTest46 GO47 INSERT INTO tt48 SELECT 249 DECLARE @strbackup NVARCHAR(100)50 --改为日期加时间的51 SET @strbackup = 'C:LogChainTest_log1_'52 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ',53 ''), ':', '') + '.bak'54 BACKUP LOG LogChainTest TO DISK = @strbackup WITH INIT;55 GO56 57 58 59 60 --第二个完整备份61 USE LogChainTest62 GO63 INSERT INTO tt64 SELECT 3 UNION ALL65 SELECT 466 DECLARE @strbackup NVARCHAR(100)67 --改为日期加时间的68 SET @strbackup = 'C:LogChainTest_full2_'69 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ',70 ''), ':', '') + '.bak'71 BACKUP DATABASE LogChainTest TO DISK = @strbackup WITH INIT;72 GO73 74 75 --第二个日志备份76 USE LogChainTest77 GO78 INSERT INTO tt79 SELECT 580 DECLARE @strbackup NVARCHAR(100)81 --改为日期加时间的82 SET @strbackup = 'C:LogChainTest_log2_'83 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ',84 ''), ':', '') + '.bak'85 BACKUP LOG LogChainTest TO DISK = @strbackup WITH INIT;86 GO
还原脚本
1 USE master 2 GO 3 --还原第一个完整备份 4 RESTORE DATABASE LogChainTest FROM DISK='C:LogChainTest_full1_20131207102535.bak' 5 WITH REPLACE ,NORECOVERY 6 GO 7 8 9 --还原第二个日志备份10 RESTORE LOG LogChainTest FROM DISK='C:LogChainTest_log2_20131207102602.bak' 11 WITH RECOVERY12 GO
插入的数据太少，日志太少，搞得文件的size不那么明显
结果报错
1 消息 4305，级别 16，状态 1，第 2 行2 此备份集中的日志开始于 LSN 35000000017200001，该 LSN 太晚，无法应用到数据库。可以还原包含 LSN 35000000008600001 的较早的日志备份。3 消息 3013，级别 16，状态 1，第 2 行4 RESTORE LOG 正在异常终止。
因为没有还原日志备份1，缺少了完备1到日备1之间的日志，所以就报错了
我们使用下面的脚本来进行还原，只还原完备1，日备1，日备2
1 USE master 2 GO 3 --还原第一个完整备份 4 RESTORE DATABASE LogChainTest FROM DISK='C:LogChainTest_full1_20131207102535.bak' 5 WITH REPLACE ,NORECOVERY 6 GO 7 8 --还原第一个日志备份 9 RESTORE LOG LogChainTest FROM DISK='C:LogChainTest_log1_20131207102542.bak' 10 WITH NORECOVERY11 GO 12 13 --还原第二个日志备份14 RESTORE LOG LogChainTest FROM DISK='C:LogChainTest_log2_20131207102602.bak' 15 WITH RECOVERY16 GO 17 18 USE [LogChainTest]19 GO20 SELECT * FROM tt
这次成功了，数据都没有丢失，那么说明我丢失了差异备份、完整备份2也没有关系
如果我丢失了日备1、差备、完备2，只有完备1和日备2，那么这个时候你只能祈祷了，你只能还原完备1
差备、日备1、完备2、日备2的数据都已经丢失
BAK文件中日志数量的多少
我刚才说
完备：复制数据和少量的log到bak
差备：复制有差异的数据和少量的log到bak
日备：不复制数据，如果是第一次日备，会把所有的log复制到bak，如果是第二次日备，会把自上一次日备到这次日备的log复制到bak
我怎麽看出来的？
测试：
我们看一下每次备份完毕后，bak文件里面的日志数量
1 USE master 2 GO 3 SELECT * 4 FROM fn_dump_dblog(NULL, NULL, N'DISK', 1, 5 N'c:LogChainTest_full1_20131207102535.bak', DEFAULT, 6 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, 7 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, 8 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, 9 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,10 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,11 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,12 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,13 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,14 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,15 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,16 DEFAULT, DEFAULT)
完备1
差备
日备1
完备2
日备2
在完备2的时候bak中的日志只有44行，说明完整备份只存储一些必要的日志，不是所有日志都存储
完备存储这些日志的作用是在还原的时候根据这些log去redo/undo 保证事务一致性，所以只会写入少量日志
因为完备和差备都是复制数据，所以就没有必要像日备那样全部事务日志都复制到bak里面
而日备2为什麽只有73行记录，因为在日备1的时候SQLSERVER已经截断了事务日志，日备2的日志就像我前面说的
如果是第二次日备，会把自上一次日备到这次日备的log复制到bak
如果我们不想在backup log 的时候截断事务日志，可以使用NO_TRUNCATE和COPY_ONLY这两个backup option
备份脚本 NO_TRUNCATE
1 USE master 2 GO 3 --创建数据库 4 CREATE DATABASE LogChainTest; 5 GO 6 --改为完整恢复模式 7 ALTER DATABASE LogChainTest SET RECOVERY FULL; 8 GO 9 10 11 12 13 14 15 --第一个完整备份 16 DECLARE @strbackup NVARCHAR(100) 17 --改为日期加时间的 18 SET @strbackup = 'C:LogChainTest_full1_' 19 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ', 20 ''), ':', '') + '.bak' 21 BACKUP DATABASE LogChainTest TO DISK =@strbackup WITH INIT,CHECKSUM ; 22 GO 23 24 25 26 27 28 --第一个差异备份 29 USE LogChainTest 30 GO 31 CREATE TABLE tt(id INT) 32 INSERT INTO tt 33 SELECT 1 34 DECLARE @strbackup NVARCHAR(100) 35 --改为日期加时间的 36 SET @strbackup = 'C:LogChainTest_diff_' 37 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ', 38 ''), ':', '') + '.bak' 39 BACKUP DATABASE LogChainTest TO DISK = @strbackup WITH INIT, DIFFERENTIAL; 40 GO 41 42 43 44 --第一个日志备份 45 USE LogChainTest 46 GO 47 INSERT INTO tt 48 SELECT 2 49 DECLARE @strbackup NVARCHAR(100) 50 --改为日期加时间的 51 SET @strbackup = 'C:LogChainTest_log1_' 52 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ', 53 ''), ':', '') + '.bak' 54 BACKUP LOG LogChainTest TO DISK = @strbackup WITH INIT,NO_TRUNCATE; 55 GO 56 57 USE [LogChainTest] 58 GO 59 SELECT * FROM [sys].[fn_dblog](NULL,NULL) ORDER BY [Begin Time] ASC 60 61 62 63 --第二个完整备份 64 USE LogChainTest 65 GO 66 INSERT INTO tt 67 SELECT 3 UNION ALL 68 SELECT 4 69 DECLARE @strbackup NVARCHAR(100) 70 --改为日期加时间的 71 SET @strbackup = 'C:LogChainTest_full2_' 72 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ', 73 ''), ':', '') + '.bak' 74 BACKUP DATABASE LogChainTest TO DISK = @strbackup WITH INIT; 75 GO 76 77 78 --第二个日志备份 79 USE LogChainTest 80 GO 81 INSERT INTO tt 82 SELECT 5 83 DECLARE @strbackup NVARCHAR(100) 84 --改为日期加时间的 85 SET @strbackup = 'C:LogChainTest_log2_' 86 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ', 87 ''), ':', '') + '.bak' 88 BACKUP LOG LogChainTest TO DISK = @strbackup WITH INIT,NO_TRUNCATE; 89 GO 90 91 92 93 94 95 96 USE master 97 GO 98 SELECT * 99 FROM fn_dump_dblog(NULL, NULL, N'DISK', 1,100 N'c:LogChainTest_full1_20131207102535.bak', DEFAULT,101 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,102 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,103 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,104 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,105 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,106 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,107 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,108 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,109 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,110 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,111 DEFAULT, DEFAULT)
我们看一下第一个日志备份和第二个日志备份之后，数据库事务日志和bak文件里面的日志数量
日备1 数据库日志
日备1 bak文件日志
日备2 数据库日志
日备2 bak文件日志
备份脚本 COPY_ONLY
1 USE master 2 GO 3 --创建数据库 4 CREATE DATABASE LogChainTest; 5 GO 6 --改为完整恢复模式 7 ALTER DATABASE LogChainTest SET RECOVERY FULL; 8 GO 9 10 11 12 13 14 15 --第一个完整备份16 DECLARE @strbackup NVARCHAR(100)17 --改为日期加时间的18 SET @strbackup = 'C:LogChainTest_full1_'19 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ',20 ''), ':', '') + '.bak'21 BACKUP DATABASE LogChainTest TO DISK =@strbackup WITH INIT,CHECKSUM ;22 GO23 24 25 26 27 28 --第一个差异备份29 USE LogChainTest30 GO31 CREATE TABLE tt(id INT)32 INSERT INTO tt33 SELECT 134 DECLARE @strbackup NVARCHAR(100)35 --改为日期加时间的36 SET @strbackup = 'C:LogChainTest_diff_'37 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ',38 ''), ':', '') + '.bak'39 BACKUP DATABASE LogChainTest TO DISK = @strbackup WITH INIT, DIFFERENTIAL;40 GO41 42 43 44 --第一个日志备份45 USE LogChainTest46 GO47 INSERT INTO tt48 SELECT 249 DECLARE @strbackup NVARCHAR(100)50 --改为日期加时间的51 SET @strbackup = 'C:LogChainTest_log1_'52 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ',53 ''), ':', '') + '.bak'54 BACKUP LOG LogChainTest TO DISK = @strbackup WITH INIT,COPY_ONLY;55 GO56 57 USE [LogChainTest]58 GO59 SELECT * FROM [sys].[fn_dblog](NULL,NULL) ORDER BY [Begin Time] ASC60 61 62 63 --第二个完整备份64 USE LogChainTest65 GO66 INSERT INTO tt67 SELECT 3 UNION ALL68 SELECT 469 DECLARE @strbackup NVARCHAR(100)70 --改为日期加时间的71 SET @strbackup = 'C:LogChainTest_full2_'72 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ',73 ''), ':', '') + '.bak'74 BACKUP DATABASE LogChainTest TO DISK = @strbackup WITH INIT;75 GO76 77 78 --第二个日志备份79 USE LogChainTest80 GO81 INSERT INTO tt82 SELECT 583 DECLARE @strbackup NVARCHAR(100)84 --改为日期加时间的85 SET @strbackup = 'C:LogChainTest_log2_'86 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ',87 ''), ':', '') + '.bak'88 BACKUP LOG LogChainTest TO DISK = @strbackup WITH INIT,COPY_ONLY;89 GO90 91 USE [LogChainTest]92 GO93 SELECT * FROM [sys].[fn_dblog](NULL,NULL) ORDER BY [Begin Time] ASC
我们看一下第一个日志备份和第二个日志备份之后，数据库事务日志和bak文件里面的日志数量
日备1 数据库日志
日备1 bak文件日志
日备2 数据库日志
日备2 bak文件日志
大家可以看一下这篇帖子
差异备份的作用
既然SQLSERVER靠bak文件里的日志来进行redo/undo，就像上面说的那样，靠完备1，日备1，日备2就可以恢复所有数据
那么差异备份有什么用呢？？
为什麽要有差异备份呢？
差异备份是为了RTO(Recovery Time Objective)
详见：http://blog.sina.com.cn/s/blog_59388e440100oq52.html
如果只做日志备份RTO有可能保证不了
之前说过：差备：复制有差异的数据和少量的log到bak
差异备份：靠DCM页面复制粘贴把bak文件里的数据复制粘贴到mdf文件的数据页
日志备份：redo/undo log
这两个选项肯定是复制粘贴在速度上占优势
当还原了差异备份之后，SQLSERVER根据差异备份时候的log使数据库保存了事务一致性，然后还原日备1
还原日备1的时候，SQLSERVER根据差备的last lsn，只需要redo/undo 差备-》日备1这段时间的log就可以了
这样节省了时间，不用redo/undo 完备1-》日备1这段时间的log，从而保证了RTO
而日志备份，本人觉得是为了保证RPO(Recovery Point Objective)
被神化的日志链
实际上日志链就是我上面说的数据库事务日志，只是备份的时候，SQLSERVER把事务日志放进去bak文件里
我画了几张图
上面那个实验的理解图
————————————————————————————————————————-
————————————————————————————————————
—————————————————————————————————————-
大家可以使用下面两个SQL语句
1 SELECT * FROM [sys].[fn_dblog]()2 SELECT * FROM [sys].[fn_dump_dblog]()
在完整备份、差异备份、日志备份测试一下在哪种备份类型下日志会被截断，截断的意思（数据库事务日志的记录数比bak文件里的日志记录数少）
就像我在开头做的那个实验一样
GUI界面下，默认就是截断事务日志，很多人都以为截断事务日志要加XX backup option才可以截断
日志链断裂的情况
paul的文章说了 SQL Server误区30日谈-Day20-破坏日志备份链之后，需要一个完整备份来重新开始日志链
下面这几种操作都有可能引起日志链断裂
（1）由完整恢复模式或大容量事务日志恢复模式转为简单恢复模式
（2）从数据库镜像进行恢复
（3）备份日志时指定了NO_LOG 或 WITH TRUNCATE_ONLY(还好在SQL Server 2008中这个选项被取消了)
本人觉得日志链断裂是一个非常专业的名称
很多人以为，我做了下面的备份策略：完备1-》差备-》日备1-》完备2-》日备2
如果差备丢失了就认为是日志链断裂了，数据库不能还原到日备1
其实日志链断裂通俗的理解就是：没有将日志放进去bak文件里
怎样的情况才叫 没有将日志放进去bak文件里呢？？
我们知道当我们进行完备、差备、日备的时候都会把日志放进去bak文件里
情况一：
当你将数据库恢复模式由完整恢复模式或大容量事务日志恢复模式转为简单恢复模式
大家还是先看一下这篇文章吧：SQL Server日志在简单恢复模式下的角色
简单恢复模式的机制是：文章中有这样一句话
“在简单恢复模式下，每一次CheckPoint，都会去检查是否有日志可以截断，如果有inactive的VLF时，
CheckPoint都会将可截断部分进行截断，并将MinLSN向后推”
简单来讲就是简单恢复模式不是在backup log DB 的情况下截断日志
而是在checkpoint的时候截断日志，那么既然在checkpoint的时候已经截断了日志，在备份的时候数据库的事务日志
就没有不活动日志用于归档（把日志放进去bak文件）
我们使用下面的脚本进行日志备份就会报错
1 USE master 2 GO 3 CREATE DATABASE LogChainTest; 4 GO 5 ALTER DATABASE LogChainTest SET RECOVERY SIMPLE; 6 GO 7 8 DECLARE @strbackup NVARCHAR(100) 9 --改为日期加时间的10 SET @strbackup = 'C:LogChainTest_log_'11 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ',12 ''), ':', '') + '.bak'13 BACKUP LOG LogChainTest TO DISK = @strbackup WITH INIT;14 GO
1 消息 4208，级别 16，状态 1，第 6 行2 当恢复模式为 SIMPLE 时，不允许使用 BACKUP LOG 语句。请使用 BACKUP DATABASE 或用 ALTER DATABASE 更改恢复模式。3 消息 3013，级别 16，状态 1，第 6 行4 BACKUP LOG 正在异常终止。
但是完整备份和差异备份则不受影响
备份脚本
1 USE master 2 GO 3 CREATE DATABASE LogChainTest; 4 GO 5 ALTER DATABASE LogChainTest SET RECOVERY SIMPLE; 6 GO 7 8 --第一个完整备份 9 DECLARE @strbackup NVARCHAR(100)10 --改为日期加时间的11 SET @strbackup = 'C:LogChainTest_full1_'12 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ',13 ''), ':', '') + '.bak'14 BACKUP DATABASE LogChainTest TO DISK =@strbackup WITH INIT,CHECKSUM ;15 GO16 17 18 19 --第一个差异备份20 USE LogChainTest21 GO22 CREATE TABLE tt(id int)23 INSERT INTO tt24 SELECT 125 DECLARE @strbackup NVARCHAR(100)26 --改为日期加时间的27 SET @strbackup = 'C:LogChainTest_diff1_'28 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ',29 ''), ':', '') + '.bak'30 BACKUP DATABASE LogChainTest TO DISK = @strbackup WITH INIT, DIFFERENTIAL;31 GO32 33 --第二个差异备份34 USE LogChainTest35 GO36 INSERT INTO tt37 SELECT 938 DECLARE @strbackup NVARCHAR(100)39 --改为日期加时间的40 SET @strbackup = 'C:LogChainTest_diff2_'41 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ',42 ''), ':', '') + '.bak'43 BACKUP DATABASE LogChainTest TO DISK = @strbackup WITH INIT, DIFFERENTIAL;44 GO
完整备份和差异备份可以用下图来理解，少量活动日志放到bak文件里用于保证事务一致性
完整备份差异备份时依然会将last lsn写入bak文件里
还原脚本
1 --还原第一个完整备份 2 RESTORE DATABASE LogChainTest FROM DISK='C:LogChainTest_full1_20131207120946.bak' 3 WITH REPLACE , NORECOVERY 4 GO 5 6 --还原第二个差异备份 7 RESTORE DATABASE LogChainTest FROM DISK='c:LogChainTest_diff2_20131207121428.bak' 8 WITH NORECOVERY 9 GO 10 11 --还原第一个差异备份12 RESTORE DATABASE LogChainTest FROM DISK='c:LogChainTest_diff_20131207120957.bak' 13 WITH RECOVERY14 GO
先还原差备2再还原差备1就报错
1 消息 4305，级别 16，状态 1，第 1 行2 此备份集中的日志开始于 LSN 35000000028200004，该 LSN 太晚，无法应用到数据库。可以还原包含 LSN 35000000024100001 的较早的日志备份。3 消息 3013，级别 16，状态 1，第 1 行4 RESTORE LOG 正在异常终止。
实际上完整和差备都是复制数据和少量活动日志到bak里面，所以还原是没有问题的
但是日备不同，日备需要将完备到第一个日备的log，或者自上一次日备到这次日备的log全部放进去bak文件
因为简单恢复模式是一checkpoint就截断日志，根本无办法保存完整的log，所以是不允许日备的
情况二：
备份日志时指定了NO_LOG 或 WITH TRUNCATE_ONLY(还好在SQL Server 2008中这个选项被取消了)
TRUNCATE_ONLY的意思是只截断日志不备份日志到bak文件里（只能用在backup log语句）
NO_LOG的意思是不备份日志到bak文件里（不备份日志到bak文件里意味着不能backup log，当然也意味着不能截断日志）
我们转到SQLSERVER2005
备份脚本
NO_LOG
1 USE master 2 GO 3 --创建数据库 4 CREATE DATABASE LogChainTest; 5 GO 6 --改为完整恢复模式 7 ALTER DATABASE LogChainTest SET RECOVERY FULL; 8 GO 9 10 --第一个完整备份11 DECLARE @strbackup NVARCHAR(100)12 --改为日期加时间的13 SET @strbackup = 'C:LogChainTest_full1_'14 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ',15 ''), ':', '') + '.bak'16 BACKUP DATABASE LogChainTest TO DISK =@strbackup WITH INIT,NO_LOG ;17 GO18 19 已为数据库 'LogChainTest'，文件 'LogChainTest' (位于文件 1 上)处理了 176 页。20 BACKUP DATABASE...FILE=<name> 成功处理了 176 页，花费 0.025 秒(57.671 MB/秒)。21 22 23 24 --第一个差异备份25 USE LogChainTest26 GO27 CREATE TABLE tt(id INT)28 INSERT INTO tt29 SELECT 130 DECLARE @strbackup NVARCHAR(100)31 --改为日期加时间的32 SET @strbackup = 'C:LogChainTest_diff_'33 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ',34 ''), ':', '') + '.bak'35 BACKUP DATABASE LogChainTest TO DISK = @strbackup WITH INIT, DIFFERENTIAL,NO_LOG;36 GO37 38 39 (1 行受影响)40 已为数据库 'LogChainTest'，文件 'LogChainTest' (位于文件 1 上)处理了 96 页。41 BACKUP DATABASE...FILE=<name> WITH DIFFERENTIAL 成功处理了 96 页，花费 0.016 秒(49.152 MB/秒)。42 43 44 45 --第一个日志备份46 USE LogChainTest47 GO48 INSERT INTO tt49 SELECT 250 DECLARE @strbackup NVARCHAR(100)51 --改为日期加时间的52 SET @strbackup = 'C:LogChainTest_log1_'53 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ',54 ''), ':', '') + '.bak'55 BACKUP LOG LogChainTest TO DISK = @strbackup WITH INIT,NO_LOG;56 GO
备份策略：完备-》差备-》日备
大家可以看到执行日备的时候没有产生bak文件
查看bak文件里的日志
1 SELECT * 2 FROM fn_dump_dblog(NULL, NULL, N'DISK', 1, 3 N'c:LogChainTest_full1_20131207123314.bak', DEFAULT, 4 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, 5 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, 6 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, 7 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, 8 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, 9 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,10 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,11 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,12 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,13 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,14 DEFAULT, DEFAULT)
完备0行
差备0行
其实可以用下图来理解
bak文件里只有数据没有日志，连保证事务一致性的少量的活动日志都没有
备份脚本
TRUNCATE_ONLY
1 USE master 2 GO 3 --创建数据库 4 CREATE DATABASE LogChainTest; 5 GO 6 --改为完整恢复模式 7 ALTER DATABASE LogChainTest SET RECOVERY FULL; 8 GO 9 10 --日备前的事务日志记录11 USE [LogChainTest]12 GO13 SELECT * FROM [sys].[fn_dblog](NULL,NULL) ORDER BY [Begin Time] ASC14 15 16 --日志备份17 USE LogChainTest18 GO19 CREATE TABLE tt(id INT)20 INSERT INTO tt21 SELECT 222 DECLARE @strbackup NVARCHAR(100)23 --改为日期加时间的24 SET @strbackup = 'C:LogChainTest_log1_'25 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ',26 ''), ':', '') + '.bak'27 BACKUP LOG LogChainTest TO DISK = @strbackup WITH TRUNCATE_ONLY;28 GO29 30 --日备后的事务日志记录31 USE [LogChainTest]32 GO33 SELECT * FROM [sys].[fn_dblog](NULL,NULL) ORDER BY [Begin Time] ASC34 35 36 37 SELECT *38 FROM fn_dump_dblog(NULL, NULL, N'DISK', 1,39 N'c:LogChainTest_diff_20131207123347.bak', DEFAULT,40 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,41 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,42 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,43 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,44 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,45 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,46 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,47 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,48 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,49 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,50 DEFAULT, DEFAULT)
备份策略：日备
大家可以看到执行日备的时候没有产生bak文件
查看日志备份前数据库事务日志
查看日志备份前数据库事务日志
其实可以用下图来理解
truncate_only只是截断了日志，没有产生bak文件，更不用说备份日志到bak文件里面了
我们再做一个实验
备份脚本
1 USE master 2 GO 3 --创建数据库 4 CREATE DATABASE LogChainTest; 5 GO 6 --改为完整恢复模式 7 ALTER DATABASE LogChainTest SET RECOVERY FULL; 8 GO 9 10 11 12 --第一个完整备份13 USE master14 GO15 DECLARE @strbackup NVARCHAR(100)16 --改为日期加时间的17 SET @strbackup = 'C:LogChainTest_full1_'18 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ',19 ''), ':', '') + '.bak'20 BACKUP DATABASE LogChainTest TO DISK = @strbackup WITH INIT;21 GO22 23 24 25 --第一个日志备份26 USE LogChainTest27 GO28 CREATE TABLE tt(id INT)29 INSERT INTO tt30 SELECT 131 DECLARE @strbackup NVARCHAR(100)32 --改为日期加时间的33 SET @strbackup = 'C:LogChainTest_log1_'34 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ',35 ''), ':', '') + '.bak'36 BACKUP LOG LogChainTest TO DISK = @strbackup WITH INIT;37 GO38 39 40 --第二个日志备份WITH TRUNCATE_ONLY41 USE LogChainTest42 GO43 INSERT INTO tt44 SELECT 245 DECLARE @strbackup NVARCHAR(100)46 --改为日期加时间的47 SET @strbackup = 'C:LogChainTest_log2_'48 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ',49 ''), ':', '') + '.bak'50 BACKUP LOG LogChainTest TO DISK = @strbackup WITH TRUNCATE_ONLY;51 GO52 53 54 --第三个日志备份55 USE LogChainTest56 GO57 INSERT INTO tt58 SELECT 359 DECLARE @strbackup NVARCHAR(100)60 --改为日期加时间的61 SET @strbackup = 'C:LogChainTest_log3_'62 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ',63 ''), ':', '') + '.bak'64 BACKUP LOG LogChainTest TO DISK = @strbackup WITH INIT;65 GO
当我进行到第三个日志备份的时候就报错了
1 (1 行受影响)2 消息 4214，级别 16，状态 1，第 8 行3 无法执行 BACKUP LOG，因为当前没有数据库备份。4 消息 3013，级别 16，状态 1，第 8 行5 BACKUP LOG 正在异常终止。
可以用下图来理解
（2）从数据库镜像进行恢复这种情况由于没有研究过就不说了
小结:
截断日志跟日志链断裂不是同一样东西！！
截断日志:针对数据库事务日志
日志链断裂:针对bak里的日志
大家不要混淆了
不神秘的事务日志尾部
当你的数据库损坏或置疑，你可以尝试进行尾日志备份
尾日志指的是哪个地方？ 为什麽要进行尾日志备份？
假如有下面的脚本
1 USE master 2 GO 3 --创建数据库 4 CREATE DATABASE LogChainTest; 5 GO 6 --改为完整恢复模式 7 ALTER DATABASE LogChainTest SET RECOVERY FULL; 8 GO 9 10 11 12 --第一个完整备份13 DECLARE @strbackup NVARCHAR(100)14 --改为日期加时间的15 SET @strbackup = 'C:LogChainTest_full1_'16 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ',17 ''), ':', '') + '.bak'18 BACKUP DATABASE LogChainTest TO DISK =@strbackup WITH INIT,CHECKSUM ;19 GO20 21 22 23 --第一个日志备份24 USE LogChainTest25 GO26 CREATE TABLE tt(id INT)27 INSERT INTO tt28 SELECT 129 DECLARE @strbackup NVARCHAR(100)30 --改为日期加时间的31 SET @strbackup = 'C:LogChainTest_log1_'32 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ',33 ''), ':', '') + '.bak'34 BACKUP LOG LogChainTest TO DISK = @strbackup WITH INIT,COPY_ONLY;35 GO36 37 38 39 40 41 --第二个日志备份42 USE LogChainTest43 GO44 INSERT INTO tt45 SELECT 246 DECLARE @strbackup NVARCHAR(100)47 --改为日期加时间的48 SET @strbackup = 'C:LogChainTest_log2_'49 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ',50 ''), ':', '') + '.bak'51 BACKUP LOG LogChainTest TO DISK = @strbackup WITH INIT,COPY_ONLY;52 GO53 54 55 --在第二个日志备份后插入记录到tt表56 INSERT INTO tt57 SELECT 3
在第二个日志备份之后还插入了一条记录到tt表
如果这时候数据库损坏，那么你可以备份事务日志尾部，把最后的事务日志记录（INSERT INTO tt
SELECT 3）放进去bak文件里，然后进行还原数据库
使用下面脚本，备份日志尾部
注意：数据库离线的状态下是不能备份日志尾部的！！
网上很多文章都误导人
由于数据库 'LogChainTest' 离线，无法打开该数据库
1 --备份日志尾部 2 USE master 3 GO 4 DECLARE @strbackup NVARCHAR(100) 5 --改为日期加时间的 6 SET @strbackup = 'C:LogChainTest_log_tail_' 7 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ', 8 ''), ':', '') + '.bak' 9 BACKUP LOG LogChainTest TO DISK = @strbackup WITH INIT,NORECOVERY;10 GO
这时候数据库显示正在还原
还原脚本
1 ------------------------------------------------------------- 2 --还原 3 USE master 4 GO 5 --还原第一个完整备份 6 RESTORE DATABASE LogChainTest FROM DISK='C:LogChainTest_full1_20131207145154.bak' 7 WITH REPLACE ,CHECKSUM, NORECOVERY 8 GO 9 10 --还原第一个日志备份11 RESTORE LOG LogChainTest FROM DISK='c:LogChainTest_log1_20131207145157.bak' 12 WITH NORECOVERY13 GO 14 15 --还原第二个日志备份16 RESTORE LOG LogChainTest FROM DISK='c:LogChainTest_log2_20131207145158.bak' 17 WITH NORECOVERY18 GO 19 20 --还原日志尾部21 RESTORE DATABASE LogChainTest FROM DISK='c:LogChainTest_log_tail_20131207145333.bak' 22 WITH RECOVERY23 GO
数据没有丢失，可以查出最后一条插入到tt表的记录3
回答开头的问题：尾日志指的是哪个地方？ 为什麽要进行尾日志备份？
其实备份日志尾部，大家可以把他作为普通的事务日志备份
如果遇到错误还可以加上CONTINUE_AFTER_ERROR 的backup option
1 --备份日志尾部 2 USE master 3 GO 4 DECLARE @strbackup NVARCHAR(100) 5 --改为日期加时间的 6 SET @strbackup = 'C:LogChainTest_log_tail_' 7 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ', 8 ''), ':', '') + '.bak' 9 BACKUP LOG LogChainTest TO DISK = @strbackup WITH CONTINUE_AFTER_ERROR,NORECOVERY;10 GO
备份记录
实际上这个[msdb].[dbo].[backupset]表的作用只是给你看做了哪些备份
1 SELECT * FROM [msdb].[dbo].[backupset]
使用GUI的时候，我发现了一个问题
当我用上面的备份策略 完备1-》差备-》日备1-》完备2-》日备2
当我完成日备1的时候，还原界面和backupset表的界面如下
当我再进行完备2和日备2的时候，还原界面变成了下面的样子
backupset表依然能显示出备份记录
很多人就认为备份链断裂了，日志链断裂，备份日志链断裂，日志备份链断裂
这个表的记录是删除不了的
1 USE [msdb]2 GO3 DELETE FROM [msdb].[dbo].[backupset]4 TRUNCATE TABLE [msdb].[dbo].[backupset]
1 消息 547，级别 16，状态 0，第 1 行2 DELETE 语句与 REFERENCE 约束"FK__backupfil__backu__473C8FC7"冲突。该冲突发生于数据库"msdb"，表"dbo.backupfilegroup", column 'backup_set_id'。3 语句已终止。4 消息 4712，级别 16，状态 1，第 2 行5 无法截断表 'msdb.dbo.backupset'，因为该表正由 FOREIGN KEY 约束引用。
这个表记录了在备份的时候的lsn号
可以根据paul的文章做一些实验
Debunking a couple of myths around full database backups
我们做一个实验，先做一个完整备份
1 --第一个完整备份2 DECLARE @strbackup NVARCHAR(100)3 --改为日期加时间的4 SET @strbackup = 'C:LogChainTest_full1_'5 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ',6 ''), ':', '') + '.bak'7 BACKUP DATABASE LogChainTest TO DISK =@strbackup WITH INIT,CHECKSUM ;8 GO
backupset表就会产生一条记录
我们将bak文件删除
用GUI来还原数据库
结果：
他们的关系
1 USE [msdb]2 GO3 SELECT * FROM [dbo].[backupfile]4 SELECT * FROM [dbo].[backupfilegroup]5 SELECT * FROM [dbo].[backupset]6 SELECT * FROM [sys].[backup_devices]7 SELECT * FROM [dbo].[backupmediafamily]8 SELECT * FROM [dbo].[backupmediaset]
每次备份的记录都记录在这些表里面，还原的时候SSMS读取这些表的记录，让你勾上几个选项就可以还原数据库了（非常傻瓜）
大家不要以为SQLSERVER在还原数据库的时候依靠[msdb].[dbo].[backupset]表的lsn去对比备份顺序
大家可以试想一下：
你的数据库备份了3次，有3个备份记录保存在backupset表
那么当你把数据库分离附加到别的sql实例的时候，你也可以还原你之前的备份
为什麽呢？？
因为还原的时候只去数据库的事务日志去对比last lsn，是不依靠外部的其他的数据的而且也不需要依靠
如果还不明白的话，大家再看一下我上面贴出来的图片吧o(∩_∩)o
总结
一直以来本人对SQLSERVER的备份还原机制都不是很熟悉，通过跟宋沄剑的讨论让本人重新认识SQLSERVER的备份、还原
失眠了两晚，今晚可以吃一个好的水饺了
相关内容：
http://www.sqlskills.com/blogs/paul/more-on-how-much-transaction-log-a-full-backup-includes/
http://www.sqlskills.com/blogs/paul/debunking-a-couple-of-myths-around-full-database-backups/
浅谈SQL Server中的事务日志(一)—-事务日志的物理和逻辑构架
浅谈SQL Server中的事务日志(五)—-日志在高可用和灾难恢复中的作用
上面的结论都经过我测试，希望大家可以指出本人的错处o(∩_∩)o
您们也可以动手测试一下我说的是不是真的o(∩_∩)o
如有不对的地方，欢迎大家拍砖o(∩_∩)o
2013-12-7 补充：
大家不要误解了，数据库事务日志截断的意思不是说把不活动日志部分删除了，而是把这些日志清空了
等待重用，除非你收缩事务日志，不然这些日志空间（VLF）只会等待重用
2013-12-8 补充：
还原日志备份的时候使用restore log 或restore database都是一样的
而还原差异备份的时候使用restore log就会报错
1 USE master 2 GO 3 --创建数据库 4 CREATE DATABASE LogChainTest; 5 GO 6 --改为完整恢复模式 7 ALTER DATABASE LogChainTest SET RECOVERY FULL; 8 GO 9 10 11 --------------------------------------------------------------------12 --备份13 --第一个完整备份14 USE master15 GO16 DECLARE @strbackup NVARCHAR(100)17 --改为日期加时间的18 SET @strbackup = 'C:LogChainTest_full1_'19 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ',20 ''), ':', '') + '.bak'21 BACKUP DATABASE LogChainTest TO DISK = @strbackup WITH INIT;22 GO23 24 25 26 --第一个日志备份27 USE LogChainTest28 GO29 CREATE TABLE tt(id INT)30 INSERT INTO tt31 SELECT 132 DECLARE @strbackup NVARCHAR(100)33 --改为日期加时间的34 SET @strbackup = 'C:LogChainTest_log1_'35 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ',36 ''), ':', '') + '.bak'37 BACKUP LOG LogChainTest TO DISK = @strbackup WITH INIT;38 GO39 40 41 42 --第一个差异备份43 USE LogChainTest44 GO45 INSERT INTO tt46 SELECT 247 DECLARE @strbackup NVARCHAR(100)48 --改为日期加时间的49 SET @strbackup = 'C:LogChainTest_diff1_'50 + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ',51 ''), ':', '') + '.bak'52 BACKUP DATABASE LogChainTest TO DISK = @strbackup WITH INIT, DIFFERENTIAL;53 GO54 ------------------------------------------------------------------------55 56 57 --------------------------------------------------------------------------58 --还原59 60 61 62 USE master63 GO64 --只有完备备份还原才可以移动数据库文件65 RESTORE DATABASE LogChainTest FROM DISK='C:LogChainTest_full1_20131208100145.bak' 66 WITH MOVE 'LogChainTest' TO 'E:LogChainTest.mdf', 67 MOVE 'LogChainTest_log' TO 'E:LogChainTest_log.ldf',68 NORECOVERY ,REPLACE69 GO70 71 72 RESTORE LOG LogChainTest FROM DISK='c:LogChainTest_log1_20131208100151.bak' 73 WITH MOVE 'LogChainTest' TO 'E:LogChainTest.mdf', 74 MOVE 'LogChainTest_log' TO 'E:LogChainTest_log.ldf',75 NORECOVERY76 GO 77 -------------------------------------------------78 RESTORE DATABASE LogChainTest FROM DISK='c:LogChainTest_log1_20131208100151.bak' 79 WITH MOVE 'LogChainTest' TO 'E:LogChainTest.mdf', 80 MOVE 'LogChainTest_log' TO 'E:LogChainTest_log.ldf',81 NORECOVERY82 GO 83 84 85 RESTORE LOG LogChainTest FROM DISK='c:LogChainTest_diff1_20131208100251.bak' 86 WITH MOVE 'LogChainTest' TO 'E:LogChainTest.mdf', 87 MOVE 'LogChainTest_log' TO 'E:LogChainTest_log.ldf',88 RECOVERY89 GO90 ----------------------------------------------------------91 RESTORE DATABASE LogChainTest FROM DISK='c:LogChainTest_diff1_20131208100251.bak' 92 WITH MOVE 'LogChainTest' TO 'E:LogChainTest.mdf', 93 MOVE 'LogChainTest_log' TO 'E:LogChainTest_log.ldf',94 RECOVERY95 GO96 97 USE [LogChainTest]98 GO99 SELECT * FROM [dbo].[tt]