當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

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

發布時間：2025/3/15 数据库 26 豆豆

生活随笔收集整理的這篇文章主要介紹了您真的理解了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文件理解成一個壓縮包，完整備份和差異備份的時候會把數據和日志一起帶進壓縮包，

而日志備份的時候只會把日志帶進壓縮包

我們先從一個實驗開始吧

測試環境：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-》差異備份-》日志備份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') --34000000031500001BACKUP 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') --34000000032300001BACKUP 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')--34000000032800001USE [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 = 5GOUSE [master] RESTORE DATABASE [test] FROM DISK = N'D:\DBBackup\testlog2.bak' WITH FILE = 1, NOUNLOAD,NORECOVERY , STATS = 5GO 消息 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

備份記錄

實際上這個[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

我們做一個實驗，先做一個完整備份

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的備份、還原

失眠了兩晚，今晚可以吃一個好的水餃了

總結

以上是生活随笔為你收集整理的您真的理解了SQLSERVER的日志链了吗？的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： Android学习系列(15)--App
下一篇：【2014-11-23】《The Har