当前位置: 澳门新濠3559 > 数据库 > 正文

  对于一个数据库小白来说,Server 2008下设计合

时间:2019-10-12 05:06来源:数据库
如果你曾经有那么一个不经意的心跳来自于数据库数据损坏:错误的新增、更新、删除 。那么下面的方案一定能抚平你的创伤!  您真的理解了SQLSERVER的日志链了吗? 先感谢 宋沄剑

如果你曾经有那么一个不经意的心跳来自于数据库数据损坏:错误的新增、更新、删除 。那么下面的方案一定能抚平你的创伤!

 您真的理解了SQLSERVER的日志链了吗?

先感谢宋沄剑给本人指点迷津,还有郭忠辉童鞋今天在QQ群里抛出的问题

这个问题跟宋沄剑讨论了三天,再次感谢宋沄剑

 

一直以来,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文件理解成一个压缩包,完整备份差异备份的时候会把数据和日志一起带进压缩包,

日志备份的时候只会把日志带进压缩包

图片 1

我们先从一个实验开始吧

测试环境:SQLSERVER2012 开发版

脚本

为了不产生额外的日志,所以脚本里面没有select into语句,本来想select into进去临时表再对临时表进行排序

但是因为select into会产生额外的日志,只有直接对fn_dblog进行排序了

创建数据库

图片 2图片 3

1 USE master
2 GO
3 --创建数据库
4 CREATE DATABASE LogChainTest;
5 GO
6 --改为完整恢复模式
7 ALTER DATABASE LogChainTest SET RECOVERY FULL;
8 GO

View Code

查看当前的事务日志

图片 4图片 5

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

View Code

图片 6

进行完整备份

图片 7图片 8

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文件中的事务日志

图片 9图片 10

 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

图片 11

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

图片 12图片 13

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

View Code

图片 14

发现完整备份之后事务日志比之前少了69-10=59行

我们发现bak文件中只记录AllocUnitId,而不记录表名,可能因为bak文件里的日志给SQLSERVER还原用的

而不是给用户查看事务日志用的,所以SQLSERVER干脆不记录表名了,以节省备份时间

图片 15

图片 16

看到这里大家会有问题了,为什麽日志会截断了?完整备份之后事务日志比之前少了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

我们用下图来表示吧

图片 17

 

这里大家可以看一下宋沄剑的文章:再谈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,以便在备份中包含对数据库页的全部更改。

我摘抄了网上的一些资料

图片 18图片 19

 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

 

 

图片 20

那么大家可以将bak文件里的事务日志当作为数据库事务日志

 

备份脚本

图片 21图片 22

 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

还原脚本

图片 23图片 24

 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确保日志顺序

图片 25

当我先还原日志备份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看有没有问题

备份脚本

图片 26图片 27

 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

还原脚本

图片 28图片 29

 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

图片 30

图片 31

插入的数据太少,日志太少,搞得文件的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

图片 32图片 33

 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

图片 34

这次成功了,数据都没有丢失,那么说明我丢失了差异备份、完整备份2也没有关系

如果我丢失了日备1、差备、完备2,只有完备1和日备2,那么这个时候你只能祈祷了,你只能还原完备1

差备、日备1、完备2、日备2的数据都已经丢失


BAK文件中日志数量的多少

我刚才说

完备:复制数据和少量的log到bak

差备:复制有差异的数据和少量的log到bak

日备:不复制数据,如果是第一次日备,会把所有的log复制到bak,如果是第二次日备,会把自上一次日备到这次日备的log复制到bak

我怎麽看出来的?

测试:

我们看一下每次备份完毕后,bak文件里面的日志数量

图片 35图片 36

 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

图片 37

差备

图片 38

日备1

图片 39

完备2

图片 40

日备2

图片 41

在完备2的时候bak中的日志只有44行,说明完整备份只存储一些必要的日志,不是所有日志都存储

完备存储这些日志的作用是在还原的时候根据这些log去redo/undo 保证事务一致性,所以只会写入少量日志

因为完备和差备都是复制数据,所以就没有必要像日备那样全部事务日志都复制到bak里面

而日备2为什麽只有73行记录,因为在日备1的时候SQLSERVER已经截断了事务日志,日备2的日志就像我前面说的

如果是第二次日备,会把自上一次日备到这次日备的log复制到bak

 

如果我们不想在backup log 的时候截断事务日志,可以使用NO_TRUNCATECOPY_ONLY这两个backup option

备份脚本 NO_TRUNCATE

图片 42图片 43

  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 数据库日志

图片 44

日备1 bak文件日志

图片 45

日备2 数据库日志

图片 46

日备2 bak文件日志

图片 47

 

备份脚本 COPY_ONLY

图片 48图片 49

 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 数据库日志

图片 50

日备1 bak文件日志

图片 51

日备2 数据库日志

图片 52

日备2 bak文件日志

图片 53

 

大家可以看一下这篇帖子

完整备份能截断日志吗?


差异备份的作用

既然SQLSERVER靠bak文件里的日志来进行redo/undo,就像上面说的那样,靠完备1,日备1,日备2就可以恢复所有数据

那么差异备份有什么用呢??

为什麽要有差异备份呢?

差异备份是为了RTO(Recovery Time Objective)

详见:

如果只做日志备份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文件里

我画了几张图

上面那个实验的理解图

图片 54


图片 55


图片 56


图片 57

大家可以使用下面两个SQL语句

1 SELECT * FROM [sys].[fn_dblog]()
2 SELECT * FROM [sys].[fn_dump_dblog]()

在完整备份、差异备份、日志备份测试一下在哪种备份类型下日志会被截断,截断的意思(数据库事务日志的记录数比bak文件里的日志记录数少)

就像我在开头做的那个实验一样

图片 58

 

GUI界面下,默认就是截断事务日志,很多人都以为截断事务日志要加XX backup option才可以截断

图片 59

 

如何查看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:DBBackuptestfull.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:DBBackuptestlog1.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:DBBackuptestlog2.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:DBBackuptestfull.bak' WITH  FILE = 1, 
MOVE N'test' TO N'D:MSSQLtest.mdf',  
MOVE N'test_log' TO N'D:MSSQLtest_log.ldf',  
NOUNLOAD,NORECOVERY , STATS = 5

GO

USE [master]
RESTORE DATABASE [test] 
FROM  DISK = N'D:DBBackuptestlog2.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 的情况下截断日志

图片 60

 

而是在checkpoint的时候截断日志,那么既然在checkpoint的时候已经截断了日志,在备份的时候数据库的事务日志

就没有不活动日志用于归档(把日志放进去bak文件)

 

 图片 61

 

我们使用下面的脚本进行日志备份就会报错

图片 62图片 63

 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 正在异常终止。

但是完整备份和差异备份则不受影响

备份脚本

图片 64图片 65

 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文件里

图片 66

还原脚本

图片 67图片 68

 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

图片 69

备份脚本

NO_LOG

图片 70图片 71

 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文件

图片 72

查看bak文件里的日志

图片 73图片 74

 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

图片 75

完备0行

图片 76

差备0行

其实可以用下图来理解

图片 77

bak文件里只有数据没有日志,连保证事务一致性的少量的活动日志都没有

 

备份脚本

TRUNCATE_ONLY

图片 78图片 79

 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文件

图片 80

查看日志备份前数据库事务日志

图片 81

查看日志备份前数据库事务日志

图片 82

其实可以用下图来理解

图片 83

truncate_only只是截断了日志,没有产生bak文件,更不用说备份日志到bak文件里面了

我们再做一个实验

备份脚本

图片 84图片 85

 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

图片 86

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

1 (1 行受影响)
2 消息 4214,级别 16,状态 1,第 8 行
3 无法执行 BACKUP LOG,因为当前没有数据库备份。
4 消息 3013,级别 16,状态 1,第 8 行
5 BACKUP LOG 正在异常终止。

可以用下图来理解

图片 87

 

(2)从数据库镜像进行恢复这种情况由于没有研究过就不说了

小结:

截断日志跟日志链断裂不是同一样东西!!

截断日志:针对数据库事务日志

日志链断裂:针对bak里的日志

大家不要混淆了


不神秘的事务日志尾部

当你的数据库损坏或置疑,你可以尝试进行尾日志备份

尾日志指的是哪个地方? 为什麽要进行尾日志备份?

假如有下面的脚本

图片 88图片 89

 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' 离线,无法打开该数据库

图片 90图片 91

 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

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

图片 92

图片 93
还原脚本

图片 94图片 95

 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
图片 96

 

回答开头的问题:尾日志指的是哪个地方? 为什麽要进行尾日志备份?

其实备份日志尾部,大家可以把他作为普通的事务日志备份

图片 97

如果遇到错误还可以加上CONTINUE_AFTER_ERROR 的backup option

图片 98图片 99

 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]

图片 100

 

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

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

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

图片 101

图片 102

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

图片 103

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

图片 104

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

 

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

图片 105图片 106

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号
图片 107

可以根据paul的文章做一些实验

Debunking a couple of myths around full database backups

 图片 108

 

我们做一个实验,先做一个完整备份

图片 109图片 110

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表就会产生一条记录
图片 111

我们将bak文件删除

图片 112

用GUI来还原数据库

图片 113

图片 114

 结果:

图片 115

他们的关系

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]

图片 116

每次备份的记录都记录在这些表里面,还原的时候SSMS读取这些表的记录,让你勾上几个选项就可以还原数据库了(非常傻瓜)

大家不要以为SQLSERVER在还原数据库的时候依靠[msdb].[dbo].[backupset]表的lsn去对比备份顺序

大家可以试想一下:

你的数据库备份了3次,有3个备份记录保存在backupset表

那么当你把数据库分离附加到别的sql实例的时候,你也可以还原你之前的备份

为什麽呢??

因为还原的时候只去数据库的事务日志去对比last lsn,是不依靠外部的其他的数据的而且也不需要依靠

如果还不明白的话,大家再看一下我上面贴出来的图片吧o(∩_∩)o


总结

一直以来本人对SQLSERVER的备份还原机制都不是很熟悉,通过跟宋沄剑的讨论让本人重新认识SQLSERVER的备份、还原

失眠了两晚,今晚可以吃一个好的水饺了

 

相关内容:

 

浅谈SQL Server中的事务日志(一)----事务日志的物理和逻辑构架

浅谈SQL Server中的事务日志(五)----日志在高可用和灾难恢复中的作用

 

上面的结论都经过我测试,希望大家可以指出本人的错处o(∩_∩)o

您们也可以动手测试一下我说的是不是真的o(∩_∩)o

如有不对的地方,欢迎大家拍砖o(∩_∩)o

 

2013-12-7 补充:

大家不要误解了,数据库事务日志截断的意思不是说把不活动日志部分删除了,而是把这些日志清空了

等待重用,除非你收缩事务日志,不然这些日志空间(VLF)只会等待重用

2013-12-8 补充:

还原日志备份的时候使用restore log 或restore database都是一样的

而还原差异备份的时候使用restore log就会报错

图片 117图片 118

 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的作用是记录当前数据库需要恢复时,可能回滚的上限

实例恢复和介质恢复

图片 119

图片 120

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

图片 121

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

图片 122

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

《SQL Server2008数据库技术内幕》

图片 123

 

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

原始出处:

  对于一个数据库小白来说,数据库的任何闪失带来的打击可说都是致命的。最初,我们让一个叫做“数据库”的小孩任性的在服务器上裸奔(没有任何数据备份),倡导自由与开放。突然有一天,这孩子生病了(数据篡改),病得很突然,很厉害,他不能和你描述他的任何感受(差异数据),那么你将苦于诊断病症。如果你现在有小孩以前的行为习惯数据(完备、差备、日志备份、尾日志备份),那么这无疑可以很快的针对治疗并康复(Recovery)!放声歌唱吧,阿拉So Easy, 哪里不会点哪里!


 

好了,欢愉过后,让我们静下来理一理上面着色的备份字面量:

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

无论是数据库Dev还是DBA,都希望关键业务数据库的完整性和可用性能得到保障,数据库备份是一种不错的选择。SQL Server 2008支持不同应用层次的多种备份方式,为我们的业务数据提供了强有力的保障,这一篇博文就来探讨如何在SQL Server 2008下设计合理的备份策略。

    1、完备:完整数据备份(截止现在时间点,数据库完整数据)

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

为了设计合理的备份策略,首先要熟悉SQL Server 2008都支持哪些恢复模式,它支持的恢复模式有如下:

    2、差备:差异数据备份(截止现在时间点,与上一份完备数据之间的差异)

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

 

    3、日志备份:事务日志数据备份(截止现在时间点,与上一次日志备份的差异)

备份的重要性

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

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

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

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

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

 

    4、尾日志备份:需手动备份(截止异常时间点,与上一次日志备份的差异)

文件和文件组备份

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

图片 124

    备注:

恢复模式

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

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

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

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

翻译后如下:

        1、1、2、3、4点都基于数据库恢复模式:完整模式(此模式会产生大量日志,需要定期维护日志)

不能最小记录的操作

可以被最小记录的操作包括大容量导入操作(例如使用BCP或BULK INSERT),SELECT/INTO操作和特定索引操作(例如索引重建)。完整列表可以在这里找到:

 

        2、2、3、4点的都基于点1,即其他备份依赖至少有一次完备才能进行 

选择正确的恢复模式

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

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

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

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

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

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

 

     3、1、2、3点都可以使用Sqlserver维护计划来备份

日志链和尾日志备份……

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

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

图片 125

     4、1、2点的备份里面,虽然数据是完整的,但是事务日志记录基本没有,所以要查看历史数据变更行为,必须有日志备份

设置和切换恢复模式

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

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

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

 

 

探索恢复模式

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

1 SELECT   name ,
2          recovery_model_desc
3 FROM     sys.databases
4 WHERE    name = 'TestDB' ;
5 GO

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

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

 1 /* STEP 1: CREATE THE DATABASE*/
 2 USE master ;
 3 
 4 IF EXISTS ( SELECT  name
 5             FROM    sys.databases
 6             WHERE   name = 'TestDB' ) 
 7     DROP DATABASE TestDB ;
 8 
 9 CREATE DATABASE TestDB ON
10 (
11   NAME = TestDB_dat,
12   FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATATestDB.mdf'
13 ) LOG ON
14 (
15   NAME = TestDB_log,
16   FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDataTestDB.ldf'
17 ) ;
18 
19 /*STEP 2: INSERT A MILLION ROWS INTO A TABLE*/
20 USE TestDB
21 GO
22 IF OBJECT_ID('dbo.LogTest', 'U') IS NOT NULL 
23     DROP TABLE dbo.LogTest ;
24 SELECT TOP 1000000
25   SomeID = IDENTITY( INT,1,1 ),
26   SomeInt = ABS(CHECKSUM(NEWID())) % 50000   1 ,
27   SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID())) % 26   65)
28     CHAR(ABS(CHECKSUM(NEWID())) % 26   65) ,
29   SomeMoney = CAST(ABS(CHECKSUM(NEWID())) % 
30                      10000 / 100.0 AS MONEY) ,
31   SomeDate = CAST(RAND(CHECKSUM(NEWID()))
32                    * 3653.0   36524.0 AS DATETIME) ,
33   SomeHex12 = RIGHT(NEWID(), 12)
34 INTO    dbo.LogTest
35 FROM    sys.all_columns ac1
36         CROSS JOIN sys.all_columns ac2 ;
37 
38 SELECT   name ,
39          recovery_model_desc
40 FROM     sys.databases
41 WHERE    name = 'TestDB' ;
42 GO

图片 126

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

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

图片 127

强制一个检查点(CHECKPOINT),再次检查日志使用率。

1 CHECKPOINT
2 GO
3 
4 DBCC SQLPERF(LOGSPACE) ;
5 -- DBCC SQLPERF reports a 100 MB log file about 60% full

图片 128

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

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

1 SELECT   db_name(database_id) AS 'DatabaseName' ,
2          last_log_backup_lsn
3 FROM     master.sys.database_recovery_status
4 WHERE    database_id = db_id('TestDB') ;
5 GO

图片 129

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

简单恢复模式:

备份方案

切换模式

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

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

在简单恢复模式下,只支持完整备份和差异备份,不支持事务日志备份。在简单恢复模式下还原数据库时只能还原到上一次数据库备份的数据,而上一次数据库备份以后的数据将无法进行还原,在发生灾难时,这些上一次数据库备份以后的数据必须重做。所以简单恢复模式并不适用于生产系统。另外在简单恢复模式下,由于事务日志会被截断,所以日志文件不会一直膨胀,非常小。

  完备(1天/次) 差备(6小时/次) 日志备份(30分钟/次) 尾日志备份(可选,灾难时使用)

备份的自动化和验证

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

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

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

完整恢复模式:

    备注:

完整恢复模式是微软建议在生产环境中使用的恢复模式。在正常情况下(即能备份日志尾部)发生灾难进行还原数据库时,不会丢失任务数据。但是如果日志尾部损坏,则必须重做自上一次日志备份或差异备份等之后所做的更改。在完整恢复模式下,所有的操作都会在日志中完整地记录下来。

      1、备份间隔视具体情况可调整,1天1完备针对小型数据库还行,大型数据库会造成大量冗余

大容量日志恢复模式:

        2、ApexSQLLog是一个不错的分析日志的工具,有破解版,支持SqlServer2014

大容量日志恢复模式简单地记录了大多数大容量操作日志(如Bulk INSERT,CREATE INDEX,SELECT INTO等),而不是记录全部大容量操作日志,所以这些大容量操作比在完整恢复模式下执行要快很多,同时大容量日志恢复模式完整记录了其他事务日志。所以大容量日志恢复模式是一种特殊用途的恢复模式,只应用于提高某些大规模大容量操作(如大量数据的大容量导入)的性能。完整恢复模式下有关备份的许多说明也适用于大容量日志恢复模式。

 

如果在最新日志备份后发生日志损坏或执行大容量日志记录操作,则必须重做自该上次备份之后所做的更改,否则不丢失任何数据。

 

另外设计合理的备份策略,还要熟悉SQL Server 2008都支持哪些备份类型,它支持的备份类型有如下:

by:海豚湾-丰

 

图片 130

 

 

翻译后如下:

 

图片 131

 

 

完整数据库备份:

完整备份会备份数据库中的所有数据,以及可以恢复这些数据的足够的日志。它为差异、事务日志备份创建基准备份。在数据库底层上,完整备份实际上是把所有页(page)复制到备份设备上。

差异数据库备份:

差异备份仅备份自上次完整备份后发生更改的数据。通常,建立基准备份之后执行的差异备份比基准备份更小,创建速度也更快。因此,使用差异备份可以加快进行频繁备份的速度,从而降低数据丢失的风险。通常,一个差异基准会由若干个相继的差异备份使用。还原时,首先还原完整备份,然后再还原最后一个的差异备份。
业务数据库运行一段时间后,随着数据库的更新,包含在差异备份中的数据量会增加。这使得创建和还原差异备份的速度变慢。因此,必须重新创建一个完整备份,为另一个系列的差异备份提供新的差异基准。

同样,差异备份和完整备份类似,也会备份恢复数据的足够日志,这是由数据库系统控制的。

在数据库底层上,差异备份是备份自上次完整备份以后所有修改的区(extent)。

部分备份:

部分备份与完整数据库备份类似,但是部分备份不包含所有文件组。部分备份包含主文件组、每个读写文件组以及任何指定(可选)的只读文件中的所有数据。部分备份在希望不包括只读文件组时非常有用。只读数据库的部分备份仅包含主文件组。

部分备份功能从SQL Server 2005开始引入。

创建部分备份时,必须在BACKUP 语句中指定 READ_WRITE_FILEGROUPS 选项。也可以指定任何只读文件或文件组,以便将其包括在部分备份中。

事务日志备份:

在完整恢复模式或大容量日志恢复模式下,需要定期进行事务日志备份。每个日志备份都包括创建备份时处于活动状态的部分事务日志,以及先前日志备份中未备份的所有日志记录。在创建第一个事务日志备份之前,必须先创建完整备份(如完整数据库备份或一组文件备份中的第一个完整备份)。此后,必须定期备份事务日志。这不仅能最小化工作丢失风险,还有助于事务日志的截断。通常,事务日志在每次常规日志备份之后截断。

连续的日志备份序列称为“日志链”。日志链从数据库的完整备份开始。通常,仅当第一次完整备份数据库时或者将恢复模式从简单恢复模式切换到完整恢复模式或大容量日志恢复模式之后,才会开始一个新的日志链。在完整恢复模式下(或者在大容量日志恢复模式下的某些时候),连续不断的日志链可以将数据库还原到任意时间点。

若要将数据库还原到故障点,必须保证日志链是完整的。也就是说,事务日志备份的连续序列必须能够延续到故障点。此日志序列的开始位置取决于所还原的数据备份类型:数据库备份(包括完整或差异备份)、部分备份或文件备份。对于数据库备份或部分备份,日志备份序列必须从数据库备份或部分备份的结尾处开始延续。对于一组文件备份,日志备份序列必须从整组文件备份的开头开始延续。

如果日志备份丢失或损坏,则可通过创建完整数据库备份或差异数据库备份并随后备份事务日志来开始一个新的日志链。如果要将数据库还原到事务日志备份内的某个时点,则建议保留丢失的日志备份之前的事务日志备份。

尾日志备份:

在完整恢复模式或大容量日志恢复模式下数据库发生灾难时,SQL Server 2005或2008可以备份日志结尾以捕获尚未备份的活动日志记录,把还原数据库操作之前对日志尾部执行的日志备份称为尾日志备份。

所以这里面有一点特别重要,在完整恢复模式或大容量日志恢复模式下一旦数据库发生灾难,还原数据库时,进行的第一步操作是尾日志备份(如果尾日志能备份的话),这样才不会丢失自上一次日志备份(也可能是完整或差异备份,主要是看用什么备份策略)后的数据。如果日志文件受损且无法创建结尾日志备份,则必须在不使用结尾日志备份的情况下还原数据库。最新日志备份(也可能是完整或差异备份,主要是看用什么备份策略)后提交的任何事务都将丢失。

文件和文件组备份:

针对大型数据库和性能要求使完整数据库备份显得不切实际时,则可以创建文件备份。文件备份包含一个或多个文件(或文件组)中的所有数据。文件备份包括完整文件备份和差异文件备份。针对大型数据库可以分别备份和还原数据库中的文件,而且可以仅还原已损坏的文件,而不必还原数据库的其他部分。

差异文件备份为创建当前文件备份提供了一种快速并且节省空间的方式。在简单恢复模式下,仅为只读文件组启用了差异文件备份。在完整恢复模式下,允许对具有差异基准的任何文件组进行差异文件备份。

文件和文件组备份增加了备份和还原的复杂度。

Copy_only备份:

仅复制备份可以在不打断正常备份序列的情况下复制数据库的内容,这个功能从SQL Server 2005开始引入。事务日志从不在仅复制备份后出现截断,这对平时DEV或DBA仅想获得一份完整的数据库用于测试工作,而又不影响当前的备份序列非常方便。

 

   设计一个数据库的最佳备份策略,会面临如何选择使用哪种恢复模式的问题,因为恢复模式控制着备份和还原的行为。一般来讲,简单恢复模式一般适合用于测试或开发数据库。对于生产数据库,最佳选择通常是完整恢复模式,还可以选择大容量日志恢复模式作为补充。但简单恢复模式有时也适合小型生产数据库(尤其是当其大部分或完全为只读时)或数据仓库使用。

若要为特定数据库确定最佳恢复模式,应考虑数据库的恢复目标和要求,数据使用方式,员工因素以及是否可对日志备份进行管理等。

恢复目标要求 :

l  不丢失任何更改的重要程度如何?

l  重新创建丢失的数据的难易程度如何?

l  是否有两个或两个以上的数据库在逻辑上必须保持一致?

员工因素 :

是否雇用系统或数据库管理员?如果没有,那么由谁负责执行备份和恢复操作,如何对他们进行培训?

数据使用方式,针对当前数据库考虑下列问题:

l  数据库中的数据多长时间更改一次?

l  是否有些表明显比其他表修改频繁?

l  是否有关键生产周期?如果有,那么在这些周期中的使用方式是怎样的?数据库是否会经历插入操作和其他更新操作的高峰期?
可能需要计划在非高峰期进行数据备份。当大量使用 I/O 系统时,通常只需使用日志备份。

l  数据库是否会遇到可能无法立即检测到的危险更新或应用程序错误?
如果数据库会遇到这些情况,请考虑使用完整恢复模式。这可以使用日志备份将数据库恢复到特定时间点。

何时使用简单恢复模式?

如果符合下列所有要求,则使用简单恢复模式:

l  不需要故障点恢复。如果数据库丢失或损坏,则会丢失自上一次备份到故障发生之间的所有更新,但你愿意接受这个损失。

l  愿意承担丢失日志中某些数据的风险。

l  不希望备份和还原事务日志,希望只依靠完整备份和差异备份。

 

何时使用完整恢复模式?

如果符合下列任一要求,则使用完整恢复模式(还可以选择使用大容量日志恢复模式):

l  必须能够恢复所有数据。

l  必须能够恢复到故障点。

l  希望可以还原单个页。

l  愿意承担事务日志备份的管理开销。

l  数据库包含多个文件组,并且希望逐段还原读/写辅助文件组(以及可选地还原只读文件组)。

 

何时使用大容量日志恢复模式?

大容量日志恢复模式作为完整恢复模式的附加补充。建议仅在运行大规模大容量操作期间以及在不需要数据库的时间点恢复时使用该模式。

*l  数据库是否会发生周期性的数据库大容量操作?

在该恢复模式下,多数大容量操作仅进行最小日志记录。如果使用完整恢复模式,则可以在执行此类大容量操作前临时切换到大容量日志恢复模式。通常,大容量日志恢复模式与完整恢复模式相似,只是它按最小方式记录多数大容量操作。大容量日志恢复模式仅适合在能够以最小方式记录操作的大容量操作期间使用。建议在其余时间使用完整恢复模式。当完成一组大容量操作后,建议立即切换回完整恢复模式。*

 

下面就以简单恢复模式和完整恢复模式来设计几个备份策略。

一,简单恢复模式下的备份策略设计:

1, 仅完整数据库备份策略

这种策略仅适用于经常备份的小型数据库,数据丢失风险比较大

此策略仅使用包含数据库中所有数据的完整数据库备份。例如下图完成5个完整数据库备份后发生灾难,只需要还原最近的备份(在 t5 时点执行的备份)。还原此备份会将数据库恢复到 t5 时点。由t6 框表示的所有后续更新都将丢失。

 

图片 132

 

在这种策略下为了最大程度降低数据丢失的风险,可以增加备份次数和缩短备份间隔,如下图:

 

 

 

图片 133

2,完整数据库备份 差异数据库备份策略

这种策略比只使用仅完整数据库备份策略,减少了数据丢失风险。例如下图在第一个完整数据库备份完成后,会接着进行三个差异数据库备份。随着时间推移,第三个差异备份已经足够大,因而下一个备份重新使用完整数据库备份。该数据库备份将成为新的差异基准。

 

图片 134

在这种备份策略下,如果在t4时进行“差异数据库备份3”完成后而t5时的“完整数据库备份2”还没进行的情况下发生灾难,只需要先还原t1时的“完整数据库备份1”,接着还原最后一次即t4时的“差异数据库备份3”就可以恢复数据库,但是t4以后的数据会丢失。

二,完整恢复模式下的备份策略设计:

1, 完整数据库备份 日志备份策略

例如下图已完成了完整数据库备份 Db_1 以及两个日志备份 Log_1 和 Log_2。在 Log_2 日志备份后的某个时间,数据库出现数据丢失或灾难。在还原这三个备份前,必须备份活动日志(日志尾部,如果能备份的话)。然后依次还原 Db_1、Log_1 和 Log_2,而不恢复数据库(还原时必须使用norecovery选项),接着还原并恢复结尾日志备份 (还原时必须使用recovery选项)。这将把数据库恢复到故障点,从而恢复所有数据。

 

 

图片 135

2, 完整数据库备份 差异数据库备份策略

例如下图在第一个数据库备份完成后,会接着进行三个差异数据库备份。随着时间推移,第三个差异备份已经足够大,因而下一个备份重新使用完整数据库备份。该数据库备份将成为新的差异基准。

 

图片 136

 

在这种备份策略下,如果在t10时进行“差异数据库备份3”完成后而t13时的“完整数据库备份2”还没进行的情况下发生灾难,还原时,必须先备份活动日志(日志尾部,如果能备份的话)。然后依次还原t10时的“完整数据库备份1”,最后一次即t4时的“差异数据库备份3”(还原时必须使用norecovery选项)接着还原并恢复结尾日志备份 (还原时必须使用recovery选项)。这将把数据库恢复到故障点,从而恢复所有数据。

3, 完整数据库备份 差异数据库备份 日志备份策略

这种备份策略可以最大程度地降低数据丢失的风险,也是比较推荐的备份策略!

例如下图从t1到t12时间段内,进行了一次完整数据库备份,若干日志备份,三个差异数据库备份。

 

 

图片 137

在这种备份策略下,当t12时的日志备份完成后数据丢失或发生灾难,如何还原数据库呢?步骤如下:

第一步:备份活动日志(日志尾部,如果能备份的话)

第二步:还原t1时的“完整数据库备份1”

第三步:还原t10时的“差异数据库备份3”

第四步:还原t11时的日志备份

第五步:还原t12时的日志备份

第六步:还原第一步的“尾日志备份”

其中第二,三,四,五步还原时必须用norecovery选项,第六步用recovery选项。

根据业务系统级别的不同,一般可以一周进行一次完整数据库备份,一天进行一次差异数据库备份,30分钟或1小时进行一次日志备份。

编辑:数据库 本文来源:  对于一个数据库小白来说,Server 2008下设计合

关键词: 澳门新濠3559