【技术视界】巧用日志文件，轻松恢复sql server数据库数据

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

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

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

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

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

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

↓

数据库类型中以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是将一个操作给细分成很多个子操作，而这些子操作都存放在一个块中。所以，一个块中的所有记录组成一个完整的操作。

2、LDF文件的操作码

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

Code

Operation

Code

Operation

0

LOP_NULL

142

LOP_CREATE_STREAMFILE

1

LOP_FORMAT_PAGE

143

LOP_MIGRATE_LOCKS

2

LOP_INSERT_ROWS

144

LOP_DROP_STREAMFILE

3

LOP_DELETE_ROWS

145

LOP_FS_DOWNLEVEL_OP

4

LOP_MODIFY_ROW

148

LOP_BP_DBVER

5

LOP_MODIFY_HEADER

150

LOP_BEGIN_CKPT

6

LOP_MODIFY_COLUMNS

152

LOP_XACT_CKPT

7

LOP_SET_BITS

153

LOP_END_CKPT

9

LOP_DELTA_SYSIND

154

LOP_BUF_WRITE

10

LOP_SET_FREE_SPACE

155

LOP_IDENTITY_TYPE

11

LOP_DELETE_SPLIT

160

LOP_BEGIN_RECOVERY

12

LOP_UNDO_DELETE_SPLIT

161

LOP_END_RECOVERY

13

LOP_EXPUNGE_ROWS

173

LOP_CREATE_INDEX

16

LOP_FILE_HDR_MODIFY

174

LOP_DROP_INDEX

17

LOP_CLEAR_GAM_BITS

176

LOP_CREATE_ALLOCCHAIN

18

LOP_COUNT_DELTA

180

LOP_CREATE_FTCAT

19

LOP_ROOT_CHANGE

181

LOP_DROP_FTCAT

20

LOP_COMPRESSION_INFO

200

LOP_REPL_COMMAND

21

LOP_ENCRYPT_PAGE

201

LOP_BEGIN_UPDATE

127

LOP_MAX_PAGE_OP

202

LOP_END_UPDATE

128

LOP_BEGIN_XACT

203

LOP_TEXT_POINTER

129

LOP_COMMIT_XACT

204

LOP_TEXT_INFO_BEGIN

130

LOP_ABORT_XACT

205

LOP_TEXT_INFO_END

131

LOP_PREP_XACT

206

LOP_REPL_NOOP

132

LOP_MARK_SAVEPOINT

207

LOP_TEXT_VALUE

133

LOP_FORGET_XACT

210

LOP_MAX_REPL_OP

134

LOP_CREATE_FILE

211

LOP_SHRINK_NOOP

135

LOP_DROP_FILE

212

LOP_FILESTREAM_INFO_BEGIN

136

LOP_MARK_DDL

213

LOP_FILESTREAM_INFO_END

137

LOP_HOBT_DDL

214

LOP_BULK_EXT_ALLOCATION

138

LOP_IDENT_NEWVAL

215

LOP_SECURITY_OP

139

LOP_IDENT_SENTVAL

216

LOP_PAGE_REENCRYPT

140

LOP_HOBT_DELTA

220

LOP_RANGE_INSERT

141

LOP_LOCK_XACT

3、LDF文件恢复方案

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

点击查看大图

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

（二）现有处理日志文件的几种方式

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

1、SQL Server自带命令

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

USE[BBBBBBBBBB]

GO

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

数据恢复情况

2、第三方软件SysTools

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

数据恢复情况

3、效率源QuicklyAnalysis软件

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

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

数据恢复情况

（三）恢复效果对比分析

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

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数据库取证分析系列产品中，其他类型的数据库恢复方案也会在之后的【技术视界】栏目中陆续推出，感兴趣的小伙伴请多多关注我们。”

本期投稿：刘涛

本期编辑：源妹

打个广告

效率源DBF6300数据库取证分析系统是业内首款数据库取证分析产品，可以对大部分类型的数据库进行恢复取证分析工作。对数据库被删除、数据库损坏、文件系统损坏、无数据库密码等情况，用户均可在DBF6300中对恢复的数据进行排序、筛选、统计、关键字查询、可视化查询、SQL语句查询等操作，快速进行数据库的恢复提取、查询、统计和分析，无需使用多个软件即可完成案件中的数据库取证分析工作，使用更便捷。

点击试用

想了解更多产品动态、技术资讯

赶快星标 置顶我吧

【扩展阅读】

单兵携带+免拆机取证，现场勘验一个ta就够了！

【新品发布】业内首款数据库取证分析产品上线，邀你免费体验

【技术实战】如何通过日志备份恢复SQL Server数据库删除数据？

【技术视界】网站防入侵，如何检测webshell？

南京兆柏数据恢复中心 南京兆柏数据恢复中心 南京兆柏数据恢复中心