【技術視界】巧用日志文件，輕松恢復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？

南京兆柏數據恢復中心 南京兆柏數據恢復中心 南京兆柏數據恢復中心