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

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

關(guān)鍵詞：SQL Server;事務(wù)日志備份;完整恢復(fù)模式

中圖分類號(hào)：TP311 ? ? 文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：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ù)庫(kù)備份是保證數(shù)據(jù)安全的重要措施。SQLServer數(shù)據(jù)庫(kù)備份分為全庫(kù)備份、事務(wù)日志備份和差異備份三種類型，全庫(kù)備份的內(nèi)容為數(shù)據(jù)庫(kù)中的全部數(shù)據(jù)以及first_lsn和last_lsn內(nèi)的全部重做數(shù)據(jù)，差異備份是自從上次備份以來(lái)修改過(guò)的區(qū)內(nèi)的數(shù)據(jù)。數(shù)據(jù)庫(kù)管理員應(yīng)熟悉各類備份的步驟，并深刻理解各類備份操作的內(nèi)容。

事務(wù)日志備份是為了恢復(fù)數(shù)據(jù)庫(kù)全庫(kù)備份操作完成后產(chǎn)生的新數(shù)據(jù)，從而使數(shù)據(jù)庫(kù)恢復(fù)到故障時(shí)刻，不會(huì)因?yàn)榻橘|(zhì)故障而造成數(shù)據(jù)丟失，也可以使數(shù)據(jù)庫(kù)恢復(fù)到全庫(kù)備份操作后的指定時(shí)間，用以撤銷(xiāo)某些誤操作。

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

本文詳細(xì)介紹事務(wù)日志備份的相關(guān)概念和步驟，并用實(shí)例驗(yàn)證相關(guān)結(jié)論。

1 ?全庫(kù)備份的first_lsn和last_lsn

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

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

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

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

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

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

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

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

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

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

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

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

1> create database testBackup

2> go

1> use testBackup

2> go

已將數(shù)據(jù)庫(kù)上下文更改為 '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ù)庫(kù)設(shè)置為完整恢復(fù)模式：

1> alter database testBackup set recovery full

2> go

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

1> backup database testBackup

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

3> with name='testBackup_full'

4> go

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

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

BACKUP DATABASE 成功處理了181頁(yè)，花費(fèi) 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中開(kāi)始一個(gè)顯式事務(wù)，對(duì)t1表執(zhí)行insert操作，最后不提交，使其處于未結(jié)束狀態(tài)：

1> use testBackup

2> go

已將數(shù)據(jù)庫(kù)上下文更改為 '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次事務(wù)日志備份。

1> backup log testBackup

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

3> with name='testBackup_log_1'

4> go

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

BACKUP LOG 成功處理了 6 頁(yè)，花費(fèi) 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中開(kāi)始一個(gè)顯式事務(wù)，對(duì)t2表執(zhí)行insert操作，并提交事務(wù)，然后查看此事務(wù)產(chǎn)生的重做數(shù)據(jù)：

1> use testBackup

2> go

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

1> backup log testBackup

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

3> with name='testBackup_log_2'

4> go

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

BACKUP LOG 成功處理了 1 頁(yè)，花費(fèi) 0.430 秒（0.002 MB/秒）

執(zhí)行下面命令，查詢第2次事務(wù)日志備份的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í)行下面命令，查詢各個(gè)備份集的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é)果，可以驗(yàn)證四個(gè)結(jié)論：

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

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

（3）執(zhí)行事務(wù)日志備份時(shí)，不會(huì)導(dǎo)致checkpoint執(zhí)行;

（4）每次事務(wù)日志備份的database_backup_lsn未發(fā)生變化，都是其全庫(kù)備份checkpoint_lsn。

參考文獻(xiàn)：

[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] 李愛(ài)武.SQLServer 2008數(shù)據(jù)庫(kù)技術(shù)內(nèi)幕 [M].北京：中國(guó)鐵道出版社，2012.

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