首頁 > 關于效率源 > 新聞和事件 > 技術視界
【技術視界】巧用日志文件,輕松恢復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數據庫的文件分類、備份方式、恢復模式等內容,在之前的技術文章里面已經詳細介紹過,小編這里就不贅述了。詳情請戳)

(一)日志文件底層結構分析

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數據庫取證分析系列產品中,其他類型的數據庫恢復方案也會在之后的【技術視界】欄目中陸續推出,感興趣的小伙伴請多多關注我們。

本期投稿:劉濤
本期編輯:源妹