SQL Server事務日志備份內容研究

摘 ?要：研究了在將數據庫設置為完整恢復模式后，事務日志備份操作中的內容。給出SQL Server事務日志備份的概念，解釋了first_lsn和last_lsn的概念，并給出SQL Server確定這兩個數值的方法，指出每次事務日志備份的內容是first_lsn和last_lsn之間的重做數據。構造簡潔的實驗步驟，驗證了第一次事務日志備份時，first_lsn是上一次全庫備份的first_lsn，從第二次事務日志備份開始，first_lsn是上一次事務日志備份的last_lsn。

關鍵詞：SQL Server;事務日志備份;完整恢復模式

中圖分類號：TP311 ? ? 文獻標識碼：A 文章編號：2096-4706（2021）06-0158-03

Study on the SQL Server Transaction Log Backup Content

Li Aiwu

（Guangdong Vocational College of Post and Telecom，Guangzhou ?510630，China）

Abstract：This paper studies the content of transaction log backup operation after the database is set to full recovery mode. Gives the concept of SQL Server transaction log backup，explains the concept of first_lsn and last_lsn，and gives the method for SQL Server to determine these two numerical values，pointing out that the content of each transaction log backup is the redo data between first_lsn and last_lsn. Constructing concise experimental steps to verify that the first_lsn is the first_lsn of the previous full database backup when the first transaction log backups，and the first_lsn is the last_lsn of the previous transaction log backup from the beginning of the second transaction log backup.

Keywords：SQL Server;transaction log backup;full recovery mode

0 ?引 ?言

數據庫備份是保證數據安全的重要措施。SQLServer數據庫備份分為全庫備份、事務日志備份和差異備份三種類型，全庫備份的內容為數據庫中的全部數據以及first_lsn和last_lsn內的全部重做數據，差異備份是自從上次備份以來修改過的區內的數據。數據庫管理員應熟悉各類備份的步驟，并深刻理解各類備份操作的內容。

事務日志備份是為了恢復數據庫全庫備份操作完成后產生的新數據，從而使數據庫恢復到故障時刻，不會因為介質故障而造成數據丟失，也可以使數據庫恢復到全庫備份操作后的指定時間，用以撤銷某些誤操作。

執行事務日志備份時，先確定要備份的重做數據范圍，即確定first_lsn和last_lsn，然后備份位于first_lsn和last_lsn之間的重做數據。

本文詳細介紹事務日志備份的相關概念和步驟，并用實例驗證相關結論。

1 ?全庫備份的first_lsn和last_lsn

執行全庫備份時，SQL Server依序完成以下步驟：

（1）SQL Server執行checkpoint，把當前內存中被修改的數據寫入磁盤文件，并記下checkpoint操作的LSN（Log Sequence Number，用于標識重做記錄的序號），并作為checkpoint_lsn寫入備份集文件頭。

（2）計算數據庫當前的MinLSN，MinLSN是checkpoint_lsn與當前最早活動事務的起始LSN之間的較小者，這個LSN稱為全庫備份的first_lsn。

（3）拷貝數據庫中的所有數據。

（4）數據讀取完畢后，根據數據庫當前的最大LSN值，計算數據庫將要執行的下一個事務的開始LSN（這個LSN稱為last_lsn），然后將first_lsn與last_lsn記入此次備份集的文件頭。

2 ?完整恢復模式下事務日志備份的內容

如果是執行全庫備份后第一次執行事務日志備份，則first_lsn是上一次全庫備份的first_lsn，即第一次事務日志備份會將其對應全庫備份集中已備份的重做數據再重新備份。如果在本次事務日志備份之前已經執行過事務日志備份，則first_lsn是上一次事務日志備份的last_lsn。last_lsn是執行事務日志備份時最后一個成功結束事務的下一個事務的開始LSN。

在完整恢復模式下，從全庫備份后的第二次事務日志備份開始，其內容是上次事務日志備份以來新產生的重做數據。

圖1中呈現了在完整恢復模式下事務日志備份的主要內容。

3 ?事務日志備份內容的驗證

下面創建測試數據庫testBackup，然后執行一次全庫備份，再執行2次事務日志備份，最后通過查詢這3個備份集信息，得出有關事務日志備份內容的結論。

在連接1中執行以下操作，創建測試數據庫及測試數據：

1> create database testBackup

2> go

1> use testBackup

2> go

已將數據庫上下文更改為 'testBackup'

1> create table t1（a int， b char（5））

2> create table t2（a int， b char（5））

3> insert into t1 values（1，'xxxxx'）

4> insert into t2 values（1，'xxxxx'）

5> go

執行下面命令，將testBackup數據庫設置為完整恢復模式：

1> alter database testBackup set recovery full

2> go

執行下面命令，對testBackup數據庫執行全庫備份：

1> backup database testBackup

2> to disk='e：＼sqldata＼testBackup_full.bak'

3> with name='testBackup_full'

4> go

已為數據庫 'testBackup'，文件 'testBackup' （位于文件1上）處理了176頁

已為數據庫 'testBackup'，文件 'testBackup_log' （位于文件1上）處理了5頁

BACKUP DATABASE 成功處理了181頁，花費 0.352 秒（4.010 MB/秒）

查詢其LSN范圍如下：

1> select cast（name as char（20）） as name，first_lsn，last_lsn

2> from msdb.dbo.backupset

3> where database_name='testBackup'

4> go

namefirst_lsnlast_lsn

-------------------- --------------------------- ---------------------------

testBackup_full ? ? ? ? ? ? ? ?56000000008400178 ? ? ? ? ? 56000000015900001

在連接2中開始一個顯式事務，對t1表執行insert操作，最后不提交，使其處于未結束狀態：

1> use testBackup

2> go

已將數據庫上下文更改為 'testBackup'

1> declare @cur_max_lsn as nchar（46）

2> select @cur_max_lsn=max（[current lsn]）

3> from fn_dblog（null，null）

4> begin tran

5> insert into t1 values（2，'xxxxx'）

6> insert into t1 values（3，'xxxxx'）

7> select （[current lsn]） as lsn， operation

8> from fn_dblog（null，null）

9> where [current lsn]>@cur_max_lsn

10> go

（1 行受影響）

lsn ? ? ? ? ? ? ? ? ? ? operation

----------------------- -------------------------

00000038：000000a7：0001 ?LOP_BEGIN_XACT

00000038：000000a7：0002 ?LOP_INSERT_ROWS

00000038：000000a7：0003 ?LOP_INSERT_ROWS

切換至連接1，執行第1次事務日志備份。

1> backup log testBackup

2> to disk='e：＼sqldata＼testBackup_log_1.bak'

3> with name='testBackup_log_1'

4> go

已為數據庫 'testBackup'，文件 'testBackup_log' （位于文件 1 上）處理了 6 頁

BACKUP LOG 成功處理了 6 頁，花費 0.105 秒（0.385 MB/秒）

執行下面命令，查詢其LSN范圍：

1> select cast（name as char（20）） as name，first_lsn，last_lsn

2> from msdb.dbo.backupset

3> where database_name='testBackup'

4> go

namefirst_lsnlast_lsn

-------------------- --------------------------- ---------------------------

testBackup_full ? ? ? ? ? ? ? ?56000000008400178 ? ? ? ? ? 56000000015900001

testBackup_log_1 ? ? ? ? ? ? ? 56000000008400178 ? ? ? ? ? 56000000016700001

在連接3中開始一個顯式事務，對t2表執行insert操作，并提交事務，然后查看此事務產生的重做數據：

1> use testBackup

2> go

已將數據庫上下文更改為 'testBackup'

1> declare @cur_max_lsn as nchar（46）

2> select @cur_max_lsn=max（[current lsn]）

3> from fn_dblog（null，null）

4> begin tran

5> insert into t2 values（2，'xxxxx'）

6> insert into t2 values（3，'xxxxx'）

7> commit

8> select （[current lsn]） as lsn， operation

9> from fn_dblog（null，null）

10> where [current lsn]>@cur_max_lsn

11> go

（1 行受影響）

lsn ? ? ? ? ? ? ? ? ? ? operation

----------------------- -------------------------------

00000038：000000a7：0004 ?LOP_BEGIN_XACT

00000038：000000a7：0005 ?LOP_SET_BITS

00000038：000000a7：0006 ?LOP_INSERT_ROWS

00000038：000000a7：0007 ?LOP_INSERT_ROWS

00000038：000000a7：0008 ?LOP_COMMIT_XACT

切換至連接1，執行下面命令，進行第2次事務日志備份：

1> backup log testBackup

2> to disk='e：＼sqldata＼testBackup_log_2.bak'

3> with name='testBackup_log_2'

4> go

已為數據庫 'testBackup'，文件 'testBackup_log' （位于文件 1 上）處理了 1 頁。

BACKUP LOG 成功處理了 1 頁，花費 0.430 秒（0.002 MB/秒）

執行下面命令，查詢第2次事務日志備份的LSN范圍：

1> select cast（name as char（20）） as name，first_lsn，last_lsn

2> from msdb.dbo.backupset

3> where database_name='testBackup'

4> go

namefirst_lsnlast_lsn

-------------------- --------------------------- ---------------------------

testBackup_full ? ? ? ? ? ? ? ?56000000008400178 ? ? ? ? ? 56000000015900001

testBackup_log_1 ? ? ? ? ? ? ? 56000000008400178 ? ? ? ? ? 56000000016700001

testBackup_log_2 ? ? ? ? ? ? ? 56000000016700001 ? ? ? ? ? 56000000016900001

執行下面命令，查詢各個備份集的checkpoint_lsn及database_backup_lsn：

1> select cast（name as char（20）） as name，checkpoint_lsn，database_backup_lsn

2> from msdb.dbo.backupset

3> where database_name='testBackup'

4> go

namecheckpoint_lsndatabase_backup_lsn

-------------------- --------------------------- ---------------------------

testBackup_full ? ? ? ? ? ? ? ?56000000008400178 ? ? ? ? ? ? ? ? ? ? ? ? ? 0

testBackup_log_1 ? ? ? ? ? ? ? 56000000008400178 ? ? ? ? ? 56000000008400178

testBackup_log_2 ? ? ? ? ? ? ? 56000000008400178 ? ? ? ? ? 56000000008400178

4 ?結 ?論

由以上查詢結果，可以驗證四個結論：

（1）全庫備份后的第1次事務日志備份內容的起始點為全庫備份的first_lsn，也就是說，第1次事務日志備份會重新將全庫備份中已經備份的重做記錄再次備份;

（2）從第2次事務日志備份開始，每次備份的起始點為上次事務日志備份的last_lsn，從而使所有的事務日志備份內容構成一個連續的整體，在利用事務日志備份恢復數據庫時，可以在恢復全庫備份后，再按照其備份順序依次恢復事務日志備份;

（3）執行事務日志備份時，不會導致checkpoint執行;

（4）每次事務日志備份的database_backup_lsn未發生變化，都是其全庫備份checkpoint_lsn。

參考文獻：

[1] KOROTKEVITCH D. Expert SQL Server Transactions and Locking [M].New York：Apress，2018.

[2] MCGEHEE S. SQL Server Backup and Restore [M].Redgate Publishing，2012.

[3] CARTER P A. Securing SQL Server：DBAs Defending the Database [M].Berkely：Apress，2016.

[4] KOROTKEVITCH D. Pro SQL Server Internals [M].2nd ed.New York：Apress，2016.

[5] 李愛武.SQLServer 2008數據庫技術內幕 [M].北京：中國鐵道出版社，2012.

作者簡介：李愛武（1969.07—），男，漢族，河北肅寧人，副教授，理學碩士，研究方向：數據庫技術、數據分析。