【巧用日志文件】恢復(fù)sql server數(shù)據(jù)庫數(shù)據(jù)

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

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

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

常見數(shù)據(jù)庫二進(jìn)制日志文件如下所示↓數(shù)據(jù)庫類型中以SQL Server使用最為廣泛，我們通過對SQL Server二進(jìn)制日志文件（LDF文件）底層結(jié)構(gòu)的分析，提出一種基于SQL Server二進(jìn)制日志文件的恢復(fù)思路和方法，并將其集成于工具，能夠有效恢復(fù)目標(biāo)數(shù)據(jù)庫中的操作記錄內(nèi)容，在本文和大家交流分享。二、SQL Server日志文件SQL Server 使用Write-ahead logging (WAL)方式保證任何數(shù)據(jù)變更的日志要比數(shù)據(jù)變更先發(fā)生。在完整恢復(fù)模式下，對數(shù)據(jù)庫中任何對象的變更操作都會被記錄在日志中。注意是所有的數(shù)據(jù)對象，包括：tables, views, stored procedures, users, permissions等。（關(guān)于SQL Server數(shù)據(jù)庫的文件分類、備份方式、恢復(fù)模式等內(nèi)容，在之前的技術(shù)文章里面已經(jīng)詳細(xì)介紹過，小編這里就不贅述了。詳情請戳：【技術(shù)實(shí)戰(zhàn)】如何通過日志備份恢復(fù)SQL Server數(shù)據(jù)庫刪除數(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è)塊組成。塊中就記錄了所有的操作記錄。這里我們簡要的介紹一下 VLF 。VLF可以粗略的分為活動(dòng)VLF和不活動(dòng)VLF兩大類，也可細(xì)致的分為↓↓↓

A、活動(dòng)（Active）：在VLF 上存儲的任意一條LSN（日志序列編號，是事務(wù)日志里面每條記錄的編號）是活動(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)的，并且還未被使用過。我們通過研究發(fā)現(xiàn)，SQL Server是將一個(gè)操作給細(xì)分成很多個(gè)子操作，而這些子操作都存放在一個(gè)塊中。所以，一個(gè)塊中的所有記錄組成一個(gè)完整的操作。2、LDF文件的操作碼我們發(fā)現(xiàn)，在LDF文件中存在非常之多的操作碼，不同的操作碼表示當(dāng)前LSN所做的操作。我們將這些操作碼及對應(yīng)操作進(jìn)行了歸納總結(jié)，并制作成具體的操作碼對應(yīng)表，如下所示：CodeOperationCodeOperation0LOP_NULL142LOP_CREATE_STREAMFILE1LOP_FORMAT_PAGE143LOP_MIGRATE_LOCKS2LOP_INSERT_ROWS144LOP_DROP_STREAMFILE3LOP_DELETE_ROWS145LOP_FS_DOWNLEVEL_OP4LOP_MODIFY_ROW148LOP_BP_DBVER5LOP_MODIFY_HEADER150LOP_BEGIN_CKPT6LOP_MODIFY_COLUMNS152LOP_XACT_CKPT7LOP_SET_BITS153LOP_END_CKPT9LOP_DELTA_SYSIND154LOP_BUF_WRITE10LOP_SET_FREE_SPACE155LOP_IDENTITY_TYPE11LOP_DELETE_SPLIT160LOP_BEGIN_RECOVERY12LOP_UNDO_DELETE_SPLIT161LOP_END_RECOVERY13LOP_EXPUNGE_ROWS173LOP_CREATE_INDEX16LOP_FILE_HDR_MODIFY174LOP_DROP_INDEX17LOP_CLEAR_GAM_BITS176LOP_CREATE_ALLOCCHAIN18LOP_COUNT_DELTA180LOP_CREATE_FTCAT19LOP_ROOT_CHANGE181LOP_DROP_FTCAT20LOP_COMPRESSION_INFO200LOP_REPL_COMMAND21LOP_ENCRYPT_PAGE201LOP_BEGIN_UPDATE127LOP_MAX_PAGE_OP202LOP_END_UPDATE128LOP_BEGIN_XACT203LOP_TEXT_POINTER129LOP_COMMIT_XACT204LOP_TEXT_INFO_BEGIN130LOP_ABORT_XACT205LOP_TEXT_INFO_END131LOP_PREP_XACT206LOP_REPL_NOOP132LOP_MARK_SAVEPOINT207LOP_TEXT_VALUE133LOP_FORGET_XACT210LOP_MAX_REPL_OP134LOP_CREATE_FILE211LOP_SHRINK_NOOP135LOP_DROP_FILE212LOP_FILESTREAM_INFO_BEGIN136LOP_MARK_DDL213LOP_FILESTREAM_INFO_END137LOP_HOBT_DDL214LOP_BULK_EXT_ALLOCATION138LOP_IDENT_NEWVAL215LOP_SECURITY_OP139LOP_IDENT_SENTVAL216LOP_PAGE_REENCRYPT140LOP_HOBT_DELTA220LOP_RANGE_INSERT141LOP_LOCK_XACT3、LDF文件恢復(fù)方案對于記錄被反復(fù)修改，可通過對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)行對比分析測試，具體情況如下。1、SQL Server自帶命令使用SQL Server自帶的fn_dblog()查詢?nèi)罩居涗洠撁羁刹樵兂鲎罱僮饔涗洝＃?biāo)紅處為本次測試的數(shù)據(jù)庫名，可更換）USE[BBBBBBBBBB]GOSELECT*FROM[sys].[fn_dblog](NULL,NULL)數(shù)據(jù)恢復(fù)情況2、第三方軟件SysTools常見的第三方恢復(fù)軟件如：SysTools SQL Log Analyzer，該軟件需要結(jié)合MDF文件才能解析出結(jié)果。數(shù)據(jù)恢復(fù)情況3、效率源QuicklyAnalysis軟件基于對【LDF文件恢復(fù)方案】的研究，我們將技術(shù)集成于最新研發(fā)的一款日志分析軟件——QuicklyAnalysis，可快速解析數(shù)據(jù)。下圖為效率源QuicklyAnalysis.exe分析結(jié)果。數(shù)據(jù)恢復(fù)情況

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

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

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

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

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

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

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

“ 面對這種情況，本文章提出一種基于SQL Server 二進(jìn)制日志文件（LDF文件）的數(shù)據(jù)恢復(fù)方法，該方法能夠有效、完整地恢復(fù)出目標(biāo)數(shù)據(jù)庫中的操作記錄。劇透：效率源QuicklyAnalysis軟件后面會加入到效率源DBF數(shù)據(jù)庫取證分析系列產(chǎn)品中，其他類型的數(shù)據(jù)庫恢復(fù)方案也會在之后的【技術(shù)視界】欄目中陸續(xù)推出，感興趣的小伙伴請多多關(guān)注我們?！?/p>

來源：效率源