原文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
************************日志分析完成
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
************************日志分析完成