用c#读取并分析sql2005日志


用过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条数据之后把表清空

  1. use master
  2. go
  3. create database dbLogTest
  4. go
  5. use  dbLogTest
  6. go
  7. create table log_test(id int ,code char(10),name varchar(20),date datetime,memo varchar(100))
  8. insert into log_test select 100, 'id001','jinjazz',getdate(),'剪刀'
  9. insert into log_test select 65549,'id002','游客',getdate()-1,'这家伙很懒，没有设置昵称'
  10. insert into log_test select -999,'id003','这家伙来自火星',getdate()-1000,'a'
  12. delete from log_test
  14. --use master 
  15. --go
  16. --drop database dbLogTest
  17.  


2、我们最终的目的是要找到被我们删掉的数据

3、分析日志的c#代码:我已经尽量详细的写了注释

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Text;
  5. namespace ConsoleApplication21
  6. {
  7.     class Program
  8.     {
  9.         /// <summary>
  10.         /// 分析sql2005日志，找回被delete的数据，引用请保留以下信息
  11.         /// 作者：jinjazz (csdn的剪刀)
  12.         /// 作者blog：http://blog.csdn.net/jinjazz
  13.         /// </summary>
  14.         /// <param name="args"></param>
  15.         static void Main(string[] args)
  16.         {
  17.             using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection())
  18.             {
  19.                 conn.ConnectionString = "server=localhost;uid=sa;pwd=sqlgis;database=dbLogTest";
  20.                 conn.Open();
  21.                 using (System.Data.SqlClient.SqlCommand command = conn.CreateCommand())
  22.                 {
  23.                     //察看dbo.log_test对象的sql日志
  24.                     command.CommandText = @"SELECT allocunitname,operation,[RowLog Contents 0] as r0,[RowLog Contents 1]as r1 
  25.                                 from::fn_dblog (null, null)   
  26.                                 where allocunitname like 'dbo.log_test%'and
  27.                                 operation in('LOP_INSERT_ROWS','LOP_DELETE_ROWS')";
  29.                     System.Data.SqlClient.SqlDataReader reader = command.ExecuteReader();
  30.                     //根据表字段的顺序建立字段数组
  31.                     Datacolumn[] columns = new Datacolumn[]
  32.                         {
  33.                             new Datacolumn("id", System.Data.SqlDbType.Int),
  34.                             new Datacolumn("code", System.Data.SqlDbType.Char,10),
  35.                             new Datacolumn("name", System.Data.SqlDbType.VarChar),
  36.                             new Datacolumn("date", System.Data.SqlDbType.DateTime),
  37.                             new Datacolumn("memo", System.Data.SqlDbType.VarChar)
  38.                         };
  39.                     //循环读取日志
  40.                     while (reader.Read())
  41.                     {
  42.                         byte[] data = (byte[])reader["r0"];
  43.                         
  44.                         try
  45.                         {
  46.                             //把二进制数据结构转换为明文
  47.                             TranslateData(data, columns);
  48.                             Console.WriteLine("数据对象{1}的{0}操作：", reader["operation"], reader["allocunitname"]);
  49.                             foreach (Datacolumn c in columns)
  50.                             {
  51.                                 Console.WriteLine("{0} = {1}", c.Name, c.Value);
  52.                             }
  53.                             Console.WriteLine();
  54.                         }
  55.                         catch
  56.                         {
  57.                             //to-do...
  58.                         }
  59.                         
  60.                     }
  61.                     reader.Close();
  62.                 }
  63.                 conn.Close();
  64.             }
  65.             Console.WriteLine("************************日志分析完成");
  66.             Console.ReadLine();
  67.         }
  68.         //自定义的column结构
  69.         public class Datacolumn
  70.         {
  71.             public string Name;
  72.             public System.Data.SqlDbType DataType;
  73.             public short Length = -1;
  74.             public object Value = null;
  75.             public Datacolumn(string name, System.Data.SqlDbType type)
  76.             {
  77.                 Name = name;
  78.                 DataType = type;
  79.             }
  80.             public Datacolumn(string name,System.Data.SqlDbType type,short length)
  81.             {
  82.                 Name = name;
  83.                 DataType = type;
  84.                 Length = length;
  85.             }
  86.         }
  87.         /// <summary>
  88.         /// sql二进制结构翻译，这个比较关键，测试环境为sql2005，其他版本没有测过。
  89.         /// </summary>
  90.         /// <param name="data"></param>
  91.         /// <param name="columns"></param>
  92.         static void TranslateData(byte[] data, Datacolumn[] columns)
  93.         {
  94.             //我只根据示例写了Char,DateTime,Int三种定长度字段和varchar一种不定长字段，其余的有兴趣可以自己补充
  95.             //这里没有暂时没有考虑Null和空字符串两种情况，以后会补充。
  97.             //引用请保留以下信息：
  98.             //作者：jinjazz 
  99.             //sql的数据行二进制结构参考我的blog
  100.             //http://blog.csdn.net/jinjazz/archive/2008/08/07/2783872.aspx
  101.             //行数据从第5个字节开始
  102.             short index = 4;
  103.             //先取定长字段
  104.             foreach (Datacolumn c in columns)
  105.             {
  106.                 switch (c.DataType)
  107.                 {
  108.                     case System.Data.SqlDbType.Char:
  109.                         //读取定长字符串，需要根据表结构指定长度
  110.                         c.Value = System.Text.Encoding.Default.GetString(data,index,c.Length);
  111.                         index += c.Length;
  112.                         break;
  113.                     case System.Data.SqlDbType.DateTime:
  114.                         //读取datetime字段，sql为8字节保存
  115.                         System.DateTime date = new DateTime(1900, 1, 1);
  116.                         //前四位1/300秒保存
  117.                         int second = BitConverter.ToInt32(data, index);
  118.                         date = date.AddSeconds(second/300);
  119.                         index += 4;
  120.                         //后四位1900-1-1的天数
  121.                         int days = BitConverter.ToInt32(data, index);
  122.                         date=date.AddDays(days);
  123.                         index += 4;
  124.                         c.Value = date;
  125.                         break;
  126.                     case System.Data.SqlDbType.Int:
  127.                         //读取int字段,为4个字节保存
  128.                         c.Value = BitConverter.ToInt32(data, index);
  129.                         index += 4;
  130.                         break;
  131.                    default:
  132.                        //忽略不定长字段和其他不支持以及不愿意考虑的字段
  133.                         break;
  134.                 }
  135.             }
  136.             //跳过三个字节
  137.             index += 3;
  138.             //取变长字段的数量,保存两个字节
  139.             short varColumnCount = BitConverter.ToInt16(data, index);
  140.             index += 2;
  141.             //接下来,每两个字节保存一个变长字段的结束位置,
  142.             //所以第一个变长字段的开始位置可以算出来
  143.             short startIndex =(short)( index + varColumnCount * 2);
  144.             //第一个变长字段的结束位置也可以算出来
  145.             short endIndex = BitConverter.ToInt16(data, index);
  146.             //循环变长字段列表读取数据
  147.             foreach (Datacolumn c in columns)
  148.             {
  149.                 switch (c.DataType)
  150.                 {
  151.                     case System.Data.SqlDbType.VarChar:
  152.                         //根据开始和结束位置，可以算出来每个变长字段的值
  153.                         c.Value =System.Text.Encoding.Default.GetString(data, startIndex, endIndex - startIndex);
  154.                         //下一个变长字段的开始位置
  155.                         startIndex = endIndex;
  156.                         //获取下一个变长字段的结束位置
  157.                         index += 2;
  158.                         endIndex = BitConverter.ToInt16(data, index);
  159.                         break;
  160.                     default:
  161.                         //忽略定长字段和其他不支持以及不愿意考虑的字段
  162.                         break;
  163.                 }
  164.             }
  165.             //获取完毕
  166.         }
  167.     }
  168. }

4、更改你的sql连接字符串后运行以上代码，会看到如下输出信息：

    1. 数据对象dbo.log_test的LOP_INSERT_ROWS操作：
    2. id = 100
    3. code = id001
    4. name = jinjazz
    5. date = 2008-8-7 18:14:03
    6. memo = 剪刀
    8. 数据对象dbo.log_test的LOP_INSERT_ROWS操作：
    9. id = 65549
    10. code = id002
    11. name = 游客
    12. date = 2008-8-6 18:14:03
    13. memo = 这家伙很懒，没有设置昵称
    15. 数据对象dbo.log_test的LOP_INSERT_ROWS操作：
    16. id = -999
    17. code = id003
    18. name = 这家伙来自火星
    19. date = 2005-11-11 18:14:03
    20. memo = a
    22. 数据对象dbo.log_test的LOP_DELETE_ROWS操作：
    23. id = 100
    24. code = id001
    25. name = jinjazz
    26. date = 2008-8-7 18:14:03
    27. memo = 剪刀
    29. 数据对象dbo.log_test的LOP_DELETE_ROWS操作：
    30. id = 65549
    31. code = id002
    32. name = 游客
    33. date = 2008-8-6 18:14:03
    34. memo = 这家伙很懒，没有设置昵称
    36. 数据对象dbo.log_test的LOP_DELETE_ROWS操作：
    37. id = -999
    38. code = id003
    39. name = 这家伙来自火星
    40. date = 2005-11-11 18:14:03
    41. memo = a
    43. ************************日志分析完

      1. 读取一个SQL Server事务日志

        SQL Server事务日志包含了描述对数据库造成的更改的记录。它们存储足够的信息来恢复数据库到一个特定的时间点，来重做或回滚一个更改。不过，如何去看这些事务日志里面的东西，去查出一个特定的事务，去看一下有什么发生了并且在例如意外删除了记录的恢复是如何回滚更改的。

         

        查看存储联机事务日志或者事务日志备份里的东西并不简单。

        在二进制编辑器里打开LDF和TRN文件会显示无法理解的很明显不能直接阅读的一些记录。 例如，这是对一个LDF文件内容的摘录

         

        Opening LDF and TRN files in a binary editor

         

        使用 fn_dblog

        fn_dblog是一个没有文档记载的SQL Server函数，它可以读取一个联机事务日志的活动部分。

        来看一下您必须要做的步骤和每一步的结果显示吧

          1. 运行 fn_dblog函数
        Select * FROM sys.fn_dblog(NULL,NULL)

        Results set returned by fn_dblog function

        函数本身返回了129列，返回结果中只有一个特定列能够缩窄结果范围到一个特定的事务类型

          1. 从fn_dblog函数返回的结果集来看，找到您想要的事务

        要查看插入记录的事务，请运行：

        SELECT [Current LSN], 
       Operation, 
       Context, 
       [Transaction ID], 
       [Begin time]
       FROM sys.fn_dblog
   (NULL, NULL)
  WHERE operation IN
   ('LOP_INSERT_ROWS');

         

        Transactions for inserted rows

         

        要查看删除记录的事务，请运行：

        SELECT [begin time], 
       [rowlog contents 1], 
       [Transaction Name], 
       Operation
  FROM sys.fn_dblog
   (NULL, NULL)
  WHERE operation IN
   ('LOP_DELETE_ROWS');

        Transactions for deleted rows

          1. 要找到存储了inserted 列或者deleted 列的值 -需要查看RowLog Contents 0，RowLog Contents 1，RowLog Contents 2，RowLog Contents 3，RowLog Contents 4，描述和日志记录

        对于这个函数没有官方文档的，要知道事务的类型真的不太容易

        被插入的和被删除的行会以十六进制的形式显示。要把这些值分割为一个一个的字段您必须要知道所用的格式，理解状态位，知道有多少列等等。

        1. 要将二进制数据转换为表的数据需要考虑到表字段的数据类型。要注意不同的数据类型转换机制是不同的。

        fn_dbLog 是一个强大，伟大的和免费的函数，但是它也有一些限制-对于对象结构的改变阅读日志记录是比较复杂的，因为它通常涉及到重构几个系统表的状态，
        只有一个事务日志的活动部分才能被读取，并且里面不包含UPDATE/BLOB 的重构。

        因为UPDATE 操作是以最小日志记录的方式存在在事务日志里，没有旧值或新值，只有记录的变化（例如：SQL Server可能只记录“G”被改变为“F”，当实际上完整的值是“GLOAT”被改变为“FLOAT”），您必须手动重构update之前的状态这个会涉及到需要重构在行被插入到页面到页面被更新之间的所有的中间状态，这些状态您都需要尝试去重构。

        当删除BLOB数据的时候，已删除的BLOB 是不会被插入到事务日志的，因此，只是从日志记录里读取DELETE BLOB是不能把BLOB数据恢复的。
        如果对于已删除的BLOB只有一个INSERT 日志记录，您可以设法匹配这两个，您可以通过fn_dblog 从事务日志里恢复被删的BLOB

        使用fn_dump_dblog

        读取本地事务日志或者使用fn_dump_dblog 函数读取本地已经压缩的日志备份文件。再说一次，这些函数都是没有文档记载的。

          1. 在一个特定的事务日志备份上运行fn_dump_dblog 函数。注意，您必须指定所有的63个参数
        SELECT *
FROM fn_dump_dblog
(NULL,NULL,N'DISK',1,N'E:\ApexSQL\backups\AdventureWorks2012_05222013.trn', 
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT, 
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT, 
DEFAULT);

        fn_dump_dblog function output

        而您现在又回到了起点 ，就像使用fn_dblog 函数-您需要手动重构所有的行值，您需要重构UPDATE 操作整个链条的状态和BLOB值等等。

        SELECT [Current LSN], 
       Operation, 
       Context, 
       [Transaction ID], 
     [transaction name],
       Description
FROM fn_dump_dblog
(NULL,NULL,N'DISK',1,N'E:\ApexSQL\backups\AdventureWorks2012_05222013.trn', 
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT, 
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT, 
DEFAULT);

        Returning specific columns using fn_dump_dblog function

        如果您不想手工从事务日志备份中解压出事务，不过只是想还原数据库到在操作发生之前的一个特定的时间点，您可以：

        1. 指定事务的LSN
        2. 将LSN转换为WITH STOPBEFOREMARK = ‘<mark_name>’ 子句能识别的格式，就像这样 e.g 00000070:00000011:0001 应该转换为 112000000001700001
        3. 还原所有的日志备份链直到您到达事务发生的那个时间。使用WITH STOPBEFOREMARK = ‘<mark_name>’ 子句来指定相关的事务LSN 
          RESTORE LOG AdventureWorks2012
FROM
    DISK = N'E:\ApexSQL\backups\AW2012_05232013.trn'
WITH
    STOPBEFOREMARK = 'lsn:112000000001700001',
    NORECOVERY;

        使用DBCC PAGE

        另一个有用的但也是没有文档记载的命令是DBCC PAGE。使用它阅读在线的数据库文件内容-MDF和LDF。
        语法是：

        DBCC PAGE ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])

        转储AdventureWorks2012 数据库联机事务日志文件的第一页，使用：

        SELECT FILE_ID ('AdventureWorks2012_Log') AS 'File ID' 
-- to determine Log file ID = 2
DBCC PAGE (AdventureWorks2012, 2, 0, 2)

        您将会得到下面信息

        DBCC execution completed. If DBCC printed error messages, 
contact your system administrator.

        默认不会输出任何东西。如果您想在 SQL Server Management Studio输出，首先需要打开跟踪标志3604

        DBCC TRACEON (3604, -1)

        然后重新运行命令

        DBCC PAGE (AdventureWorks2012, 2, 0, 2)

        您会得到一堆错误和糟糕的头部信息，这些您都可以忽略它。在最后您会从在线LDF文件中得到极壮观的十六进制输出：

        Hexadecimal output from the online LDF file

        对于您的数据库数据这样显示是非常不友好的，基本上无异于在十六进制编辑器里查看数据，尽管您现在至少已经可以访问在线数据了。

        使用ApexSQL Log

        ApexSQL Log 是一个 sqlserver 事务日志阅读器 它能够读取联机事务日志，分离的事务日志和事务日志备份文件-不管是本地的还是压缩的。
        如有需要，它也会读取数据库备份来获取足够信息来进行成功地重构。它可以重播对于数据库数据和对象的更改，包括在ApexSQL Log安装之前已经发生的更改。
        它不像上面的没有文档记载和不受支持的函数描述那样，您可以得到完美的可以理解的关于发生的事情，旧值和新值的信息。

        1. 启动 ApexSQL Log

        2. 连接到您想读取事务日志的数据库

          Connecting to the database to read the transaction logs from

           

        3. 在Select SQL logs to analyze步骤，选择您想读取的日志。 确保他们来自于完整日 志链

          Selecting the transaction logs to read from

        4. 要添加事务日志备份文件和分离的LDF文件，使用添加按钮

        5. 使用过滤设置选项来收窄事务读取 ，使用时间范围、操作类型、表和其他有用的过滤选项

          Filtering the transactions read

        6. 点击“打开”

          完整全面的结果将会被显示在 ApexSQL Log 的grid窗格

          您能够看到时间、操作的开始和结束、操作类型、架构、受影响的对象名、执行操作的用户名、计算机和执行操作的程序。
          对于UPDATE，您会看到被更新字段的旧值和新值。

          Fully comprehensive results shown in the ApexSQL Log grid

        使用 ApexSQL Log 可以避免十六进制值、没文档记载的函数、不清晰的列内容、长查询、复杂操作步骤、不完整的更新和BLOB重构当读取SQL Server事务日志的时候。
        它会读取事务日志并使用“纯英语”来展示结果。另外，undo和redo脚本只需要点击鼠标即可。

posted @ 2017-08-31 09:52  赤狐(zcm123)  阅读(519)  评论(0)  编辑  收藏  举报