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

php中文网

发布时间：2016-06-07 15:34:32

1318人浏览过

来源于php中文网

原创

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

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

1 --第一个完整备份
2 DECLARE @strbackup NVARCHAR(100)
3 --改为日期加时间的
4 SET @strbackup = 'C:\LogChainTest_full1_'
5     + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ',
6                       ''), ':', '') + '.bak'
7 BACKUP DATABASE LogChainTest TO DISK =@strbackup  WITH INIT,CHECKSUM ;
8 GO

 1 SELECT  *
 2 FROM    fn_dump_dblog(NULL, NULL, N'DISK', 1,
 3                       N'c:\LogChainTest_full1_20131206202536.bak', DEFAULT,
 4                       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
 5                       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
 6                       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
 7                       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
 8                       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
 9                       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
10                       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
11                       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
12                       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
13                       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
14                       DEFAULT, DEFAULT)

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

 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。

 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

 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]

1 消息 3136，级别 16，状态 3，第 1 行
2 无法还原此差异备份，因为该数据库尚未还原到正确的早期状态。
3 消息 3013，级别 16，状态 1，第 1 行
4 RESTORE DATABASE 正在异常终止。

 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

 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

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

 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

 1 USE master
 2 GO
 3 SELECT  *
 4 FROM    fn_dump_dblog(NULL, NULL, N'DISK', 1,
 5                       N'c:\LogChainTest_full1_20131207102535.bak', DEFAULT,
 6                       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
 7                       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
 8                       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
 9                       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
10                       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
11                       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
12                       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
13                       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
14                       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
15                       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
16                       DEFAULT, DEFAULT)

  1 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)