SQL Server 2019全庫(kù)備份的過程與內(nèi)容研究

摘? 要：首先給出SQL Server全庫(kù)備份內(nèi)容的結(jié)論，詳細(xì)列出SQL Server執(zhí)行全庫(kù)備份時(shí)要執(zhí)行的各個(gè)任務(wù)，指出全庫(kù)備份中不同范圍的事務(wù)日志備份在執(zhí)行數(shù)據(jù)庫(kù)恢復(fù)時(shí)的不同作用，列舉全庫(kù)備份操作中容易忽視和混淆的幾個(gè)注意事項(xiàng)，解釋了涉及數(shù)據(jù)庫(kù)備份的幾個(gè)LSN屬性，最后以實(shí)例說明了SQL Server 2019全庫(kù)備份的first_lsn和last_lsn產(chǎn)生的過程。

關(guān)鍵詞：SQL Server 2019;數(shù)據(jù)庫(kù)全庫(kù)備份;備份內(nèi)容;備份過程

中圖分類號(hào)：TP311? ? ? 文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：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? 引? 言

數(shù)據(jù)庫(kù)備份是保證數(shù)據(jù)安全的重要措施。SQL Server數(shù)據(jù)庫(kù)備份分為全庫(kù)備份、事務(wù)日志備份和差異備份三種類型，數(shù)據(jù)庫(kù)管理員應(yīng)熟悉各類備份的步驟，并深刻理解各類備份操作的內(nèi)容。

全庫(kù)備份的內(nèi)容是執(zhí)行備份操作時(shí)，拷貝整個(gè)數(shù)據(jù)庫(kù)中的全部數(shù)據(jù)外加部分事務(wù)日志數(shù)據(jù)。全庫(kù)備份是執(zhí)行差異備份或事務(wù)日志備份的基礎(chǔ)，若要執(zhí)行差異備份或事務(wù)日志備份，必須先執(zhí)行全庫(kù)備份。理解全庫(kù)備份的原理也是理解其他備份方式的基礎(chǔ)。

本文研究了全庫(kù)備份的步驟，解釋了涉及全庫(kù)備份的幾個(gè)LSN屬性，并且用實(shí)例驗(yàn)證了相關(guān)結(jié)論。

1? 全庫(kù)備份的內(nèi)容

SQL Server的全庫(kù)備份命令很簡(jiǎn)潔，假定數(shù)據(jù)庫(kù)名稱為testBackup，執(zhí)行以下SQL命令即可對(duì)其進(jìn)行全庫(kù)備份：

1> backup database testBackup

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

3> with name='testBackup_full_1'

4> go

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

執(zhí)行上述命令進(jìn)行全庫(kù)備份時(shí)，SQL Server依次執(zhí)行以下操作：

（1）執(zhí)行checkpoint，把當(dāng)前內(nèi)存中被修改的數(shù)據(jù)寫入磁盤文件，把checkpoint操作的LSN作為checkpoint_lsn寫入備份集文件頭（LSN即Log Sequence Number，表示數(shù)據(jù)庫(kù)操作的重做日志序列號(hào)）;

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

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

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

（5）讀取數(shù)據(jù)庫(kù)重做日志文件中位于first_lsn與last_lsn之間的重做記錄，即要讀取的重做記錄的LSN應(yīng)滿足條件：first_lsn≤LSN

（6）將此次備份集文件頭中的有關(guān)信息，如fisrt_lsn、last_lsn、備份操作的開始及結(jié)束時(shí)間等數(shù)據(jù)，記入msdb數(shù)據(jù)庫(kù)的dbo.bacupset系統(tǒng)表。

以上步驟可以用圖1來簡(jiǎn)單描述。

由以上步驟可知，全庫(kù)備份操作產(chǎn)生的備份集主要包括以下兩部分?jǐn)?shù)據(jù)：

（1）數(shù)據(jù)庫(kù)中所有包含數(shù)據(jù)的區(qū);

（2）first_lsn與last_lsn之間的重做數(shù)據(jù)。

first_lsn與last_lsn之間的重做數(shù)據(jù)用于直接以全庫(kù)備份的形式恢復(fù)數(shù)據(jù)庫(kù)，即不涉及應(yīng)用差異備份和事務(wù)日志備份來恢復(fù)數(shù)據(jù)庫(kù)。

若在全庫(kù)備份操作開始時(shí)，存在未完成的事務(wù)，則first_lsn

（1）first_lsn與checkpoint_lsn之間的重做數(shù)據(jù)用于在數(shù)據(jù)庫(kù)恢復(fù)時(shí)，回滾在備份完成時(shí)尚未提交的事務(wù);

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

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

對(duì)于全庫(kù)備份集中的數(shù)據(jù)及重做記錄，還要注意以下幾點(diǎn)：

（1）對(duì)于繁忙的大容量數(shù)據(jù)庫(kù)，全庫(kù)備份操作要拷貝的區(qū)在讀取GAM和SGAM數(shù)據(jù)頁(yè)時(shí)就已確定，在讀取包含數(shù)據(jù)的區(qū)以及之后讀取重做日志階段，如果因?yàn)樾聰?shù)據(jù)的產(chǎn)生導(dǎo)致分配了新區(qū)，這些區(qū)不會(huì)包含在全庫(kù)備份內(nèi)容中。

（2）如果在備份操作結(jié)束前，已經(jīng)讀取的數(shù)據(jù)頁(yè)內(nèi)容又發(fā)生了改變，這些新數(shù)據(jù)也不會(huì)被重新讀取。也就是說，全庫(kù)備份中的數(shù)據(jù)一般是備份操作開始時(shí)的數(shù)據(jù)庫(kù)快照內(nèi)容，而不是備份操作完成時(shí)的數(shù)據(jù)庫(kù)快照內(nèi)容。

（3）全庫(kù)備份中的重做數(shù)據(jù)并不是從備份操作開始到結(jié)束之間產(chǎn)生的重做數(shù)據(jù)。如果備份操作開始之前已經(jīng)進(jìn)行了若干事務(wù)，則first_lsn顯然會(huì)小于備份時(shí)的LSN，這種情況下，全庫(kù)備份會(huì)包含備份操作開始之前產(chǎn)生的重做記錄。

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

（5）雖然全庫(kù)備份中包含了事務(wù)日志備份，但是全庫(kù)備份操作并不會(huì)截?cái)嗳罩疚募?，只有事?wù)日志備份操作才會(huì)截?cái)嗳罩疚募?/p>

2? 全庫(kù)備份集的幾個(gè)LSN屬性

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

checkpoint_lsn是備份操作開始時(shí)，執(zhí)行checkpoint操作的開始lsn，在使用備份文件恢復(fù)數(shù)據(jù)庫(kù)時(shí)，先把數(shù)據(jù)文件恢復(fù)到相應(yīng)目錄后，再對(duì)數(shù)據(jù)文件應(yīng)用重做日志，這個(gè)LSN用于指定所應(yīng)用重做日志的起始點(diǎn)。

database_backup_lsn指最近一次全庫(kù)備份的checkpoint_lsn。一般在差異備份時(shí)才會(huì)有用。對(duì)于差異備份操作創(chuàng)建的備份集，它的database_backup_lsn屬性就是其上次全庫(kù)備份操作的checkpoint_lsn，差異備份的內(nèi)容是執(zhí)行上次全庫(kù)備份以來發(fā)生變化的區(qū)，而統(tǒng)計(jì)變化的起始點(diǎn)就是上次全庫(kù)備份開始時(shí)的checkpoint_lsn。差異備份的database_backup_lsn的作用是確認(rèn)差異備份集文件與全庫(kù)備份集文件是否匹配。

如果是第一次執(zhí)行全庫(kù)備份，則其database_backup_lsn為0。

3? 驗(yàn)證全庫(kù)備份集的first_lsn及l(fā)ast_lsn

下面的實(shí)驗(yàn)過程主要驗(yàn)證全庫(kù)備份集的first_lsn與last_lsn是如何確定的。主要實(shí)驗(yàn)步驟為：

（1）創(chuàng)建測(cè)試數(shù)據(jù)庫(kù)，為了使全庫(kù)備份操作能夠持續(xù)幾十秒鐘，在簡(jiǎn)單恢復(fù)模式下為其添加400 MB數(shù)據(jù)。設(shè)置簡(jiǎn)單恢復(fù)模式的目的在于添加數(shù)據(jù)時(shí)，可以重用重做文件中的VLF;

（2）在測(cè)試數(shù)據(jù)庫(kù)中創(chuàng)建兩個(gè)測(cè)試表t1與t2;

（3）開啟3個(gè)連接;

（4）在連接1中，執(zhí)行SQL腳本程序，在其中開始一個(gè)事務(wù)，為t1表添加記錄，并使其處于未結(jié)束狀態(tài)，最后得出此事務(wù)中各個(gè)操作的LSN號(hào)以及事務(wù)的開始時(shí)間，假設(shè)事務(wù)開始的LSN為lsn#1;

（5）在連接2中，執(zhí)行全庫(kù)備份，在全庫(kù)備份執(zhí)行過程中，切換至連接3;

（6）在連接3中，執(zhí)行SQL腳本程序（這個(gè)腳本程序應(yīng)預(yù)先準(zhǔn)備好，切換到連接3后，可以馬上執(zhí)行），在其中開始一個(gè)事務(wù)，為t2表添加記錄，然后提交事務(wù)，最后得出此事務(wù)各個(gè)操作的LSN號(hào)以及事務(wù)的開始、結(jié)束時(shí)間，切換至連接2;

（7）在連接2中，等待全庫(kù)備份操作完成后，通過查詢msdb數(shù)據(jù)庫(kù)中的backupset系統(tǒng)表，得到備份集的first_lsn、last_lsn以及備份操作的開始、結(jié)束時(shí)間。確認(rèn)first_lsn即連接1中得到的lsn#1，而備份集的last_lsn是連接3中所執(zhí)行事務(wù)的下一個(gè)事務(wù)的開始LSN。

接著按照以上步驟完成實(shí)驗(yàn)：

（1）在連接1中，創(chuàng)建測(cè)試數(shù)據(jù)庫(kù)，并將其設(shè)置為簡(jiǎn)單恢復(fù)模式，從而在為其添加大量數(shù)據(jù)時(shí)，不會(huì)使重做日志文件增長(zhǎng)過大。

1> create database testBackup

2> go

1> alter database testBackup set recovery simple

2> go

（2）執(zhí)行以下命令，為testBackup數(shù)據(jù)庫(kù)添加大約400 MB數(shù)據(jù)。

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）創(chuàng)建兩個(gè)測(cè)試表t1及t2。

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

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

3> go

（4）將testBackup數(shù)據(jù)庫(kù)設(shè)置為完整恢復(fù)模式：

1> alter database testBackup set recovery full

2> go

（5）繼續(xù)執(zhí)行如下文所示的SQL腳本程序，在連接1中開始一個(gè)事務(wù)，并使其處于未結(jié)束狀態(tài)，最后查詢事務(wù)的開始時(shí)間，以及事務(wù)中各個(gè)操作產(chǎn)生日志記錄的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

由上述查詢結(jié)果，可以得知：

（1）此事務(wù)的開始時(shí)刻為：2021-05-14 13：20：57.700;

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

在連接2中先刪除backupset系統(tǒng)表中的記錄，然后對(duì)測(cè)試數(shù)據(jù)庫(kù)進(jìn)行全庫(kù)備份操作。因?yàn)閿?shù)據(jù)庫(kù)中包含了400 MB數(shù)據(jù)，此備份操作一般會(huì)持續(xù)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后，在其中執(zhí)行以下SQL腳本程序，開始一個(gè)事務(wù)，為t2表添加記錄后提交事務(wù)，最后得出事務(wù)的開始、結(jié)束時(shí)間，以及事務(wù)中各個(gè)操作產(chǎn)生的日志記錄的LSN。這里的SQL腳本程序需要提前準(zhǔn)備好，保證切換至此連接后，可以在連接2中的備份操作完成之前將此SQL腳本程序執(zhí)行完畢。執(zhí)行過程如下：

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> 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

由以上查詢結(jié)果，可以得知：

（1）此事務(wù)持續(xù)的時(shí)間范圍為：2021-05-14 13：24：09.913至2021-05-14 13：24：10.033;

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

再切換至連接2，執(zhí)行如下文所示的命令，查詢backupset系統(tǒng)表：

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

由以上查詢結(jié)果可以得知：

全庫(kù)備份操作持續(xù)的時(shí)間范圍為：2021-05-14 13：24：06.000至2021-05-14 13：24：41.000。

由此可以確認(rèn)，連接3所執(zhí)行事務(wù)的開始及提交時(shí)刻恰好在這個(gè)時(shí)間范圍之內(nèi)。

在連接2中繼續(xù)執(zhí)行以下命令，查詢?nèi)珟?kù)備份集所包含重做數(shù)據(jù)的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

由以上查詢結(jié)果，可以得知，此備份集的三個(gè)LSN為：

（1）first_lsn：1372：57：1，其十六進(jìn)制數(shù)據(jù)為：55c：39：1;

（2）last_lsn：1372：130：1，其十六進(jìn)制數(shù)據(jù)為：55c：82：1;

（3）checkpoint_lsn：1372：60：153，其十六進(jìn)制數(shù)據(jù)為：55c：3c：99。

將上述結(jié)果與連接1和連接3中的查詢結(jié)果進(jìn)行對(duì)比，可以發(fā)現(xiàn)這里的first_lsn恰好為連接1中未結(jié)束事務(wù)的開始LSN。而last_lsn大于連接3中所執(zhí)行事務(wù)的結(jié)束LSN，也就是說，連接3中的事務(wù)產(chǎn)生的重做數(shù)據(jù)已經(jīng)包含在全庫(kù)備份集中。

4? 結(jié)? 論

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

參考文獻(xiàn)：

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

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