首页 > 关于效率源 > 新闻和事件 > 技术视界
【技术视界】巧用日志文件,轻松恢复SQL Server数据库数据
2019年08月21日

由于数据库的操作记录和
部分删除数据会记录在日志文件中,
所以在数据库的恢复中
日志扮演着重要的角色。

一、数据库日志文件的分类

数据库的日志文件一般分为两大类:文本存储、二进制存储

1)对于文本存储的日志文件,仅仅需要找到该日志文件的存储位置即可,它一般是以纯文本的格式存储,可以直接查看。

2)而对于二进制存储结构的日志文件的解析就没那么简单了,在读取的过程中有时会出现乱码或不能识别等情况,这就需要借助一些其他的工具和手段来完成。

常见数据库二进制日志文件如下所示

1566372569273047642.jpg

数据库类型中以SQL Server使用最为广泛,我们通过对SQL Server二进制日志文件(LDF文件)底层结构的分析,提出一种基于SQL Server二进制日志文件的恢复思路和方法,并将其集成于工具,能够有效恢复目标数据库中的操作记录内容,在本文和大家交流分享。

二、SQL Server日志文件

SQL Server 使用Write-ahead logging (WAL)方式保证任何数据变更的日志要比数据变更先发生。在完整恢复模式下,对数据库中任何对象的变更操作都会被记录在日志中。注意是所有的数据对象,包括:tables, views, stored procedures, users, permissions等。

(关于SQL Server数据库的文件分类、备份方式、恢复模式等内容,在之前的技术文章里面已经详细介绍过,小编这里就不赘述了。详情请戳:【技术实战】如何通过日志备份恢复SQL Server数据库删除数据?

(一)日志文件底层结构分析

1、LDF文件的整体结构

LDF文件的整体结构由一个文件管理块+若干个VLF组成。VLF(最少2个,最多16个)创建时,日志文件如果大于1MB,VLF的大小总是64K的倍数。

VLF的结构与整体结构存在很大的相似性,是由一个VLF管理+若干个块组成。块中就记录了所有的操作记录。

这里我们简要的介绍一下 VLF 。

VLF可以粗略的分为活动VLF和不活动VLF两大类,也可细致的分为↓↓↓

A、活动(Active):在VLF 上存储的任意一条LSN(日志序列编号,是事务日志里面每条记录的编号)是活动时,则VLF为活动状态;
B、可恢复(Recoverable):VLF是不活动的,VLF上不包含活动LSN,但未被截断;
C、可重用(Reusable):VLF是不活动的,VLF上不包含活动LSN,已被截断,可重用;
D、未使用(Unused):VLF是不活动的,并且还未被使用过。

我们通过研究发现,SQL Server是将一个操作给细分成很多个子操作,而这些子操作都存放在一个块中。所以,一个块中的所有记录组成一个完整的操作。

E2.jpg

2、LDF文件的操作码

我们发现,在LDF文件中存在非常之多的操作码,不同的操作码表示当前LSN所做的操作。我们将这些操作码及对应操作进行了归纳总结,并制作成具体的操作码对应表,如下所示:

E8.png

3、LDF文件恢复方案

对于记录被反复修改,可通过对LDF文件进行底层分析,恢复出该条记录,具体的恢复方案如下图所示。

1566372768118019450.jpg

若该被删除记录真实存在于LDF文件中,则可以有效的从删除记录中提取到该记录,然后根据上述的规则,从修改记录中找出该条记录被修改前的记录内容。

(二)现有处理日志文件的几种方式

为了更进一步验证【LDF文件恢复方案】的可行性和有效性,我们将现有处理日志文件的几种方式进行对比分析测试,具体情况如下。

1、SQL Server自带命令

使用SQL Server自带的fn_dblog()查询日志记录,该命令可查询出最近操作记录。(标红处为本次测试的数据库名,可更换)

USE[BBBBBBBBBB]
GO
SELECT*FROM[sys].[fn_dblog](NULL,NULL)

E4.png

数据恢复情况

2、第三方软件SysTools

常见的第三方恢复软件如:SysTools SQL Log Analyzer,该软件需要结合MDF文件才能解析出结果。

1566372874322001698.jpg

数据恢复情况

3、效率源QuicklyAnalysis软件

基于对【LDF文件恢复方案】的研究,我们将技术集成于最新研发的一款日志分析软件——QuicklyAnalysis,可快速解析数据。

下图为效率源QuicklyAnalysis.exe分析结果。

1566372919306001732.jpg

数据恢复情况

(三)恢复效果对比分析

从上文中的数据恢复截图我们不难看出,三种处理日志文件的方式在恢复效果上存在较大差异:

1)SQL Server数据库自带命令的查询结果是很少的,只能查询到最近的内容,在数量上有很大的局限性;
2)第三方软件SysTools SQL Log Analyzer可以恢复出操作数据,但数据量也有限;
3)效率源QuicklyAnalysis软件通过对底层结构的分析,可直接解析LDF文件中所包含的日志记录信息,也可结合MDF文件,恢复出完整的数据操作记录,以及删除的数据记录;
4)另外,通过分析我们发现:
如果只有LDF文件,则可以恢复出该数据库中的删除数据和插入数据;如果同时有MDF和LDF两个文件,则可以恢复出该数据库中的删除数据记录、插入数据记录、更新前数据记录、更新后数据记录。

三、要点总结

基于LDF文件进行数据恢复的基本原理
用户在对SQL Server数据库进行操作的时候,会将操作本身和操作的数据记录在LDF文件中,并且该文件的大小是没有限制的,会随着时间的推移增大,并不会出现循环覆盖的现象。所以,也为从其中恢复出大量的数据提供了理论的基础。


面对这种情况,本文章提出一种基于SQL Server 二进制日志文件(LDF文件)的数据恢复方法,该方法能够有效、完整地恢复出目标数据库中的操作记录。

剧透:效率源QuicklyAnalysis软件后面会加入到效率源DBF数据库千亿国际平台分析系列产品中,其他类型的数据库恢复方案也会在之后的【技术视界】栏目中陆续推出,感兴趣的小伙伴请多多关注我们。

本期投稿:刘涛
本期编辑:源妹