sql server 記錄檔結構及誤操作資料找回

標籤：base   upd   src   inf   付費   erro   values   clu   point   

原文:sql server 記錄檔結構及誤操作資料找回

一. 概述

　　在sql server 裡有資料檔案.mdf和記錄檔.ldf，記錄檔是sqlserver資料庫的另一個重要組成部分，記錄檔記錄了所有事務以及每個事務對資料庫所做的修改。為了提高資料庫的效能， sqlserver 資料是緩衝在記憶體裡，並沒有即時寫入到磁碟，直到資料庫出現檢查點(checkpoint)或者記憶體不足必須（Lazy Write）將資料的修改寫入到磁碟。 sql server在開啟了事務並對記憶體中的資料進行修改時，會組建記錄檔記錄。 sqlserver 對資料頁的插入修改刪除都是在記憶體中完成後提交事務，但並不會同步到硬碟的資料頁上。 為了保證資料庫事務的一致性 如(伺服器崩潰，斷電)等 記憶體中的修改沒有來得及寫入硬碟，下次重啟時候要能夠恢複到一個事務一致的時間點，就必須依賴於交易記錄。

   　　1.1 儲存結構

　　與資料檔案不同 記錄檔不是按頁/區來進行組織的。每個物理記錄檔是分成多個虛擬日誌單元，虛擬日誌單元沒有固定大小，且數量不固定, 管理員也不能配置大小和數量。 例如：記錄檔每自動成長一次(預設是按10%的空間擴充)，會至少增加一個虛擬單元。

　　交易記錄是一種迴繞的檔案。例如一個資料庫裡的記錄檔包括5個虛擬日誌單元，在建立資料庫時，邏輯記錄檔從物理檔案的始端開始，新的日誌記錄被添加到邏輯日誌未端，然後向物理日誌未端擴張。

　　當邏輯日誌的末端到達物理日誌的末端時，新的日誌記錄將迴繞到物理記錄檔的始端繼續向後寫（這是因為記錄備份會截斷使日誌空間重用）。

　　是記錄檔的流程圖，當記錄備份後虛擬日誌1和虛擬日誌2會被截斷，虛擬日誌3成為了邏輯日誌的開頭，當虛擬日誌3和虛擬日誌4在使用後，再次備份時，由於記錄檔是一個迴繞的檔案，此時又從虛擬日誌1開始。
　　 圖1  記錄檔的外觀

　　

　　圖2 交易記錄的迴圈使用

     

 　　在一個虛擬日誌單元裡，分成很多塊，塊內有具體的日誌記錄，每條日誌記錄有一個LSN(Log Sequence Number)編號，這個編號由三部分組成。第一部分是虛擬日誌單元(Virtual Log File)序號，第二部分是在虛擬日誌單元中塊的編號，第三部分是在塊中日誌記錄的編號。對於某個LSN,其編號為000001D:000000FD:0002。 這表明這個LSN是屬於虛擬日誌000001D,該虛擬日誌中屬於塊000000FD，在該塊中對應記錄2。

　　1.2 DBCC LOG
　　使用DBCC LOG來查看記錄檔裡存放了些什麼資訊， dbcc log(dbname, formart_id),formart_id 使用"3" 參數輸出會比較詳細。

Create database TestLoggouse TestLoggoCreate Table Test(ID int,name nvarchar(50))GOInsert into Test Values(1,‘aaaa‘)update Test set name=‘bbbb‘ where ID=1Godbcc traceon (3604)godbcc log (TestLog,3)

　　由於dbcc log是未公開的命令，所以未找到相關說明, 如所示 包括了當前序號號，操作類型，事務號等相關資訊。

 二. ApexSQL Log工具

　　由於dbcc log資料不太直觀，現通過第三方工具ApexSQL Log來查看，該工具可以看到對上面表的建立，插入，更新，刪除的操作記錄，在資料庫記錄檔裡還標註了起始時間表，以及操作由哪個使用者執行的，對於每一個操作，可以看到更具體的更新資訊。

　　　　這是剛剛操作的二條記錄如所示

 　 　

　　選中insert 該行可以找到該語句做undo (撤消復原 舊值覆蓋)和redo(提交 新值覆蓋)

　　

-- Undo   INSERT (0000001E:00000047:0013) done at 2018-07-29 09:49:55.570 by hsr-PC\hsr in transaction 0000:00000301 (Committed)BEGIN TRANSACTIONDELETE FROM [dbo].[Test] WHERE /*** WARNING: WHERE CLAUSE FOR THIS STATEMENT WAS GENERATED FOR A TABLE WITH NO PRIMARY KEY AND NO CLUSTERED INDEX ***/[ID] = 1 AND [name] = N‘aaaa‘ COLLATE Chinese_PRC_CI_ASIF @@ROWCOUNT <= 1 COMMIT TRANSACTION ELSE BEGIN ROLLBACK TRANSACTION; PRINT ‘ERROR: STATEMENT AFFECTED MORE THAN ONE ROW. ALL THE CHANGES WERE ROLLED BACK.‘ END

--Redo    INSERT (0000001E:00000047:0013) done at 2018-07-29 09:49:55.570 by hsr-PC\hsr in transaction 0000:00000301 (Committed)INSERT INTO [dbo].[Test] ([ID], [name]) VALUES (1, N‘aaaa‘ COLLATE Chinese_PRC_CI_AS)

-- 下面ID=1的語句做四做操作update Test set name=‘cccc‘ where ID=1update Test set name=‘dddd‘ where ID=1update Test set name=‘eeee‘ where ID=1delete from  Test  where ID=1

 下列記錄了相應的操作,trial restricted 可能是因為該軟體需要付費。

  總結： 使用truncate table 來刪除操作是不會記錄日誌的，且無法做undo操作。日誌記錄與實際修改的資料量有關，每一條記錄的修改都會儲存日誌記錄。sql server日誌裡面能讀到資料修改前的值和修改後的值。

 

參考文獻：

　　sq lserver2012實施與管理實戰指南

sql server 記錄檔結構及誤操作資料找回