使用fn_dblog函数查看事务日志和恢复数据

基本语法

SqlServer中有一个未在文档中公开的函数sys.fn_dblog，提供查询当前数据库事务日志的功能。通过这个函数，可以简单了解下数据库事务日志的机制

使用方法如下：

select * from sys.fn_dblog(null,null)

　　它有两个参数，分别是起始LSN和结尾LSN（Log Sequence Number），返回结果是两个LSN之间的日志。LSN值可以不指定，使用null作为参数，则是查询所有的日志。

　　两个参数，不能直接使用查询出来Current LSN值，否则会报“传递给 OpenRowset(DBLog, ...)的参数无效”错误，或者参数不起作用。

　　使用方法：

  select * from fn_dblog('0x00000041:00000320:0003',null) --LSN前面加0x
  select * from fn_dblog(null,'65:800:3') --转换成十进制形式
  select * from fn_dblog(null,'65000000080000003')--LSN的另一种写法

记录内容

　　通过一些具体操作查看日志记录情况

   　在一个数据库中查询事务日志条数，共90条：

       

       向其中一个表中插入一条数据：

　　

       查询fn_dblog，记录条数变成了93条，增加了3条，分别是事务开始(LOP_BEGIN_XACT)、插入数据(LOP_INSERT_ROWS)、事务提交(LOP_COMMIT_XACT)

      

       同理，修改数据：

  update tbl set value='hhhhh' where id=6

　　再查询日志，增加了三条：事务开始日志(LOP_BEGIN_XACT)、修改日志(LOP_MODIFY_ROW)、事务提交日志(LOP_COMMIT_XACT)

　　

     类似的，执行删除：

delete from tbl where id=6

　　日志增加了3条：事务开始日志(LOP_BEGIN_XACT)、修改日志(LOP_DELETE_ROWS)、事务提交日志(LOP_COMMIT_XACT)

　　

　　上面只是最简单的例子，如果涉及分配空间、页拆分、索引更新、系统表更新等操作，记录条数会增多。

恢复数据

　　如果想要恢复被删除或被修改的数据，可以根据事务的LSN，可以将数据库恢复到事务执行之前的状态，前提是有过一次全量备份。以上面为例，先查询tbl表，查看内容：

　　

　　id=6的记录已经被删除了，如果想要恢复，先记下删除事务开始位置对应的LSN：就是上面第97行00000041:00000318:0001，后面恢复日志备份的时候用。

　　先使用WITH NORECOVERY选项恢复一个全量备份：

　　

restore database biztest2
from disk='D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\biztest.bak'
with move 'BIZTEST' to 'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\data\biztest2', 
move 'biztest_log' to 'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\data\biztest2_log',
norecovery

　　恢复完成后，SSSM中查看数据库，会有个“恢复中”的提示

　　

　　此时数据库还不可用，只能用来继续还原增量备份或事务日志备份。对刚才被删除数据的数据库BIZTEST做一下日志备份

　　

　　使用上面查到的LSN：00000041:00000318:0001和带STOPBEFOREMARK选项的RESTORE命令：

  restore log biztest2
  from disk='D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\biztest.trn'
  WITH STOPBEFOREMARK ='lsn:0x00000041:00000318:0001',
  recovery;

　　

　　此时，BIZTEST2已经可以用了。执行查询：

　　

　　id=6的数据恢复成功。