SQL Server日志分析程式開發思路

用過logExplorer的朋友都會被他強悍的功能吸引，我寫過一篇詳細的操作文檔可以參考

http://blog.csdn.net/jinjazz/archive/2008/05/19/2459692.aspx

我們可以自己用開發工具來實作sql日志的讀取，這個應用還是很酷的，具體思路

1、首先要了解一個沒有公開的系統函數::fn_dblog，他可以讀取sql日志，并傳回二進制的行資料

2、然後要了解sql的二進制資料是如何存儲的，這個可以參考我的blog文章

http://blog.csdn.net/jinjazz/archive/2008/08/07/2783872.aspx

3、用自己擅長的開發工具來分析資料，得到我們需要的資訊

我用c#寫了一個測試樣例，分析了int，char，datetime和varchar的日志情況而且沒有考慮null和空字元串的儲存，希望感興趣的朋友能和我一起交流打造屬于自己的日志分析工具

詳細的試驗步驟以及代碼如下：

1、首先建立sqlserver的測試環境，我用的sql2005，這個過程不能保證在之前的版本中運作

以下sql語句會建立一個dbLogTest資料庫，并建立一張log_test表，然後插入3條資料之後把表清空

use master 

go 

create database dbLogTest 

use  dbLogTest 

create table log_test(id int ,code char(10),name varchar(20),date datetime,memo varchar(100)) 

insert into log_test select 100, 'id001','jinjazz',getdate(),'剪刀' 

insert into log_test select 65549,'id002','遊客',getdate()-1,'這家夥很懶，沒有設定昵稱' 

insert into log_test select -999,'id003','這家夥來自火星',getdate()-1000,'a' 

delete from log_test 

--use master  

--go 

--drop database dbLogTest 

2、我們最終的目的是要找到被我們删掉的資料

3、分析日志的c#代碼:我已經盡量詳細的寫了注釋

using System; 

using System.Collections.Generic; 

using System.Text; 

namespace ConsoleApplication21 

{ 

    class Program 

    { 

        /// <summary> 

        /// 分析sql2005日志，找回被delete的資料，引用請保留以下資訊 

        /// 作者：jinjazz (csdn的剪刀) 

        /// 作者blog：http://blog.csdn.net/jinjazz 

        /// </summary> 

        /// <param name="args"></param> 

        static void Main(string[] args) 

        { 

            using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection()) 

            { 

                conn.ConnectionString = "server=localhost;uid=sa;pwd=sqlgis;database=dbLogTest"; 

                conn.Open(); 

                using (System.Data.SqlClient.SqlCommand command = conn.CreateCommand()) 

                { 

                    //察看dbo.log_test對象的sql日志 

                    command.CommandText = @"Select allocunitname,operation,[RowLog Contents 0] as r0,[RowLog Contents 1]as r1  

                                from::fn_dblog (null, null)    

                                where allocunitname like 'dbo.log_test%'and 

                                operation in('LOP_Insert_ROWS','LOP_Delete_ROWS')"; 

                    System.Data.SqlClient.SqlDataReader reader = command.ExecuteReader(); 

                    //根據表字段的順序建立字段數組 

                    Datacolumn[] columns = new Datacolumn[] 

                        { 

                            new Datacolumn("id", System.Data.SqlDbType.Int), 

                            new Datacolumn("code", System.Data.SqlDbType.Char,10), 

                            new Datacolumn("name", System.Data.SqlDbType.VarChar), 

                            new Datacolumn("date", System.Data.SqlDbType.DateTime), 

                            new Datacolumn("memo", System.Data.SqlDbType.VarChar) 

                        }; 

                    //循環讀取日志 

                    while (reader.Read()) 

                    { 

                        byte[] data = (byte[])reader["r0"]; 

                        try 

                            //把二進制資料結構轉換為明文 

                            TranslateData(data, columns); 

                            Console.WriteLine("資料對象{1}的{0}操作：", reader["operation"], reader["allocunitname"]); 

                            foreach (Datacolumn c in columns) 

                            { 

                                Console.WriteLine("{0} = {1}", c.Name, c.Value); 

                            } 

                            Console.WriteLine(); 

                        } 

                        catch 

                            //to-do... 

                    } 

                    reader.Close(); 

                } 

                conn.Close(); 

            } 

            Console.WriteLine("************************日志分析完成"); 

            Console.ReadLine(); 

        } 

        //自定義的column結構 

        public class Datacolumn 

            public string Name; 

            public System.Data.SqlDbType DataType; 

            public short Length = -1; 

            public object Value = null; 

            public Datacolumn(string name, System.Data.SqlDbType type) 

                Name = name; 

                DataType = type; 

            public Datacolumn(string name,System.Data.SqlDbType type,short length) 

                Length = length; 

        /// sql二進制結構翻譯，這個比較關鍵，測試環境為sql2005，其他版本沒有測過。 

        /// <param name="data"></param> 

        /// <param name="columns"></param> 

        static void TranslateData(byte[] data, Datacolumn[] columns) 

            //我隻根據示例寫了Char,DateTime,Int三種定長度字段和varchar一種不定長字段，其餘的有興趣可以自己補充 

            //這裡沒有暫時沒有考慮Null和空字元串兩種情況，以後會補充。 

            //引用請保留以下資訊： 

            //作者：jinjazz  

            //sql的資料行二進制結構參考我的blog 

            //http://blog.csdn.net/jinjazz/archive/2008/08/07/2783872.aspx 

            //行資料從第5個位元組開始 

            short index = 4; 

            //先取定長字段 

            foreach (Datacolumn c in columns) 

                switch (c.DataType) 

                    case System.Data.SqlDbType.Char: 

                        //讀取定長字元串，需要根據表結構指定長度 

                        c.Value = System.Text.Encoding.Default.GetString(data,index,c.Length); 

                        index += c.Length; 

                        break; 

                    case System.Data.SqlDbType.DateTime: 

                        //讀取datetime字段，sql為8位元組儲存 

                        System.DateTime date = new DateTime(1900, 1, 1); 

                        //前四位1/300秒儲存 

                        int second = BitConverter.ToInt32(data, index); 

                        date = date.AddSeconds(second/300); 

                        index += 4; 

                        //後四位1900-1-1的天數 

                        int days = BitConverter.ToInt32(data, index); 

                        date=date.AddDays(days); 

                        c.Value = date; 

                    case System.Data.SqlDbType.Int: 

                        //讀取int字段,為4個位元組儲存 

                        c.Value = BitConverter.ToInt32(data, index); 

                   default: 

                       //忽略不定長字段和其他不支援以及不願意考慮的字段 

            //跳過三個位元組 

            index += 3; 

            //取變長字段的數量,儲存兩個位元組 

            short varColumnCount = BitConverter.ToInt16(data, index); 

            index += 2; 

            //接下來,每兩個位元組儲存一個變長字段的結束位置, 

            //是以第一個變長字段的開始位置可以算出來 

            short startIndex =(short)( index + varColumnCount * 2); 

            //第一個變長字段的結束位置也可以算出來 

            short endIndex = BitConverter.ToInt16(data, index); 

            //循環變長字段清單讀取資料 

                    case System.Data.SqlDbType.VarChar: 

                        //根據開始和結束位置，可以算出來每個變長字段的值 

                        c.Value =System.Text.Encoding.Default.GetString(data, startIndex, endIndex - startIndex); 

                        //下一個變長字段的開始位置 

                        startIndex = endIndex; 

                        //擷取下一個變長字段的結束位置 

                        index += 2; 

                        endIndex = BitConverter.ToInt16(data, index); 

                    default: 

                        //忽略定長字段和其他不支援以及不願意考慮的字段 

            //擷取完畢 

    } 

} 

4、更改你的sql連接配接字元串後運作以上代碼，會看到如下輸出資訊：

資料對象dbo.log_test的LOP_Insert_ROWS操作： 

id = 100 

code = id001 

name = jinjazz 

date = 2008-8-7 18:14:03 

memo = 剪刀 

id = 65549 

code = id002 

name = 遊客 

date = 2008-8-6 18:14:03 

memo = 這家夥很懶，沒有設定昵稱 

id = -999 

code = id003 

name = 這家夥來自火星 

date = 2005-11-11 18:14:03 

memo = a 

資料對象dbo.log_test的LOP_Delete_ROWS操作： 

************************日志分析完成