SQL Server 2019全庫備份的過程與內容研究

摘? 要：首先給出SQL Server全庫備份內容的結論，詳細列出SQL Server執行全庫備份時要執行的各個任務，指出全庫備份中不同范圍的事務日志備份在執行數據庫恢復時的不同作用，列舉全庫備份操作中容易忽視和混淆的幾個注意事項，解釋了涉及數據庫備份的幾個LSN屬性，最后以實例說明了SQL Server 2019全庫備份的first_lsn和last_lsn產生的過程。

關鍵詞：SQL Server 2019;數據庫全庫備份;備份內容;備份過程

中圖分類號：TP311? ? ? 文獻標識碼：A 文章編號：2096-4706（2021）05-0128-04

Study on the Process and Content of SQL Server 2019 Full Database Backup

LI Aiwu

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

Abstract：Firstly，it gives the conclusion of the content of SQL Server full database backup，lists in detail the various tasks to be performed when SQL Server performs full database backup，points out the different roles of different ranges of transaction log backups in full database backup when performing database recovery，lists several considerations that are easily overlooked and confused in the full database backup operations，explains several attributes of LSN related with the database backup. Finally，an example is constructed to illustrate the generating? process of first_lsn and last_lsn for SQL Server 2019 full database backup.

Keywords：SQL Server 2019;full database backup;backup content;backup process

0? 引? 言

數據庫備份是保證數據安全的重要措施。SQL Server數據庫備份分為全庫備份、事務日志備份和差異備份三種類型，數據庫管理員應熟悉各類備份的步驟，并深刻理解各類備份操作的內容。

全庫備份的內容是執行備份操作時，拷貝整個數據庫中的全部數據外加部分事務日志數據。全庫備份是執行差異備份或事務日志備份的基礎，若要執行差異備份或事務日志備份，必須先執行全庫備份。理解全庫備份的原理也是理解其他備份方式的基礎。

本文研究了全庫備份的步驟，解釋了涉及全庫備份的幾個LSN屬性，并且用實例驗證了相關結論。

1? 全庫備份的內容

SQL Server的全庫備份命令很簡潔，假定數據庫名稱為testBackup，執行以下SQL命令即可對其進行全庫備份：

1> backup database testBackup

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

3> with name='testBackup_full_1'

4> go

上述命令將數據庫備份至文件e：＼sqldata＼testBackup_full_1.bak，此次備份集的名稱為testBackup_full_1。

執行上述命令進行全庫備份時，SQL Server依次執行以下操作：

（1）執行checkpoint，把當前內存中被修改的數據寫入磁盤文件，把checkpoint操作的LSN作為checkpoint_lsn寫入備份集文件頭（LSN即Log Sequence Number，表示數據庫操作的重做日志序列號）;

（2）計算數據庫當前的MinLSN。MinLSN是第1步中checkpoint操作的LSN與當前最早活動事務的起始LSN之間的較小者，這個LSN也稱為全庫備份的first_lsn;

（3）搜索各個數據文件的GAM和SGAM數據頁中為0的位（這兩種數據頁中的位用于追蹤數據文件中區的分配狀態，為0表示已分配，為1表示未分配），得到所有分配出去的區的位置，然后讀取這些區中的數據并將數據拷貝到備份集文件中，這個步驟也可以簡單地描述為拷貝數據庫中的所有數據;

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

（5）讀取數據庫重做日志文件中位于first_lsn與last_lsn之間的重做記錄，即要讀取的重做記錄的LSN應滿足條件：first_lsn≤LSN

（6）將此次備份集文件頭中的有關信息，如fisrt_lsn、last_lsn、備份操作的開始及結束時間等數據，記入msdb數據庫的dbo.bacupset系統表。

以上步驟可以用圖1來簡單描述。

由以上步驟可知，全庫備份操作產生的備份集主要包括以下兩部分數據：

（1）數據庫中所有包含數據的區;

（2）first_lsn與last_lsn之間的重做數據。

first_lsn與last_lsn之間的重做數據用于直接以全庫備份的形式恢復數據庫，即不涉及應用差異備份和事務日志備份來恢復數據庫。

若在全庫備份操作開始時，存在未完成的事務，則first_lsn

（1）first_lsn與checkpoint_lsn之間的重做數據用于在數據庫恢復時，回滾在備份完成時尚未提交的事務;

（2）checkpoint_lsn與last_lsn之間的重做數據用于在數據庫恢復時，前滾在備份操作開始之后（即checkpoint操作之后）、數據讀取操作完成之前提交的事務。

若全庫備份開始時，不存在未完成的事務，則first_lsn= checkpoint_lsn，若只使用全庫備份恢復數據庫，則備份集中的重做數據只用于前滾。

對于全庫備份集中的數據及重做記錄，還要注意以下幾點：

（1）對于繁忙的大容量數據庫，全庫備份操作要拷貝的區在讀取GAM和SGAM數據頁時就已確定，在讀取包含數據的區以及之后讀取重做日志階段，如果因為新數據的產生導致分配了新區，這些區不會包含在全庫備份內容中。

（2）如果在備份操作結束前，已經讀取的數據頁內容又發生了改變，這些新數據也不會被重新讀取。也就是說，全庫備份中的數據一般是備份操作開始時的數據庫快照內容，而不是備份操作完成時的數據庫快照內容。

（3）全庫備份中的重做數據并不是從備份操作開始到結束之間產生的重做數據。如果備份操作開始之前已經進行了若干事務，則first_lsn顯然會小于備份時的LSN，這種情況下，全庫備份會包含備份操作開始之前產生的重做記錄。

（4）數據讀取完成與重做數據讀取完成之間產生的重做數據的LSN都不小于last_lsn，即計算出last_lsn后產生的重做數據的LSN都不小于last_lsn，顯然這些重做數據不會包含在全庫備份中。也就是說，全庫備份中包含的重做記錄范圍一般是備份操作開始之前的某一時刻到備份操作完成之前的某一時刻。如果備份開始時，數據庫中不存在未結束的事務，則first_lsn與checkpoint_lsn是相同的，在這種情況下，要備份的重做記錄的起始點也就是備份操作執行時的LSN（即checkpoint_lsn）。

（5）雖然全庫備份中包含了事務日志備份，但是全庫備份操作并不會截斷日志文件，只有事務日志備份操作才會截斷日志文件。

2? 全庫備份集的幾個LSN屬性

first_lsn與last_lsn的含義如上節內容所述，由這兩個LSN可以確定備份操作中要包含的重做日志范圍，其值分別在備份操作開始及數據讀取完成時確定。

checkpoint_lsn是備份操作開始時，執行checkpoint操作的開始lsn，在使用備份文件恢復數據庫時，先把數據文件恢復到相應目錄后，再對數據文件應用重做日志，這個LSN用于指定所應用重做日志的起始點。

database_backup_lsn指最近一次全庫備份的checkpoint_lsn。一般在差異備份時才會有用。對于差異備份操作創建的備份集，它的database_backup_lsn屬性就是其上次全庫備份操作的checkpoint_lsn，差異備份的內容是執行上次全庫備份以來發生變化的區，而統計變化的起始點就是上次全庫備份開始時的checkpoint_lsn。差異備份的database_backup_lsn的作用是確認差異備份集文件與全庫備份集文件是否匹配。

如果是第一次執行全庫備份，則其database_backup_lsn為0。

3? 驗證全庫備份集的first_lsn及last_lsn

下面的實驗過程主要驗證全庫備份集的first_lsn與last_lsn是如何確定的。主要實驗步驟為：

（1）創建測試數據庫，為了使全庫備份操作能夠持續幾十秒鐘，在簡單恢復模式下為其添加400 MB數據。設置簡單恢復模式的目的在于添加數據時，可以重用重做文件中的VLF;

（2）在測試數據庫中創建兩個測試表t1與t2;

（3）開啟3個連接;

（4）在連接1中，執行SQL腳本程序，在其中開始一個事務，為t1表添加記錄，并使其處于未結束狀態，最后得出此事務中各個操作的LSN號以及事務的開始時間，假設事務開始的LSN為lsn#1;

（5）在連接2中，執行全庫備份，在全庫備份執行過程中，切換至連接3;

（6）在連接3中，執行SQL腳本程序（這個腳本程序應預先準備好，切換到連接3后，可以馬上執行），在其中開始一個事務，為t2表添加記錄，然后提交事務，最后得出此事務各個操作的LSN號以及事務的開始、結束時間，切換至連接2;

（7）在連接2中，等待全庫備份操作完成后，通過查詢msdb數據庫中的backupset系統表，得到備份集的first_lsn、last_lsn以及備份操作的開始、結束時間。確認first_lsn即連接1中得到的lsn#1，而備份集的last_lsn是連接3中所執行事務的下一個事務的開始LSN。

接著按照以上步驟完成實驗：

（1）在連接1中，創建測試數據庫，并將其設置為簡單恢復模式，從而在為其添加大量數據時，不會使重做日志文件增長過大。

1> create database testBackup

2> go

1> alter database testBackup set recovery simple

2> go

（2）執行以下命令，為testBackup數據庫添加大約400 MB數據。

1> use testBackup

2> go

1> create table t

2> （

3>a int identity，

4>b char（3000） default 'xxxxx'，

5>c char（3000） default 'yyyyy'

6> ）

7> go

1> set nocount on

2> go

1> insert into t default values

2> go 50000

（3）創建兩個測試表t1及t2。

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

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

3> go

（4）將testBackup數據庫設置為完整恢復模式：

1> alter database testBackup set recovery full

2> go

（5）繼續執行如下文所示的SQL腳本程序，在連接1中開始一個事務，并使其處于未結束狀態，最后查詢事務的開始時間，以及事務中各個操作產生日志記錄的LSN：

1> declare @cur_max_lsn as nchar（46）

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

3> from fn_dblog（null，null）

4> select getdate（） as tran_start

5> begin tran

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

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

8> from fn_dblog（null，null）

9> where [current lsn]>@cur_max_lsnand operation= 'LOP_BEGIN_XACT'

11> go

tran_start

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

2021-05-14 13：20：57.700

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

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

0000055c：00000039：0001? LOP_BEGIN_XACT

由上述查詢結果，可以得知：

（1）此事務的開始時刻為：2021-05-14 13：20：57.700;

（2）此事務的開始LSN為：55c：39：1。

在連接2中先刪除backupset系統表中的記錄，然后對測試數據庫進行全庫備份操作。因為數據庫中包含了400 MB數據，此備份操作一般會持續30秒左右。在備份操作開始幾秒后，切換至連接3：

1>exec msdb.dbo.sp_delete_backuphistory '20100615'

2> go

1> backup database testBackup

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

3> with name='testBackup_full'

4> go

切換至連接3后，在其中執行以下SQL腳本程序，開始一個事務，為t2表添加記錄后提交事務，最后得出事務的開始、結束時間，以及事務中各個操作產生的日志記錄的LSN。這里的SQL腳本程序需要提前準備好，保證切換至此連接后，可以在連接2中的備份操作完成之前將此SQL腳本程序執行完畢。執行過程如下：

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> select getdate（） as tran_start

5> begin tran

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

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

8> commit

9> select getdate（） as tran_end

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

11> from fn_dblog（null，null）

12> where [current lsn]>@cur_max_lsn

13>and operation in（'LOP_BEGIN_XACT'，'LOP_COMMIT_XACT '）

14> go

tran_start

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

2021-05-14 13：24：09.913

tran_end

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

2021-05-14 13：24：10.033

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

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

0000055c：00000080：0001? LOP_BEGIN_XACT

0000055c：00000080：0004? LOP_COMMIT_XACT

由以上查詢結果，可以得知：

（1）此事務持續的時間范圍為：2021-05-14 13：24：09.913至2021-05-14 13：24：10.033;

（2）此事務的LSN范圍為：55c：80：1至55c：80：4。

再切換至連接2，執行如下文所示的命令，查詢backupset系統表：

1> select cast（name as char（20）） as name，backup_start_date，backup_finish_date

2> from msdb.dbo.backupset

3> go

name? ? ? ? ? ? ? ? ?backup_start_date? ? ? ?backup_finish_date

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

testBackup_full_1? ? 2021-05-14 13：24：06.000 2021-05-14 13：24：41.000

由以上查詢結果可以得知：

全庫備份操作持續的時間范圍為：2021-05-14 13：24：06.000至2021-05-14 13：24：41.000。

由此可以確認，連接3所執行事務的開始及提交時刻恰好在這個時間范圍之內。

在連接2中繼續執行以下命令，查詢全庫備份集所包含重做數據的LSN范圍：

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

2> from msdb.dbo.backupset

3> go

name? ? ? ? ? ? first_lsn? ? ? ? ? ? ?last_lsn? ? ? ? ? ? ? checkpoint_lsn

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

testBackup_full_1 1372000000005700001? 1372000 00013000001? ?1372000000006000153

由以上查詢結果，可以得知，此備份集的三個LSN為：

（1）first_lsn：1372：57：1，其十六進制數據為：55c：39：1;

（2）last_lsn：1372：130：1，其十六進制數據為：55c：82：1;

（3）checkpoint_lsn：1372：60：153，其十六進制數據為：55c：3c：99。

將上述結果與連接1和連接3中的查詢結果進行對比，可以發現這里的first_lsn恰好為連接1中未結束事務的開始LSN。而last_lsn大于連接3中所執行事務的結束LSN，也就是說，連接3中的事務產生的重做數據已經包含在全庫備份集中。

4? 結? 論

數據庫全庫備份包含數據和重做數據兩部分內容，重做數據即first_lsn與last_lsn之間的重做數據。SQLServer 2019全庫備份時，先執行checkpoint進程，把內存臟數據頁寫入數據文件，此操作完成后，即得到需要備份的數據庫快照，也確定了需要備份的重做數據的起始LSN，即first_lsn。數據備份完成后，再計算出需要備份的重做數據的結束LSN，即last_lsn，最后復制fist_lsn和last_lsn之間的重做數據，完成全庫備份操作。

參考文獻：

[1] HENDERSON K. The Gurus Guide to SQL Server Architecture and Internals [M].Hoboken：Pearson Education，2004.

[2] DELANEY K. Inside Microsoft SQL Server 2005 [M].Microsoft Press，2007.

[3] DELANEY K. Microsoft SQL Server 2012 Internals [M].Microsoft Press，2013.

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

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

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