发现完整备份之后事务日志比之前少了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.
文章地址：
http://www.sqlskills.com/blogs/paul/misconceptions-around-the-log-and-log-backups-how-to-convince-yourself/
问题：为什麽bak文件里的日志的最后的三条记录会是
LOP_BEGIN_CKPT
LOP_XACT_CKPT
LOP_END_CKPT
我们用下图来表示吧
这里大家可以看一下宋沄剑的文章：再谈SQL Server中日志的的作用
?将CheckPoint标记写入日志（标记中包含当前数据库中活动的事务信息），并将Log Block写入持久化存储
我在开头说过事务日志中会放进去bak文件里，但是并不是整个事务日志文件里的日志记录全部放进去
而是把（1）已经checkpoint了的 （2）LAZY WRITTER?? （3）EAGER WRITTER
还是看宋沄剑的文章吧，这麽复杂的过程我就不概括了： 还有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 时，数据库就处于日志截断模式：数据库使用的是简单恢复模式，并且在执行上一条引用数据库的 DATABASE 语句后，发生下列事件之一：
9
10 在数据库中执行一项最小日志记录大容量复制操作或一条最条小日志记录的 WRITETEXT 语句。
11
12 执行一个在数据库中添加或删除文件的 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/p3315041.html
23 5.将恢复间隔设置为1分钟，意味着每1分钟会对所有的数据库做一次CheckPoint
24
25 错误。将恢复间隔设置为1分钟不能想成建立一个Agent，每分钟写一个CheckPoint命令，这是两码事。这只是意味着每分钟去检查一次是否需要做CheckPoint，如果期间积累的日志量足够，才会对积累足够日志量的数据库去做CheckPoint。即使中间积累了巨量的日志，不到1分钟也不会做CheckPoint。
那么大家可以将bak文件里的事务日志当作为数据库事务日志
备份脚本
3 4 5 6 7 8 10
12
14
15 16 17 18 19 20 22 23
25
26
27
28 第一个差异备份
29 USE LogChainTest
30 31 TABLE tt(id INT)
32 INSERT INTO tt
33 1
34 35 36 C:LogChainTest_diff_37 38 39 = 40 41
42
43
44 第一个日志备份
45 46 47 48 2
49 50 51 C:LogChainTest_log1_52 53 54 BACKUP LOG LogChainTest WITH INIT;
55 56
57
58
59
60 第二个完整备份
61 62 63 64 65 C:LogChainTest_full2_66 67 68 69 70
71
72 第二个日志备份
73 74 75 76 3
77 78 79 C:LogChainTest_log2_80 81 82 83 备份策略：完整备份1-》差异备份-》日志备份1-》完整备份2-》日志备份2
还原脚本
差异备份和日志备份1打乱
还原第一个完整备份
RESTORE FROM DISK=C:LogChainTest_full1_20131206230857.bak'
WITH REPLACE,CHECKSUM,NORECOVERY
8
9 还原第一个日志备份
10 RESTORE c:LogChainTest_diff_20131206230920.bak11 WITH NORECOVERY
12 GO
14 还原差异备份
c:LogChainTest_diff_20131205222718.bakWITH NORECOVERY
18
19 消息 3136，级别 16，状态 3，第 1 行
20 无法还原此差异备份，因为该数据库尚未还原到正确的早期状态。
21 消息 3013，级别 1，第 DATABASE 正在异常终止。
27 还原第二个日志备份，没有报错
C:LogChainTest_log2_20131206230927.bakWITH RECOVERY
31
32
33
34
可以查询出id列有三行记录
37 38 dbott]
上面的还原脚本，我先还原日志备份1，再还原差异备份结果就报错了
1 消息 2 无法还原此差异备份，因为该数据库尚未还原到正确的早期状态。
3 消息 DATABASE 正在异常终止。
还有，为什麽不用还原完整备份2数据也没有丢失？？
我们每次备份的时候，无论是完备、差备、日备都会把日志拷贝到bak文件里
而拷贝的时候会有一个last lsn确保日志顺序
当我先还原日志备份1，然后还原差异备份的时候因为last lsn的顺序不对所以就报错了
为什麽不用还原完整备份2数据也没有丢失？？
这里先说一下完备、差备、日备的大概方式
完备：复制数据和少量的log到bak
差备：复制有差异的数据和少量的log到bak
日备：不复制数据，如果是第一次日备，会把所有的log复制到bak，如果是第二次日备，会把自上一次日备到这次日备的log复制到bak
paul的文章里有解释：
http://www.sqlskills.com/blogs/paul/misconceptions-around-the-log-and-log-backups-how-to-convince-yourself/
A log backup is *ALL* the log generated since the last log backup
我们没有还原完整备份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看有没有问题
3 UNION ALL
4
66 67 69 70 71 73
74
5
80 81 83 84 85 86
C:LogChainTest_full1_20131207102535.bak还原第二个日志备份
C:LogChainTest_log2_20131207102602.bakGO
插入的数据太少，日志太少，搞得文件的size不那么明显
结果报错
4305，级别 2 行
2 此备份集中的日志开始于 LSN 35000000017200001，该 LSN 太晚，无法应用到数据库。可以还原包含 LSN 35000000008600001 的较早的日志备份。
LOG 正在异常终止。
因为没有还原日志备份1，缺少了完备1到日备1之间的日志，所以就报错了
我们使用下面的脚本来进行还原，只还原完备1，日备1，日备2
C:LogChainTest_log1_20131207102542.bak13 19 20 FROM tt
这次成功了，数据都没有丢失，那么说明我丢失了差异备份、完整备份2也没有关系
如果我丢失了日备1、差备、完备2，只有完备1和日备2，那么这个时候你只能祈祷了，你只能还原完备1
差备、日备1、完备2、日备2的数据都已经丢失
BAK文件中日志数量的多少
我刚才说
完备：复制数据和少量的log到bak
差备：复制有差异的数据和少量的log到bak
日备：不复制数据，如果是第一次日备，会把所有的log复制到bak，如果是第二次日备，会把自上一次日备到这次日备的log复制到bak
我怎麽看出来的？
我们看一下每次备份完毕后，bak文件里面的日志数量
5 Nc:LogChainTest_full1_20131207102535.bak15 16 完备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
1 2 3 4 5 6 7 8 9
10
11
12
13
14
15 16 17 18 19 20 21 22 23
24
25
26
27
28 29 30 31 32 33 34 35 36 37 38 39 40 41
42
43
44 45 46 47 48 49 50 51 52 53 54 55 56
57 58 59 * ASC
60
61
62
63 64 65 66 67 68 69 70 71 72 73 74 75 76
77
78 79 80 81 82 83 84 85 86 87 88 89 90
91
92
93
94
95
96 97 98 99 100 N101 102 103 104 105 106 107 108 109 110 111 我们看一下第一个日志备份和第二个日志备份之后，数据库事务日志和bak文件里面的日志数量
日备1 数据库日志
日备1 bak文件日志
日备2 数据库日志
日备2 bak文件日志
备份脚本?COPY_ONLY
57 58 59 60
61
62
70 72 73 76
77
84 86 87 88 89 90
91 92 93 我们看一下第一个日志备份和第二个日志备份之后，数据库事务日志和bak文件里面的日志数量
日备1 数据库日志
大家可以看一下这篇帖子
完整备份能截断日志吗？
差异备份的作用
既然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语句
]()
fn_dump_dblog]()
在完整备份、差异备份、日志备份测试一下在哪种备份类型下日志会被截断，截断的意思（数据库事务日志的记录数比bak文件里的日志记录数少）
就像我在开头做的那个实验一样
GUI界面下，默认就是截断事务日志，很多人都以为截断事务日志要加XX backup option才可以截断
?
日志链断裂的情况
paul的文章说了? 下面这几种操作都有可能引起日志链断裂
（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文件）
?
我们使用下面的脚本进行日志备份就会报错
SET RECOVERY SIMPLE;
C:LogChainTest_log_11 12 GO
4208，级别 6 行
2 当恢复模式为 SIMPLE 时，不允许使用 LOG 语句。请使用 DATABASE 或用 DATABASE 更改恢复模式。
但是完整备份和差异备份则不受影响
12 13 16
int)
23 24 25 26 C:LogChainTest_diff1_28 29 第二个差异备份
9
C:LogChainTest_diff2_41 42 43 完整备份和差异备份可以用下图来理解，少量活动日志放到bak文件里用于保证事务一致性
完整备份差异备份时依然会将last lsn写入bak文件里
C:LogChainTest_full1_20131207120946.bak还原第二个差异备份
c:LogChainTest_diff2_20131207121428.bak还原第一个差异备份
c:LogChainTest_diff_20131207120957.bak 先还原差备2再还原差备1就报错
35000000028200004，该 LSN 太晚，无法应用到数据库。可以还原包含 LSN 35000000024100001 的较早的日志备份。
实际上完整和差备都是复制数据和少量活动日志到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
14 15 19 已为数据库 '，文件 ' (位于文件 1 上)处理了 176 页。
DATABASE...FILE=<name> 成功处理了 176 页，花费 0.025 秒(57.671 MB/秒)。
21
22
33 34 37
38
39 (1 行受影响)
40 已为数据库 96 页。
41 > WITH DIFFERENTIAL 成功处理了 96 页，花费 0.016 秒(49.152 MB44
52 53 54 56 备份策略：完备-》差备-》日备
大家可以看到执行日备的时候没有产生bak文件
查看bak文件里的日志
c:LogChainTest_full1_20131207123314.bak
完备0行
差备0行
其实可以用下图来理解
bak文件里只有数据没有日志，连保证事务一致性的少量的活动日志都没有
TRUNCATE_ONLY
日备前的事务日志记录
日志备份
25 26 WITH TRUNCATE_ONLY;
29
日备后的事务日志记录
35
36
39 Nc:LogChainTest_diff_20131207123347.bak40 41 42 43 44 45 46 47 48 49 50 备份策略：日备
查看日志备份前数据库事务日志
truncate_only只是截断了日志，没有产生bak文件，更不用说备份日志到bak文件里面了
我们再做一个实验
18 19 34 35 39
第二个日志备份WITH TRUNCATE_ONLY
42 48 49 52
53
第三个日志备份
C:LogChainTest_log3_62 63
当我进行到第三个日志备份的时候就报错了
1 (2 消息 4214，级别 8 行
3 无法执行 LOG，因为当前没有数据库备份。
4 消息 可以用下图来理解
（2）从数据库镜像进行恢复这种情况由于没有研究过就不说了
小结:
截断日志跟日志链断裂不是同一样东西！！
截断日志:针对数据库事务日志
日志链断裂:针对bak里的日志
大家不要混淆了
不神秘的事务日志尾部
当你的数据库损坏或置疑，你可以尝试进行尾日志备份
尾日志指的是哪个地方？ 为什麽要进行尾日志备份？
假如有下面的脚本
16 17 20
32 33 40
49 50 54
在第二个日志备份后插入记录到tt表
3
在第二个日志备份之后还插入了一条记录到tt表
如果这时候数据库损坏，那么你可以备份事务日志尾部，把最后的事务日志记录（INSERT INTO tt
SELECT 3）放进去bak文件里，然后进行还原数据库
使用下面脚本，备份日志尾部
注意：数据库离线的状态下是不能备份日志尾部的！！
网上很多文章都误导人
由于数据库 'LogChainTest' 离线，无法打开该数据库
备份日志尾部
C:LogChainTest_log_tail_ 7 8 这时候数据库显示正在还原
还原脚本
-----------------------------------------------------------
还原
C:LogChainTest_full1_20131207145154.bakc:LogChainTest_log1_20131207145157.bakc:LogChainTest_log2_20131207145158.bak还原日志尾部
c:LogChainTest_log_tail_20131207145333.bak 数据没有丢失，可以查出最后一条插入到tt表的记录3
回答开头的问题：尾日志指的是哪个地方？ 为什麽要进行尾日志备份？
其实备份日志尾部，大家可以把他作为普通的事务日志备份
如果遇到错误还可以加上CONTINUE_AFTER_ERROR 的backup option
WITH CONTINUE_AFTER_ERROR,0)">备份记录
实际上这个[msdb].[dbo].[backupset]表的作用只是给你看做了哪些备份
msdb
backupset]
使用GUI的时候，我发现了一个问题
当我用上面的备份策略 完备1-》差备-》日备1-》完备2-》日备2
当我完成日备1的时候，还原界面和backupset表的界面如下
当我再进行完备2和日备2的时候，还原界面变成了下面的样子
backupset表依然能显示出备份记录
很多人就认为备份链断裂了，日志链断裂，备份日志链断裂，日志备份链断裂
这个表的记录是删除不了的
DELETE TRUNCATE TABLE ]
547，级别 0，第 DELETE 语句与 REFERENCE 约束"FK__backupfil__backu__473C8FC7"冲突。该冲突发生于数据库"msdb"，表"dbo.backupfilegroup",255)">column
backup_set_id'。
3 语句已终止。
4712，级别 5 无法截断表 msdb.dbo.backupset'，因为该表正由 FOREIGN KEY 约束引用。
这个表记录了在备份的时候的lsn号
可以根据paul的文章做一些实验
Debunking a couple of myths around full database backups
?
我们做一个实验，先做一个完整备份
backupset表就会产生一条记录
我们将bak文件删除
用GUI来还原数据库
?结果：
他们的关系
backupfilebackupfilegroupbackup_devicesbackupmediafamilybackupmediaset
每次备份的记录都记录在这些表里面，还原的时候SSMS读取这些表的记录，让你勾上几个选项就可以还原数据库了（非常傻瓜）
大家不要以为SQLSERVER在还原数据库的时候依靠[msdb].[dbo].[backupset]表的lsn去对比备份顺序
大家可以试想一下：
你的数据库备份了3次，有3个备份记录保存在backupset表
那么当你把数据库分离附加到别的sql实例的时候，你也可以还原你之前的备份
为什麽呢？？
因为还原的时候只去数据库的事务日志去对比last lsn，是不依靠外部的其他的数据的而且也不需要依靠
如果还不明白的话，大家再看一下我上面贴出来的图片吧o(∩_∩)o
总结
一直以来本人对SQLSERVER的备份还原机制都不是很熟悉，通过跟宋沄剑的讨论让本人重新认识SQLSERVER的备份、还原
失眠了两晚，今晚可以吃一个好的水饺了
相关内容：
http://social.technet.microsoft.com/Forums/zh-CN/7e531652-1f00-441b-ae20-871b3e9573c8/sql-server-2005?forum=sqlserverzhchs
http://www.sqlskills.com/blogs/paul/misconceptions-around-the-log-and-log-backups-how-to-convince-yourself/
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就会报错
------------------------------------------------------------------
备份
35 36 50 51 53 ----------------------------------------------------------------------
55
只有完备备份还原才可以移动数据库文件
C:LogChainTest_full1_20131208100145.bak66 WITH MOVE ' TO E:LogChainTest.mdf67 MOVE LogChainTest_logE:LogChainTest_log.ldf68 NORECOVERY,255)">REPLACE
c:LogChainTest_log1_20131208100151.bak73 74 MOVE 75 NORECOVERY
-----------------------------------------------
79 80 MOVE 81 NORECOVERY
83
84
c:LogChainTest_diff1_20131208100251.bak86 87 MOVE 88 RECOVERY
90 --------------------------------------------------------
92 93 MOVE 94 RECOVERY
95 96
97 98 99 源文:?http://www.cnblogs.com/lyhabc/p/3460272.html
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
