查看SQL Server日誌 Part 1

標籤：style   blog   http   color   io   os   使用   ar   strong   

曾經有朋友問我資料被刪除了，不藉助第三方工具能不能查是什麼時候發生的。 SQL Server提供了一個undocumented的函數fn_dblog可以讓我們查看活動的transaction log。

文法如下：

::fn_dblog(@StartingLSN,@EndingLSN)

如果參數都為NULL預設是抓取所有的交易資訊。

使用這個函數我們可以查詢DML,DDL資訊，比如資料刪除，修改更新等等。下面我們來看一個資料更新的例子：

create table test(namevarchar(10))

--插入條資料

insert into testvalues(‘allen test‘)

go 5

---查詢對錶Test的修改

select [Transaction Name],Operation,AllocUnitId,AllocUnitName,[Begin Time]fromfn_dblog(null,null)

where AllocUnitName =‘dbo.test‘andOperation=‘LOP_INSERT_ROWS‘

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

NULLLOP_INSERT_ROWS72057594040090624 dbo.test NULL

NULLLOP_INSERT_ROWS72057594040090624 dbo.test NULL

NULLLOP_INSERT_ROWS72057594040090624 dbo.test NULL

NULLLOP_INSERT_ROWS72057594040090624 dbo.test NULL

NULLLOP_INSERT_ROWS72057594040090624 dbo.test

--刪除表

drop table test

----從Log中查詢對錶的刪除

select [Transaction Name],Operation,AllocUnitId,AllocUnitName,[Begin Time]fromfn_dblog(null,null)

where [Transaction Name] = ‘DROPOBJ‘

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

DROPOBJLOP_BEGIN_XACT NULL NULL

--查詢Page Split

select Operation, AllocUnitName,COUNT(*)asNumberofIncidents

from ::fn_dblog(null,null)

where Operation =N‘LOP_DELETE_SPLIT‘

group byOperation, AllocUnitName

注意：從fn_dblog 查出來的LSN是不能直接作為參數的，需要將16進位轉化為numeric：

The LSN we havefrom the log dump above is 0000009d:0000021e:0001.To convert it:

• Take the rightmost 4 characters (2-byte log record     number) and convert to a 5-character decimal number, including leading     zeroes, to get stringA
• Take the middle number (4-byte log block number) and     convert to a 10-character decimal number, including leading zeroes, to get     stringB
• Take the leftmost number (4-byte VLF sequence number)     and convert to a decimal number, with no leading zeroes, to get stringC
• The LSN string we need is stringC + stringB + stringA

So0000009d:0000021e:0001 becomes ‘157‘ + ‘0000000542‘+ ‘00001‘ =‘157000000054200001‘.（來自SQLSkills）

 

可以參考下面的指令碼：

 

DECLARE @pageID$ NVARCHAR(23), @pageIDNVARCHAR(50), @sqlCmdNVARCHAR(4000);

SET @pageID$ =‘0001:0000004d‘--- PageID

 

SELECT @pageID =

CONVERT(VARCHAR(4),CONVERT(INT,CONVERT(VARBINARY,

SUBSTRING(@pageID$, 0, 5), 2)))

+ ‘,‘ +

CONVERT(VARCHAR(8),CONVERT(INT,CONVERT(VARBINARY,

SUBSTRING(@pageID$, 6, 8), 2)))

 

---查看Page內容

SET @sqlCmd = ‘DBCC PAGE (‘‘Crack_Me‘‘,‘+ @pageID + ‘,3) WITH TABLERESULTS‘

EXECUTE(@sqlCmd)

另外這個函數只能對當前活動的Log生效，如果Log備份之後就無法讀取了，我們需要用另外一個函數（fn_dump_dblog http://blog.csdn.net/smithliu328/article/details/7817540）從備份中讀取。上面也只是能夠看到資料被修改，但是無法做到恢複資料。

目前網上有人做了一些指令碼可以實現恢複，我現在正在做測試，如果能夠成功的話後面會把指令碼發出來。

 

轉載於：http://blog.csdn.net/kevinsqlserver/article/details/7800628

 

 《解釋一下SQLSERVER交易記錄記錄》

-- select * FROM fn_dblog(null,null)

 

查看SQL Server日誌 Part 1