SQL Server事務日志備份內(nèi)容研究

摘 ?要：研究了在將數(shù)據(jù)庫設置為完整恢復模式后，事務日志備份操作中的內(nèi)容。給出SQL Server事務日志備份的概念，解釋了first_lsn和last_lsn的概念，并給出SQL Server確定這兩個數(shù)值的方法，指出每次事務日志備份的內(nèi)容是first_lsn和last_lsn之間的重做數(shù)據(jù)。構(gòu)造簡潔的實驗步驟，驗證了第一次事務日志備份時，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 ?引 ?言

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

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

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

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

1 ?全庫備份的first_lsn和last_lsn

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

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

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

（3）拷貝數(shù)據(jù)庫中的所有數(shù)據(jù)。

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

2 ?完整恢復模式下事務日志備份的內(nèi)容

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

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

圖1中呈現(xiàn)了在完整恢復模式下事務日志備份的主要內(nèi)容。

3 ?事務日志備份內(nèi)容的驗證

下面創(chuàng)建測試數(shù)據(jù)庫testBackup，然后執(zhí)行一次全庫備份，再執(zhí)行2次事務日志備份，最后通過查詢這3個備份集信息，得出有關事務日志備份內(nèi)容的結(jié)論。

在連接1中執(zhí)行以下操作，創(chuàng)建測試數(shù)據(jù)庫及測試數(shù)據(jù)：

1> create database testBackup

2> go

1> use testBackup

2> go

已將數(shù)據(jù)庫上下文更改為 '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

執(zhí)行下面命令，將testBackup數(shù)據(jù)庫設置為完整恢復模式：

1> alter database testBackup set recovery full

2> go

執(zhí)行下面命令，對testBackup數(shù)據(jù)庫執(zhí)行全庫備份：

1> backup database testBackup

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

3> with name='testBackup_full'

4> go

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

已為數(shù)據(jù)庫 '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表執(zhí)行insert操作，最后不提交，使其處于未結(jié)束狀態(tài)：

1> use testBackup

2> go

已將數(shù)據(jù)庫上下文更改為 '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，執(zhí)行第1次事務日志備份。

1> backup log testBackup

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

3> with name='testBackup_log_1'

4> go

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

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

執(zhí)行下面命令，查詢其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表執(zhí)行insert操作，并提交事務，然后查看此事務產(chǎn)生的重做數(shù)據(jù)：

1> use testBackup

2> go

已將數(shù)據(jù)庫上下文更改為 '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，執(zhí)行下面命令，進行第2次事務日志備份：

1> backup log testBackup

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

3> with name='testBackup_log_2'

4> go

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

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

執(zhí)行下面命令，查詢第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

執(zhí)行下面命令，查詢各個備份集的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 ?結(jié) ?論

由以上查詢結(jié)果，可以驗證四個結(jié)論：

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

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

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

（4）每次事務日志備份的database_backup_lsn未發(fā)生變化，都是其全庫備份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數(shù)據(jù)庫技術(shù)內(nèi)幕 [M].北京：中國鐵道出版社，2012.

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