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—），男，汉族，河北肃宁人，副教授，理学硕士，研究方向：数據库技术、数据分析。