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

2018-01-16 19:54:48来源:cnblogs.com作者:wangguanguo人点击

分享

基本语法

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 biztest2from 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的数据恢复成功。

最新文章

123

最新摄影

闪念基因

微信扫一扫

第七城市微信公众平台