SQL Server日志分析程序开发思路

原文http://www.handytech.cn/blog/article.asp?id=126

用过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 
go 
use  dbLogTest 
go 
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) 
            { 
                Name = name; 
                DataType = type; 
                Length = length; 
            } 
        } 
         ///   <summary>  
         ///  sql二进制结构翻译，这个比较关键，测试环境为sql2005，其他版本没有测过。 
         ///   </summary>  
         ///   <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); 
                        index +=  4; 
                        c.Value = date; 
                         break; 
                     case System.Data.SqlDbType.Int: 
                         // 读取int字段,为4个字节保存 
                        c.Value = BitConverter.ToInt32(data, index); 
                        index +=  4; 
                         break; 
                    default: 
                        // 忽略不定长字段和其他不支持以及不愿意考虑的字段 
                         break; 
                } 
            } 
             // 跳过三个字节 
            index +=  3; 
             // 取变长字段的数量,保存两个字节 
             short varColumnCount = BitConverter.ToInt16(data, index); 
            index +=  2; 
             // 接下来,每两个字节保存一个变长字段的结束位置, 
             // 所以第一个变长字段的开始位置可以算出来 
             short startIndex =( short)( index + varColumnCount *  2); 
             // 第一个变长字段的结束位置也可以算出来 
             short endIndex = BitConverter.ToInt16(data, index); 
             // 循环变长字段列表读取数据 
             foreach (Datacolumn c  in columns) 
            { 
                 switch (c.DataType) 
                { 
                     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); 
                         break; 
                     default: 
                         // 忽略定长字段和其他不支持以及不愿意考虑的字段 
                         break; 
                } 
            } 
             // 获取完毕 
        } 
    } 
} 
  
4、更改你的sql连接字符串后运行以上代码，会看到如下输出信息：
 

数据对象dbo.log_test的LOP_Insert_ROWS操作： 
id =  100 
code = id001 
name = jinjazz 
date =  2008- 8- 7  18: 14: 03 
memo = 剪刀 
  
数据对象dbo.log_test的LOP_Insert_ROWS操作： 
id =  65549 
code = id002 
name = 游客 
date =  2008- 8- 6  18: 14: 03 
memo = 这家伙很懒，没有设置昵称 
  
数据对象dbo.log_test的LOP_Insert_ROWS操作： 
id = - 999 
code = id003 
name = 这家伙来自火星 
date =  2005- 11- 11  18: 14: 03 
memo = a 
  
数据对象dbo.log_test的LOP_Delete_ROWS操作： 
id =  100 
code = id001 
name = jinjazz 
date =  2008- 8- 7  18: 14: 03 
memo = 剪刀 
  
数据对象dbo.log_test的LOP_Delete_ROWS操作： 
id =  65549 
code = id002 
name = 游客 
date =  2008- 8- 6  18: 14: 03 
memo = 这家伙很懒，没有设置昵称 
  
数据对象dbo.log_test的LOP_Delete_ROWS操作： 
id = - 999 
code = id003 
name = 这家伙来自火星 
date =  2005- 11- 11  18: 14: 03 
memo = a 
  
************************日志分析完成