sql server 查看操作日志日志 sqlserver怎么查看日志

 您真的理解了SQL Server的日志链了吗？

 

一直以来，SQLSERVER提供了一个非常好的管理工具：SSMS

又因为这个管理工具太好了，所有操作的简单化，以至于使我们中毒太深

对于SQLSERVER内部的一些概念搞得不清不楚

比如这些概念：日志备份链，备份日志链，日志链，备份链，备份集

 

大部分都是由于SSMS的界面所导致，有时候有些问题做一下实验就可以验证了，偏偏我们信赖了GUI工具

 

阅读下文之前大家可以先看一下宋沄剑的文章

SQL Server CheckPoint的几个误区

再谈SQL Server中日志的的作用

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 master
2 GO
3 --创建数据库
4 CREATE DATABASE LogChainTest;
5 GO
6 --改为完整恢复模式
7 ALTER DATABASE LogChainTest SET RECOVERY FULL;
8 GO

View Code

查看当前的事务日志

1 USE [LogChainTest]
2 GO
3 SELECT * FROM [sys].[fn_dblog](NULL,NULL) ORDER BY [Begin Time] ASC

View Code

进行完整备份

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

View Code

查看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)

View Code

我们再查看此时的数据库事务日志

1 USE [LogChainTest]
2 GO
3 SELECT * FROM [sys].[fn_dblog](NULL,NULL) ORDER BY [Begin Time] ASC

View Code

发现完整备份之后事务日志比之前少了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

还是看宋沄剑的文章吧，这麽复杂的过程我就不概括了：再谈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.html
23 5.将恢复间隔设置为1分钟，意味着每1分钟会对所有的数据库做一次CheckPoint
24 
25     错误。将恢复间隔设置为1分钟不能想成建立一个Agent，每分钟写一个CheckPoint命令，这是两码事。这只是意味着每分钟去检查一次是否需要做CheckPoint，如果期间积累的日志量足够，才会对积累足够日志量的数据库去做CheckPoint。即使中间积累了巨量的日志，不到1分钟也不会做CheckPoint。

View Code

 

 

那么大家可以将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 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;
55 GO
56 
57 
58 
59 
60 --第二个完整备份
61 USE master
62 GO
63 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 GO
70 
71 
72 --第二个日志备份
73 USE LogChainTest
74 GO
75 INSERT INTO tt
76 SELECT 3
77 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

View Code

备份策略：完整备份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  NORECOVERY
12 GO 
13 
14 --还原差异备份
15 RESTORE DATABASE LogChainTest FROM DISK='c:\LogChainTest_diff_20131205222718.bak' 
16 WITH NORECOVERY
17 GO
18 
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 RECOVERY
30 GO 
31 
32 
33 
34 
35 --可以查询出id列有三行记录
36 USE [LogChainTest]
37 GO
38 SELECT * FROM [dbo].[tt]

View Code

上面的还原脚本，我先还原日志备份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的文章里有解释：

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

备份策略：完整备份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 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;
55 GO
56 
57 
58 
59 
60 --第二个完整备份
61 USE LogChainTest
62 GO
63 INSERT INTO tt
64 SELECT 3 UNION ALL
65 SELECT 4
66 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 GO
73 
74 
75 --第二个日志备份
76 USE LogChainTest
77 GO
78 INSERT INTO tt
79 SELECT 5
80 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

View Code

还原脚本

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 RECOVERY
12 GO

View Code

插入的数据太少，日志太少，搞得文件的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 NORECOVERY
11 GO 
12 
13 --还原第二个日志备份
14 RESTORE LOG LogChainTest FROM DISK='C:\LogChainTest_log2_20131207102602.bak' 
15 WITH RECOVERY
16 GO 
17 
18 USE [LogChainTest]
19 GO
20 SELECT * FROM tt

View Code

这次成功了，数据都没有丢失，那么说明我丢失了差异备份、完整备份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)

View Code

完备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)

View Code

我们看一下第一个日志备份和第二个日志备份之后，数据库事务日志和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 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,COPY_ONLY;
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,COPY_ONLY;
89 GO
90 
91 USE [LogChainTest]
92 GO
93 SELECT *  FROM [sys].[fn_dblog](NULL,NULL) ORDER BY [Begin Time] ASC

View Code

我们看一下第一个日志备份和第二个日志备份之后，数据库事务日志和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才可以截断

 

如何查看last_log_backup_lsn这个值

select last_log_backup_lsn
from sys.database_recovery_status WHERE [database_id]=DB_ID('test')

last_log_backup_lsn这个值在boot page的last_log_backup_lsn项里保存，表示对数据库执行最后一次事务日志备份中的最大LSN号，也可以说是下一次事务日志备份的开始LSN

 

实验

USE [test]
select last_log_backup_lsn
from sys.database_recovery_status WHERE [database_id]=DB_ID('test')


BACKUP DATABASE [test] TO DISK ='D:\DBBackup\testfull.bak'
USE [test]
select last_log_backup_lsn
from sys.database_recovery_status WHERE [database_id]=DB_ID('test')
--34000000031500001




BACKUP LOG [test] TO DISK ='D:\DBBackup\testlog1.bak'
USE [test]
select last_log_backup_lsn
from sys.database_recovery_status WHERE [database_id]=DB_ID('test')
--34000000032300001




BACKUP LOG [test] TO DISK ='D:\DBBackup\testlog2.bak'
USE [test]
select last_log_backup_lsn
from sys.database_recovery_status WHERE [database_id]=DB_ID('test')

--34000000032800001

USE [master]
RESTORE DATABASE [test] 
FROM  DISK = N'D:\DBBackup\testfull.bak' WITH  FILE = 1, 
MOVE N'test' TO N'D:\MSSQL\test.mdf',  
MOVE N'test_log' TO N'D:\MSSQL\test_log.ldf',  
NOUNLOAD,NORECOVERY , STATS = 5

GO

USE [master]
RESTORE DATABASE [test] 
FROM  DISK = N'D:\DBBackup\testlog2.bak' WITH  FILE = 1, 
NOUNLOAD,NORECOVERY , STATS = 5

GO
消息 4305，级别 16，状态 1，第 2 行
此备份集中的日志开始于 LSN 34000000032300001，该 LSN 太晚，无法应用到数据库。可以还原包含 LSN 34000000031500001 的较早的日志备份。
消息 3013，级别 16，状态 1，第 2 行
RESTORE DATABASE 正在异常终止。

可以看到，还原日志备份的时候是读取boot page的 last_log_backup_lsn的值来判断日志序列，此处应该先还原LSN 34000000032300001的日志备份

---

日志链断裂的情况

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

View Code

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 GO
16 
17 
18 
19 --第一个差异备份
20 USE LogChainTest
21 GO
22 CREATE TABLE tt(id int)
23 INSERT INTO tt
24 SELECT 1
25 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 GO
32 
33 --第二个差异备份
34 USE LogChainTest
35 GO
36 INSERT INTO tt
37 SELECT 9
38 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

View Code

完整备份和差异备份可以用下图来理解，少量活动日志放到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 RECOVERY
14 GO

View Code

先还原差备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 GO
18 
19 已为数据库 'LogChainTest'，文件 'LogChainTest' (位于文件 1 上)处理了 176 页。
20 BACKUP DATABASE...FILE=<name> 成功处理了 176 页，花费 0.025 秒(57.671 MB/秒)。
21 
22 
23 
24 --第一个差异备份
25 USE LogChainTest
26 GO
27 CREATE TABLE tt(id INT)
28 INSERT INTO tt
29 SELECT 1
30 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 GO
37 
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 LogChainTest
47 GO
48 INSERT INTO tt
49 SELECT 2
50 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

View Code

备份策略：完备-》差备-》日备

大家可以看到执行日备的时候没有产生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)

View Code

完备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 GO
13 SELECT *  FROM [sys].[fn_dblog](NULL,NULL) ORDER BY [Begin Time] ASC
14 
15 
16 --日志备份
17 USE LogChainTest
18 GO
19 CREATE TABLE tt(id INT)
20 INSERT INTO tt
21 SELECT 2
22 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 GO
29 
30 --日备后的事务日志记录
31 USE [LogChainTest]
32 GO
33 SELECT *  FROM [sys].[fn_dblog](NULL,NULL) ORDER BY [Begin Time] ASC
34 
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)

View Code

备份策略：日备

大家可以看到执行日备的时候没有产生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 master
14 GO
15 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 GO
22 
23 
24 
25 --第一个日志备份
26 USE LogChainTest
27 GO
28 CREATE TABLE tt(id INT)
29 INSERT INTO tt
30 SELECT 1
31 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 GO
38 
39 
40 --第二个日志备份WITH TRUNCATE_ONLY
41 USE LogChainTest
42 GO
43 INSERT INTO tt
44 SELECT 2
45 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 GO
52 
53 
54 --第三个日志备份
55 USE LogChainTest
56 GO
57 INSERT INTO tt
58 SELECT 3
59 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

View Code

当我进行到第三个日志备份的时候就报错了

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 GO
20 
21 
22 
23 --第一个日志备份
24 USE LogChainTest
25 GO
26 CREATE TABLE tt(id INT)
27 INSERT INTO tt
28 SELECT 1
29 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 GO
36 
37 
38 
39 
40 
41 --第二个日志备份
42 USE LogChainTest
43 GO
44 INSERT INTO tt
45 SELECT 2
46 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 GO
53 
54 
55 --在第二个日志备份后插入记录到tt表
56 INSERT INTO tt
57 SELECT 3

View Code

在第二个日志备份之后还插入了一条记录到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

View Code

这时候数据库显示正在还原

还原脚本

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  NORECOVERY
13 GO 
14 
15 --还原第二个日志备份
16 RESTORE LOG LogChainTest FROM DISK='c:\LogChainTest_log2_20131207145158.bak' 
17 WITH  NORECOVERY
18 GO 
19 
20 --还原日志尾部
21 RESTORE DATABASE LogChainTest FROM DISK='c:\LogChainTest_log_tail_20131207145333.bak' 
22 WITH RECOVERY
23 GO

View Code 数据没有丢失，可以查出最后一条插入到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

View Code

 

---

备份记录

实际上这个[msdb].[dbo].[backupset]表的作用只是给你看做了哪些备份

1 SELECT * FROM [msdb].[dbo].[backupset]

 

使用GUI的时候，我发现了一个问题

当我用上面的备份策略 完备1-》差备-》日备1-》完备2-》日备2

当我完成日备1的时候，还原界面和backupset表的界面如下

当我再进行完备2和日备2的时候，还原界面变成了下面的样子

backupset表依然能显示出备份记录

很多人就认为备份链断裂了，日志链断裂，备份日志链断裂，日志备份链断裂

 

这个表的记录是删除不了的

1 USE [msdb]
2 GO
3 DELETE FROM [msdb].[dbo].[backupset]
4 TRUNCATE TABLE [msdb].[dbo].[backupset]

View Code

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

View Code backupset表就会产生一条记录

我们将bak文件删除

用GUI来还原数据库

 结果：

他们的关系

1 USE [msdb]
2 GO
3 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://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就会报错

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 master
15 GO
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;
22 GO
23 
24 
25 
26 --第一个日志备份
27 USE LogChainTest
28 GO
29 CREATE TABLE tt(id INT)
30 INSERT INTO tt
31 SELECT 1
32 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 GO
39 
40 
41 
42 --第一个差异备份
43 USE LogChainTest
44 GO
45 INSERT INTO tt
46 SELECT 2
47 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 GO
54 ------------------------------------------------------------------------
55 
56 
57 --------------------------------------------------------------------------
58 --还原
59 
60 
61 
62 USE master
63 GO
64 --只有完备备份还原才可以移动数据库文件
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 ,REPLACE
69 GO
70 
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   NORECOVERY
76 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   NORECOVERY
82 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   RECOVERY
89 GO
90 ----------------------------------------------------------
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   RECOVERY
95 GO
96 
97 USE [LogChainTest]
98 GO
99 SELECT * FROM [dbo].[tt]

View Code

 

2016-8-2 补充：

MinLSN是当前所有活动事务的开始LSN和checkpoint的开始LSN中的较小者

MinLSN的作用是记录当前数据库需要恢复时，可能回滚的上限

实例恢复和介质恢复

实例恢复和fn_dblog从minlsn开始显示

bootpage-》数据库最后一个checkpoint的lsn-》ldf里面定位到数据库最后一个checkpoint开始的那条日志记录-》读取minlsn

页头最后一次修改LSN（m_lsn）和dbi_checkptLSN进行对比

《SQL Server2008数据库技术内幕》