读取一个SQL Server事务日志

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

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

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

使用 fn_dblog

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

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

1. 运行 fn_dblog函数

Select * FROM sys.fn_dblog(NULL,NULL)



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

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

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

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



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

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



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

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

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

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

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_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);



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

2. 指定事务的LSN
3. 将LSN转换为WITH STOPBEFOREMARK = ‘<mark_name>’ 子句能识别的格式，就像这样 e.g 00000070:00000011:0001 应该转换为 112000000001700001
4. 还原所有的日志备份链直到您到达事务发生的那个时间。使用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文件中得到极壮观的十六进制输出：

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

使用ApexSQL Log

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

1. 启动 ApexSQL Log

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

   

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

   

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

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

   

6. 点击“打开”

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

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

   

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

翻译者: 林勇桦