【技術(shù)視界】巧用日志文件，輕松恢復(fù)sql server數(shù)據(jù)庫(kù)數(shù)據(jù)

由于數(shù)據(jù)庫(kù)的操作記錄和部分刪除數(shù)據(jù)會(huì)記錄在日志文件中，所以在數(shù)據(jù)庫(kù)的恢復(fù)中日志扮演著重要的角色。

一、數(shù)據(jù)庫(kù)日志文件的分類

數(shù)據(jù)庫(kù)的日志文件一般分為兩大類：文本存儲(chǔ)、二進(jìn)制存儲(chǔ)。

1）對(duì)于文本存儲(chǔ)的日志文件，僅僅需要找到該日志文件的存儲(chǔ)位置即可，它一般是以純文本的格式存儲(chǔ)，可以直接查看。

2）而對(duì)于二進(jìn)制存儲(chǔ)結(jié)構(gòu)的日志文件的解析就沒(méi)那么簡(jiǎn)單了，在讀取的過(guò)程中有時(shí)會(huì)出現(xiàn)亂碼或不能識(shí)別等情況，這就需要借助一些其他的工具和手段來(lái)完成。

常見(jiàn)數(shù)據(jù)庫(kù)二進(jìn)制日志文件如下所示

↓

數(shù)據(jù)庫(kù)類型中以SQL Server使用最為廣泛，我們通過(guò)對(duì)SQL Server二進(jìn)制日志文件（LDF文件）底層結(jié)構(gòu)的分析，提出一種基于SQL Server二進(jìn)制日志文件的恢復(fù)思路和方法，并將其集成于工具，能夠有效恢復(fù)目標(biāo)數(shù)據(jù)庫(kù)中的操作記錄內(nèi)容，在本文和大家交流分享。

二、SQL Server日志文件

SQL Server 使用Write-ahead logging (WAL)方式保證任何數(shù)據(jù)變更的日志要比數(shù)據(jù)變更先發(fā)生。在完整恢復(fù)模式下，對(duì)數(shù)據(jù)庫(kù)中任何對(duì)象的變更操作都會(huì)被記錄在日志中。注意是所有的數(shù)據(jù)對(duì)象，包括：tables, views, stored procedures, users, permissions等。

（關(guān)于SQL Server數(shù)據(jù)庫(kù)的文件分類、備份方式、恢復(fù)模式等內(nèi)容，在之前的技術(shù)文章里面已經(jīng)詳細(xì)介紹過(guò)，小編這里就不贅述了。詳情請(qǐng)戳：【技術(shù)實(shí)戰(zhàn)】如何通過(guò)日志備份恢復(fù)SQL Server數(shù)據(jù)庫(kù)刪除數(shù)據(jù)？）

（一）日志文件底層結(jié)構(gòu)分析

1、LDF文件的整體結(jié)構(gòu)

LDF文件的整體結(jié)構(gòu)由一個(gè)文件管理塊+若干個(gè)VLF組成。VLF（最少2個(gè)，最多16個(gè)）創(chuàng)建時(shí)，日志文件如果大于1MB，VLF的大小總是64K的倍數(shù)。

VLF的結(jié)構(gòu)與整體結(jié)構(gòu)存在很大的相似性，是由一個(gè)VLF管理+若干個(gè)塊組成。塊中就記錄了所有的操作記錄。

這里我們簡(jiǎn)要的介紹一下 VLF 。

VLF可以粗略的分為活動(dòng)VLF和不活動(dòng)VLF兩大類，也可細(xì)致的分為↓↓↓

A、活動(dòng)（Active）：在VLF 上存儲(chǔ)的任意一條LSN（日志序列編號(hào)，是事務(wù)日志里面每條記錄的編號(hào)）是活動(dòng)時(shí)，則VLF為活動(dòng)狀態(tài)；

B、可恢復(fù)（Recoverable）：VLF是不活動(dòng)的，VLF上不包含活動(dòng)LSN，但未被截?cái)啵?/p>

C、可重用（Reusable）：VLF是不活動(dòng)的，VLF上不包含活動(dòng)LSN，已被截?cái)啵芍赜茫?/p>

D、未使用（Unused）：VLF是不活動(dòng)的，并且還未被使用過(guò)。

我們通過(guò)研究發(fā)現(xiàn)，SQL Server是將一個(gè)操作給細(xì)分成很多個(gè)子操作，而這些子操作都存放在一個(gè)塊中。所以，一個(gè)塊中的所有記錄組成一個(gè)完整的操作。

2、LDF文件的操作碼

我們發(fā)現(xiàn)，在LDF文件中存在非常之多的操作碼，不同的操作碼表示當(dāng)前LSN所做的操作。我們將這些操作碼及對(duì)應(yīng)操作進(jìn)行了歸納總結(jié)，并制作成具體的操作碼對(duì)應(yīng)表，如下所示：

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文件恢復(fù)方案

對(duì)于記錄被反復(fù)修改，可通過(guò)對(duì)LDF文件進(jìn)行底層分析，恢復(fù)出該條記錄，具體的恢復(fù)方案如下圖所示。

點(diǎn)擊查看大圖

若該被刪除記錄真實(shí)存在于LDF文件中，則可以有效的從刪除記錄中提取到該記錄，然后根據(jù)上述的規(guī)則，從修改記錄中找出該條記錄被修改前的記錄內(nèi)容。

（二）現(xiàn)有處理日志文件的幾種方式

為了更進(jìn)一步驗(yàn)證【LDF文件恢復(fù)方案】的可行性和有效性，我們將現(xiàn)有處理日志文件的幾種方式進(jìn)行對(duì)比分析測(cè)試，具體情況如下。

1、SQL Server自帶命令

使用SQL Server自帶的fn_dblog()查詢?nèi)罩居涗洠撁羁刹樵兂鲎罱僮饔涗洝＃?biāo)紅處為本次測(cè)試的數(shù)據(jù)庫(kù)名，可更換）

USE[BBBBBBBBBB]

GO

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

數(shù)據(jù)恢復(fù)情況

2、第三方軟件SysTools

常見(jiàn)的第三方恢復(fù)軟件如：SysTools SQL Log Analyzer，該軟件需要結(jié)合MDF文件才能解析出結(jié)果。

數(shù)據(jù)恢復(fù)情況

3、效率源QuicklyAnalysis軟件

基于對(duì)【LDF文件恢復(fù)方案】的研究，我們將技術(shù)集成于最新研發(fā)的一款日志分析軟件——QuicklyAnalysis，可快速解析數(shù)據(jù)。

下圖為效率源QuicklyAnalysis.exe分析結(jié)果。

數(shù)據(jù)恢復(fù)情況

（三）恢復(fù)效果對(duì)比分析

從上文中的數(shù)據(jù)恢復(fù)截圖我們不難看出，三種處理日志文件的方式在恢復(fù)效果上存在較大差異：

1）SQL Server數(shù)據(jù)庫(kù)自帶命令的查詢結(jié)果是很少的，只能查詢到最近的內(nèi)容，在數(shù)量上有很大的局限性；

2）第三方軟件SysTools SQL Log Analyzer可以恢復(fù)出操作數(shù)據(jù)，但數(shù)據(jù)量也有限；

3）效率源QuicklyAnalysis軟件通過(guò)對(duì)底層結(jié)構(gòu)的分析，可直接解析LDF文件中所包含的日志記錄信息，也可結(jié)合MDF文件，恢復(fù)出完整的數(shù)據(jù)操作記錄，以及刪除的數(shù)據(jù)記錄；

4）另外，通過(guò)分析我們發(fā)現(xiàn)：

如果只有LDF文件，則可以恢復(fù)出該數(shù)據(jù)庫(kù)中的刪除數(shù)據(jù)和插入數(shù)據(jù)；如果同時(shí)有MDF和LDF兩個(gè)文件，則可以恢復(fù)出該數(shù)據(jù)庫(kù)中的刪除數(shù)據(jù)記錄、插入數(shù)據(jù)記錄、更新前數(shù)據(jù)記錄、更新后數(shù)據(jù)記錄。

三、要點(diǎn)總結(jié)

基于LDF文件進(jìn)行數(shù)據(jù)恢復(fù)的基本原理

用戶在對(duì)SQL Server數(shù)據(jù)庫(kù)進(jìn)行操作的時(shí)候，會(huì)將操作本身和操作的數(shù)據(jù)記錄在LDF文件中，并且該文件的大小是沒(méi)有限制的，會(huì)隨著時(shí)間的推移增大，并不會(huì)出現(xiàn)循環(huán)覆蓋的現(xiàn)象。所以，也為從其中恢復(fù)出大量的數(shù)據(jù)提供了理論的基礎(chǔ)。

“

面對(duì)這種情況，本文章提出一種基于SQL Server 二進(jìn)制日志文件（LDF文件）的數(shù)據(jù)恢復(fù)方法，該方法能夠有效、完整地恢復(fù)出目標(biāo)數(shù)據(jù)庫(kù)中的操作記錄。

劇透：效率源QuicklyAnalysis軟件后面會(huì)加入到效率源DBF數(shù)據(jù)庫(kù)取證分析系列產(chǎn)品中，其他類型的數(shù)據(jù)庫(kù)恢復(fù)方案也會(huì)在之后的【技術(shù)視界】欄目中陸續(xù)推出，感興趣的小伙伴請(qǐng)多多關(guān)注我們。”

本期投稿：劉濤

本期編輯：源妹

打個(gè)廣告

效率源DBF6300數(shù)據(jù)庫(kù)取證分析系統(tǒng)是業(yè)內(nèi)首款數(shù)據(jù)庫(kù)取證分析產(chǎn)品，可以對(duì)大部分類型的數(shù)據(jù)庫(kù)進(jìn)行恢復(fù)取證分析工作。對(duì)數(shù)據(jù)庫(kù)被刪除、數(shù)據(jù)庫(kù)損壞、文件系統(tǒng)損壞、無(wú)數(shù)據(jù)庫(kù)密碼等情況，用戶均可在DBF6300中對(duì)恢復(fù)的數(shù)據(jù)進(jìn)行排序、篩選、統(tǒng)計(jì)、關(guān)鍵字查詢、可視化查詢、SQL語(yǔ)句查詢等操作，快速進(jìn)行數(shù)據(jù)庫(kù)的恢復(fù)提取、查詢、統(tǒng)計(jì)和分析，無(wú)需使用多個(gè)軟件即可完成案件中的數(shù)據(jù)庫(kù)取證分析工作，使用更便捷。

點(diǎn)擊試用

想了解更多產(chǎn)品動(dòng)態(tài)、技術(shù)資訊

趕快星標(biāo) 置頂我吧

【擴(kuò)展閱讀】

單兵攜帶+免拆機(jī)取證，現(xiàn)場(chǎng)勘驗(yàn)一個(gè)ta就夠了！

【新品發(fā)布】業(yè)內(nèi)首款數(shù)據(jù)庫(kù)取證分析產(chǎn)品上線，邀你免費(fèi)體驗(yàn)

【技術(shù)實(shí)戰(zhàn)】如何通過(guò)日志備份恢復(fù)SQL Server數(shù)據(jù)庫(kù)刪除數(shù)據(jù)？

【技術(shù)視界】網(wǎng)站防入侵，如何檢測(cè)webshell？

南京兆柏?cái)?shù)據(jù)恢復(fù)中心 南京兆柏?cái)?shù)據(jù)恢復(fù)中心 南京兆柏?cái)?shù)據(jù)恢復(fù)中心