SQL Server事务日志备份内容研究

摘  要：研究了在将数据库设置为完整恢复模式后，事务日志备份操作中的内容。给出SQL Server事务日志备份的概念，解释了first_lsn和last_lsn的概念，并给出SQL Server确定这两个数值的方法，指出每次事务日志备份的内容是first_lsn和last_lsn之间的重做数据。构造简洁的实验步骤，验证了第一次事务日志备份时，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  引  言

數据库备份是保证数据安全的重要措施。SQLServer数据库备份分为全库备份、事务日志备份和差异备份三种类型，全库备份的内容为数据库中的全部数据以及first_lsn和last_lsn内的全部重做数据，差异备份是自从上次备份以来修改过的区内的数据。数据库管理员应熟悉各类备份的步骤，并深刻理解各类备份操作的内容。

事务日志备份是为了恢复数据库全库备份操作完成后产生的新数据，从而使数据库恢复到故障时刻，不会因为介质故障而造成数据丢失，也可以使数据库恢复到全库备份操作后的指定时间，用以撤销某些误操作。

执行事务日志备份时，先确定要备份的重做数据范围，即确定first_lsn和last_lsn，然后备份位于first_lsn和last_lsn之间的重做数据。

本文详细介绍事务日志备份的相关概念和步骤，并用实例验证相关结论。

1  全库备份的first_lsn和last_lsn

执行全库备份时，SQL Server依序完成以下步骤：

（1）SQL Server执行checkpoint，把当前内存中被修改的数据写入磁盘文件，并记下checkpoint操作的LSN（Log Sequence Number，用于标识重做记录的序号），并作为checkpoint_lsn写入备份集文件头。

（2）计算数据库当前的MinLSN，MinLSN是checkpoint_lsn与当前最早活动事务的起始LSN之间的较小者，这个LSN称为全库备份的first_lsn。

（3）拷贝数据库中的所有数据。

（4）数据读取完毕后，根据数据库当前的最大LSN值，计算数据库将要执行的下一个事务的开始LSN（这个LSN称为last_lsn），然后将first_lsn与last_lsn记入此次备份集的文件头。

2  完整恢复模式下事务日志备份的内容

如果是执行全库备份后第一次执行事务日志备份，则first_lsn是上一次全库备份的first_lsn，即第一次事务日志备份会将其对应全库备份集中已备份的重做数据再重新备份。如果在本次事务日志备份之前已经执行过事务日志备份，则first_lsn是上一次事务日志备份的last_lsn。last_lsn是执行事务日志备份时最后一个成功结束事务的下一个事务的开始LSN。

在完整恢复模式下，从全库备份后的第二次事务日志备份开始，其内容是上次事务日志备份以来新产生的重做数据。

图1中呈现了在完整恢复模式下事务日志备份的主要内容。

3  事务日志备份内容的验证

下面创建测试数据库testBackup，然后执行一次全库备份，再执行2次事务日志备份，最后通过查询这3个备份集信息，得出有关事务日志备份内容的结论。

在连接1中执行以下操作，创建测试数据库及测试数据：

1> create database testBackup

2> go

1> use testBackup

2> go

已将数据库上下文更改为 '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

执行下面命令，将testBackup数据库设置为完整恢复模式：

1> alter database testBackup set recovery full

2> go

执行下面命令，对testBackup数据库执行全库备份：

1> backup database testBackup

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

3> with name='testBackup_full'

4> go

已為数据库 'testBackup'，文件 'testBackup' （位于文件1上）处理了176页

已为数据库 '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表执行insert操作，最后不提交，使其处于未结束状态：

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> 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，执行第1次事务日志备份。

1> backup log testBackup

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

3> with name='testBackup_log_1'

4> go

已为数据库 'testBackup'，文件 'testBackup_log' （位于文件 1 上）处理了 6 页

BACKUP LOG 成功处理了 6 页，花费 0.105 秒（0.385 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

testBackup_log_1               56000000008400178           56000000016700001

在连接3中开始一个显式事务，对t2表执行insert操作，并提交事務，然后查看此事务产生的重做数据：

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> 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，执行下面命令，进行第2次事务日志备份：

1> backup log testBackup

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

3> with name='testBackup_log_2'

4> go

已为数据库 'testBackup'，文件 'testBackup_log' （位于文件 1 上）处理了 1 页。

BACKUP LOG 成功处理了 1 页，花费 0.430 秒（0.002 MB/秒）

执行下面命令，查询第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

执行下面命令，查询各个备份集的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  结  论

由以上查询结果，可以验证四个结论：

（1）全库备份后的第1次事务日志备份内容的起始点为全库备份的first_lsn，也就是说，第1次事务日志备份会重新将全库备份中已经备份的重做记录再次备份;

（2）从第2次事务日志备份开始，每次备份的起始點为上次事务日志备份的last_lsn，从而使所有的事务日志备份内容构成一个连续的整体，在利用事务日志备份恢复数据库时，可以在恢复全库备份后，再按照其备份顺序依次恢复事务日志备份;

（3）执行事务日志备份时，不会导致checkpoint执行;

（4）每次事务日志备份的database_backup_lsn未发生变化，都是其全库备份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数据库技术内幕 [M].北京：中国铁道出版社，2012.

作者简介：李爱武（1969.07—），男，汉族，河北肃宁人，副教授，理学硕士，研究方向：数据库技术、数据分析。