ITPub博客

首页 > 数据库 > SQL Server > sql server解析日志

sql server解析日志

原创 SQL Server 作者:lhrbest 时间:2020-11-26 09:33:34 0 删除 编辑


数据误操作,教你使用ApexSQLLog工具从 SQLServer日志恢复数据!

小伙伴问我这咋办,首先没有备份,那么只有从数据库日志查找,然后看能不能通过日志找回之前的数据,再还原到刷状态之前的数据。然后就找到了ApexSQLLog工具,接下来我介绍下这款工具的使用和如何恢复数据。ApexSQLLog有几个版本,我是用的是ApexSQLLog2014支持SqlServer更高的版本,数据库使用的是SqlSerVer2014。

ApexSQLLog2014
提取码: np4f

  • 首先建一个测试库,和一张测试表。
    测试库ApexSQLLogTest和测试用的表TestUser,然后我手动编辑了三条数据进去,保存编辑的数据。

  • 用ApexSQLLog打开测试库日志
    选择要连接的数据库,也可以从最近的session中打开,打开筛选过的记录可以保存未session。
    然后选中要筛选的日志文件,如果有备份数据库文件也会自动查找到并在这里罗列出来,自己按情况选择。

  • 条件筛选
    我们选择日志文件后就进入到筛选条件选择,可以在筛选条件里面自由组合。
    可以选择时间段(Time range)、操作(operations)、表(tables)。


    高级选项(advanced options)里面还有用户、字段条件等可以选择。

  • 查看日志数据
    当我们组合完筛选条件后,就进入到日志分析界面,可以看到我们之前手动插入的三条数据实际已经在日志里面了,分成了三条insert语句。在选中其中一条日志的时候在下面可以看到执行的各字段值的修改情况。

    可以点击 下面的Row history查看记录,Redo script可以生成执行的操作, Undo script可以还原到之前的数据。我们恢复数据就是使用Undo script。

  • 恢复数据测试。
    我们使用update语句将Status状态全都重置为3。

update TestUser set Status=3

然后刷新下日志,会看到多出了三条Update日志记录,点击第一条看到下面的Status字段从0变为了3。

我们选中这三条记录右键或者上面的菜单栏功能,用create undo script 生成恢复sql。

--	This UNDO script was generated with ApexSQL Log 2014.04.1133 on 2020-06-10 11:18:47.601
--	NOTE: Operations in UNDO scripts are always output in descending order.
--	SERVER VIP-966\SQLEXPRESS
--	DATABASE ApexSQLLogTest
--	UPDATE (00000024:000000A0:0004) done at 2020-06-10 11:09:36.293 by VIP-966\Administrator in transaction 0000:0000034B (Committed)
BEGIN TRANSACTION 
UPDATE [dbo].[TestUser] SET [Status] = 2 WHERE [Id] = 3
IF @@ROWCOUNT <= 1 COMMIT TRANSACTION ELSE BEGIN ROLLBACK TRANSACTION; PRINT 'ERROR: STATEMENT AFFECTED MORE THAN ONE ROW. ALL THE CHANGES WERE ROLLED BACK.' END
--	UPDATE (00000024:000000A0:0003) done at 2020-06-10 11:09:36.293 by VIP-966\Administrator in transaction 0000:0000034B (Committed)
BEGIN TRANSACTION 
UPDATE [dbo].[TestUser] SET [Status] = 1 WHERE [Id] = 2
IF @@ROWCOUNT <= 1 COMMIT TRANSACTION ELSE BEGIN ROLLBACK TRANSACTION; PRINT 'ERROR: STATEMENT AFFECTED MORE THAN ONE ROW. ALL THE CHANGES WERE ROLLED BACK.' END
--	UPDATE (00000024:000000A0:0002) done at 2020-06-10 11:09:36.293 by VIP-966\Administrator in transaction 0000:0000034B (Committed)
BEGIN TRANSACTION 
UPDATE [dbo].[TestUser] SET [Status] = 0 WHERE [Id] = 1
IF @@ROWCOUNT <= 1 COMMIT TRANSACTION ELSE BEGIN ROLLBACK TRANSACTION; PRINT 'ERROR: STATEMENT AFFECTED MORE THAN ONE ROW. ALL THE CHANGES WERE ROLLED BACK.' END
GO
--	FINISHED ON 2020-06-10 11:18:47.697
--	TOTAL OPERATIONS PROCESSED 3
--	END OF FILE

最后我们就可以使用这个脚本去恢复数据了。

注意

我们在使用日志恢复的时候如果表有主键会根据主键生成sql,如上图sql中  where后面的条件。如果表没有主键那么生成的sql后面的where条件会带上所有的字段。在我帮小伙伴恢复数据的时候发现他的表没有设置主键,而且字段有20多个,3万多条数据生成的sql都是100多M,还要拆分执行。
比如我把Id主键去了再更新下Status状态到4,生成的sql如下,会提示没有主键。



今天不小心对数据库执行了一次误操作,心想有没有什么工具能恢复这次误操作呢?于是找到了Log Explorer 4.2,可惜它最多只支持SQL 2005,在SQL 2008上无法使用,然后又找到了ApexSQL Log,最新版本最高支持SQL 2008以及SQL 2012,试用版可以提供功能无限制14天的免费试用期,功能倒真是强大

 

直接下载安装,官方下载地址: http://www.apexsql.com/sql_tools_log.aspx

安装完成,打开主界面:

 

点击“New”:

 

输入数据库相关信息后点击“Open”:

 

点击“Evaluate Product”:

 

表格列出之前进行的操作,在进行误操作的行上点击鼠标右键会出现很多菜单,“Create Undo Script”就是创建一个恢复脚本:

 

生成此脚本后执行即可恢复误操作:






1、事务解析

SQL Server 使用Write-ahead logging (WAL)方式保证任何数据变更的日志要比数据变更先发生。在 完全恢复模式下, 对数据库中任何对象的变更操作都会被记录在日志中。注意是所有的数据对象,包括:tables, views, stored procedures, users, permissions等。

 

使用fn_dblog()查询日志记录:

USE TestDB1GOSELECT * FROM [sys].[fn_dblog](NULL,NULL)

 

插入一条语句的日志记录如下:

 

LOP_BEGIN_XACT:标记一个事务的开始,也是日志中唯一包含事务开始时间的记录,同时还包含发出语句用户的SID

LOP_COMMIT_XACT:标记一个事务的结束

LOP_LOCK_XACT:锁

LOP_INSERT_ROWSLOP_DELETE_ROWSLOP_DELETE_ROWS:记录实际的数据修改信息

 

 

2、fn_dblog()字段解析

Current LSN:当前LSN

事务日志中的每一条日志记录由LSN(Log Sequence Number)唯一标识。LSN是有序的,如果LSN2大于LSN1,则LSN2的日志所代表的数据修改操作发生在LSN1之后。

Operation:当前LSN所做的操作

Context:操作的上下文

Transactoin ID:事务ID号

Log Record Fixed Length:LSN记录的所占虚拟日志文件的固定长度

Previous LSN:前一个LSN号

 

AllocUnitID:修改的那条数据所属分配单元ID

AllocUnitName:修改了数据的表名

Page ID:数据所在页面(16进制)

                  查看page id为289页的记录: DBCC Page ({dbid|db name}, file num, page num, [print opt])

Slot ID:数据所在数据页面的第几条记录

Partition ID:数据所在数据页面的所在分区ID

数据修改操作(如LOP_INSERT_ROWS)总是会记录它操作的物理内容(PageID,SlotID)和对象:分配单元(Allocation Unit ID)和分区ID(Partition ID)。

查看AllocUnitName列是确定哪一个对象被修改的最简单的方式。

Page ID 和Slot ID告诉我们哪个页的哪一个槽位被事务修改了。

可以通过DBCC PAGE来查看页内容。

 

 

Checkpoint Begin:Checkpoint开始时间

Checkpoint Begin DB Version:当前数据库版本 SQL2005是611  SQL2012是706

Checkpoint End:checkpoint的结束时间,这个时间肯定在Checkpoint Begin的下一条事务日志记录的位置

 

Minimum LSN: 这个第一个日志记录的日志序列号 (LSN),称为最小恢复 LSN (MinLSN)

Dirty Pages:脏的数据页

 

Oldest Replicated Begin LSN:如果数据库配置复制的话,那么最老的复制起始LSN

Next Replicated End LSN:下一个复制结尾LSN

Last Distributed End LSN:最新的分发结尾LSN

SPID:执行当前操作的进程ID

Beginlog Status:开始记录事务日志的状态,这个状态表示现时能够正常记录事务日志

 

Begin Time:事务开始时间

 

Transaction Name:事务名称

事务名称分类:

(1)显式提交-命名事务:[事务名]

begin transaction tran_testuse TestDB1insert into test values(1,0)commit

(2)显式提交-未命名事务:user_transaction

begin transaction use TestDB1insert into test values(0,0)commit

(3)隐式提交-DML:INSERT、UPDATE、DELETE

use TestDB1insert into test values(0,0)

(4)隐式提交-DDL:CREATE TABLE、ALTER TABLE、DROPOBJ

useTestDB1create table test(a int,b int,primary key(a),)

SplitPage:页拆分

可以根据Parent Transaction ID获取执行页拆分的事务,页拆分是B-TREE根据排序键维护数据顺序的一种方式,CREATE TABLE插入元数据会导致内部元数据表的页拆分,

Allocate Root:页分配

正常情况,创建表时,不会分配页给它。第一个INSERT会触发分配第一个页给表。分配操作由单独的事务完成,并且会立即提交。即使触发页分配的那个INSERT事务被回滚或者延迟提交,也不会影响其它的数据插入操作。从这里也可以看出,一个会话中,可以开始和提交独立于会话主事务之外的事务的,只是这个功能没有提供给T-SQL,只是内部使用。

 

 

Transaction SID:启动事务的登录名的SID,可以用SUSER_SNAME()函数获取到实际的登录名。

End Time:事务结束时间

Transaction Begin:记录这个事务的begin transaction的时候的cureent LSN

 

Master DBID:显示当前master数据库的DBID

Preplog Begin LSN:启动数据库前的前一个事务日志LSN

Prepare Time:准备启动数据库的时间

New Split Page:哪个数据页产生了页拆分

Rows Deleted:数据页有多少行被删除了

Description:描述这个事务是干什么的,有时候事务名称不一定就是他所做的操作名称,需要通过Description指示

 

Lock Information

Lock Information列的完整内容:

其中有表的Object ID,页ID和索引键的键锁信息。

对于B-TREE,键锁就是键值的HASH值,所以通过它能定位到数据行(就算发生页拆分,但是键值是不会变的)。

通过键锁信息,我们正确定位到第一个INSERT的行。

注意,HASH值存在HASH碰撞的可能,即不同的键值生成了同样的HASH值。碰撞的概率是非常低的,如果发生, 上面的查询会返回多行。

Offset in Row

RowLog Contents 0

RowLog Contents 1

RowLog Contents 2

RowLog Contents 3

Log Record

 

 

3、Operation与Context

Operation:当前LSN所做的操作

Context:操作的上下文

 

Operation

Context

解释

LOP_SET_BITS

LCX_DIFF_MAP

设置位图,资料: 差异(Differential)备份:只备份上次完整备份后,做修改的部分。备份单位是区(Extent)。意味着某个区内即使只有一页做了变动,则在差异备份里会被体现.差异备份依靠一个BitMap进行维护,一个Bit对应一个区,自上次完整备份后,被修改的区会被置为1,而BitMap中被置为1对应的区会被差异备份所备份。而到下一次完整备份后,BitMap中所有的Bit都会被重置为0

而这个BitMap在数据库第7页:

DCM页 差异变更(Differential Changed Map,DCM)页面他跟踪一个文件中的哪一个区在最新一次完整数据库备份之后被修改过。SQLSERVER用在增量备份时只对已发生数据变更的分区进行增量备份即可

LOP_BEGIN_XACT

事务开始

LOP_MODIFY_ROW

LCX_HEAP

修改堆表中的某一行记录

LOP_PREP_XACT

准备启动数据库

LOP_COMMIT_XACT

提交事务

LOP_MODIFY_ROW

LCX_BOOT_PAGE

修改数据库启动页

LOP_MODIFY_HEADER

LCX_PFS

修改PFS页的页头部信息

LOP_INSERT_ROWS

LCX_CLUSTERED

插入数据到聚集索引的索引页

LOP_INSERT_ROWS

LCX_INDEX_LEAF

插入数据到索引的叶子节点即数据页

LOP_FORMAT_PAGE

LCX_CLUSTERED

重新组织聚集索引

LOP_DELETE_SPLIT

LCX_CLUSTERED

删除聚集索引表的一行记录引起页拆分

LOP_MODIFY_HEADER

LCX_HEAP

修改堆表的某页的页头信息

LOP_BEGIN_CKPT

LCX_NULL

检查点开始

LOP_END_CKPT

LCX_NULL

检查点结束

LOP_SET_FREE_SPACE

LCX_PFS

修改PFS页设置那个数据页是空闲的

LOP_ROOT_CHANGE

LCX_CLUSTERED

聚集索引的根节点改变

LOP_INSERT_ROWS

LCX_HEAP

插入数据到堆表 

LOP_FORMAT_PAGE

LCX_HEAP

格式化堆里的数据页

LOP_LOCK_XACT

HoBt 0:ACQUIRE_LOCK_SCH_M METADATA: database_id = 14   STATS(object_id = 7, stats_id = 11)

在事务里获取锁

 

 

1)用系统函数

select * from fn_dblog(null,null)

2)用DBCC

dbcc log(dbname,4) --(n=0,1,2,3,4)

1 - 更多信息plus flags, tags, row length

2 - 非常详细的信息plus object name, index name,page id, slot id

3 - 每种操作的全部信息

4 - 每种操作的全部信息加上该事务的16进制信息


默认 type = 0


要查看MSATER数据库的事务日志可以用以下命令:

DBCC log (master)


DBCC LOG
To retrieve the transaction log for a given database.
对应日志文件较大的数据库,慎用该命令
Uasge:
DBCC LOG(<dbid|dbname>,<formatid>)

formatid:
0: Return only the minimum of information for each operation -- the operation, its context and the transaction ID. (Default)
1: As 0, but also retrieve any flags and the log record length.
2: As 1, but also retrieve the object name, index name, page ID and slot ID.
3: Full informational dump of each operation.
4: As 3 but includes a hex dump of the current transaction log row.

--==============================================================================

dbcc log [ (@dbid, @objid, @pagenum, @rownum, @records, @type [, @printopt]) ]

dbcc log (5, 0, 0, 0, -1, 0, 1) // Show the last begin transaction record in the log

Parameters:
@dbid Database ID
@objid Object ID
A negative value indicates that @pagenum & @rownum represent a row in the log to use as a starting point in the scan of the log.
A value of zero indicates that log records for changes to @pagenum will be included in the commands output.
A positive value followed by a non-zero value for @pagenum indicates that @pagenum and @rownum represent a transaction ID. Log records for that transaction will be included in the output.
A positive value followed by zero values for @pagenum and @rownum indicates an object ID. Log records for changes to that object will be included in the output.
@pagenum page number
@rownum row number in the log
Together with @pagenum, this is either a starting point in a scan of the log or a transaction id.
@records number of records to examine. If positive, the first
@type  
@printopt

--==============================================================================

使用fn_dblog或sys.fn_dump_dblog来查看日志

SELECT * FROM sys.fn_dblog(@StartingLSN, @EndingLSN)

@StartingLSN和@EndingLSN可以为null





Github URL:  https://github.com/ap0405140/MSSQLLogAnalyzer 

在SQL Server 2005及之前的版本, 可以用Log Explorer工具来分析读取数据库日志, 但据我所知, SQL2008及之后的版本, Log Explorer工具暂时无法支持.

 因此,我们可以自己开发工具来实现分析读取数据库日志的目的,大致思路如下: 

  1.使用系统函数sys.fn_dblog()读取原始日志信息.

  2.通过系统表sys.tables,sys.columns,sys.systypes等获取表结构及数据类型.

  3.分析数据库日志,得到 日志中每步操作对应的正向SQL(Redo)和反向SQL(Undo).

详细的实验步骤如下:

1.建测试表dbo.OrderDetail.

create table dbo.OrderDetail(OrderID int not null,ItemID int not null,ItemNumber varchar(10),QTY int,Price decimal(8,2),ADate date,AUser char(20),UDate datetime,UUser varchar(20)constraint pk_OrderDetail primary key(OrderID,ItemID))

2. 操作1: 新增3行

-- 操作1: 新增3行insert into dbo.OrderDetail(OrderID,ItemID,ItemNumber,QTY,Price,ADate,AUser,UDate,UUser)select 1001,1,'D001',100,45.62,'2015-01-02','Xh6','2015-01-03 20:15:18','Lx4'  union allselect 1001,2,'Z001_2',150,180,'2015-01-02','cx5','2015-01-08 02:45:32','Yx3' union allselect 1002,1,'Z001_2',300,182.07,'2015-12-12','CL1','2015-12-18 02:45:32','LY6'select * from dbo.OrderDetail

结果如下:

3. 操作2: 更新1行

-- 操作2: 更新1行update dbo.OrderDetail set QTY=999 where OrderID=1001 and ItemID=1select * from dbo.OrderDetail

结果如下:

4.操作3: 更新3行

-- 操作3: 更新3行update dbo.OrderDetail set ItemNumber='!@#$%'select * from dbo.OrderDetail

结果如下:

5. 操作4: 删除3行

-- 操作4: 删除3行delete from dbo.OrderDetailselect * from dbo.OrderDetail


结果如下:

此时, 目标表已为空, 接下来, 我们通过MSSQLLogAnalyzer.exe工具来实现在线恢复.

 

恢复步骤如下:

下载或克隆demo程序包到本地, Github URL:  https://github.com/ap0405140/MSSQLLogAnalyzer 

1. 执行MSSQLLogAnalyzer.exe, 

1.1 修改数据库连接串(ConnectionString)参数为实际环境值, 连接目标数据库.

1.2 修改需解析日志的时间范围, 即开始时间(StartTime)和结束时间(EndTime).

1.3 表名(TableName)可以为空, 代表将解析所有表的日志.

1.4 点击[Readlog]按钮, 等待解析结果.

执行结果:

从上图可以看到, 返回了正向SQL(RedoSQL)和反向SQL(UndoSQL), 

2.复制出所有反向SQL(UndoSQL)的值.  因结果默认是按时间升序排列, 恢复时需注意按时间降序排列执行反向SQL(UndoSQL), 即自下往上.

3.恢复各个操作, 执行反向SQL(UndoSQL).

-- 恢复原操作4(删除3行) insert into dbo.OrderDetail([OrderID],[ItemID],[ItemNumber],[QTY],[Price],[ADate],[AUser],[UDate],[UUser]) values(1002, 1, '!@#$%', 300, 182.07, '2015-12-12', 'CL1', '2015-12-18 02:45:32.000', 'LY6');insert into dbo.OrderDetail([OrderID],[ItemID],[ItemNumber],[QTY],[Price],[ADate],[AUser],[UDate],[UUser]) values(1001, 2, '!@#$%', 150, 180.00, '2015-01-02', 'cx5', '2015-01-08 02:45:32.000', 'Yx3');insert into dbo.OrderDetail([OrderID],[ItemID],[ItemNumber],[QTY],[Price],[ADate],[AUser],[UDate],[UUser]) values(1001, 1, '!@#$%', 999, 45.62, '2015-01-02', 'Xh6', '2015-01-03 20:15:18.000', 'Lx4');-- 恢复原操作3(更新3行)update dbo.OrderDetail set [ItemNumber]='Z001_2' where [OrderID]=1002 and [ItemID]=1update dbo.OrderDetail set [ItemNumber]='Z001_2' where [OrderID]=1001 and [ItemID]=2update dbo.OrderDetail set [ItemNumber]='D001' where [OrderID]=1001 and [ItemID]=1-- 恢复原操作2(更新1行)update dbo.OrderDetail set [QTY]=100 where [OrderID]=1001 and [ItemID]=1

查看恢复结果, 

select * from dbo.OrderDetail

结果如下, 即回到原操作1之后的结果, 恢复完成!




About Me

........................................................................................................................

● 本文作者:小麦苗,部分内容整理自网络,若有侵权请联系小麦苗删除

● 本文在个人微 信公众号( DB宝)上有同步更新

● QQ群号: 230161599 、618766405,微信群私聊

● 个人QQ号(646634621),微 信号(db_bao),注明添加缘由

● 于 2020年11月完成

● 最新修改时间:2020年11月

● 版权所有,欢迎分享本文,转载请保留出处

........................................................................................................................

小麦苗的微店https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

● 小麦苗出版的数据库类丛书: http://blog.itpub.net/26736162/viewspace-2142121/

小麦苗OCP、OCM、高可用、MySQL、DBA学习班http://blog.itpub.net/26736162/viewspace-2148098/

● 数据库笔试面试题库及解答: http://blog.itpub.net/26736162/viewspace-2134706/

........................................................................................................................

请扫描下面的二维码来关注小麦苗的微 信公众号( DB宝)及QQ群(230161599、618766405)、添加小麦苗微 信(db_bao), 学习最实用的数据库技术。

........................................................................................................................

 

 



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26736162/viewspace-2737060/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
【QQ:646634621】【微信:db_bao】【微信公众号:DB宝】【11g、12c OCM】【QQ群:230161599、618766405】【《数据库笔试面试宝典》作者】【OCP、OCM、高可用(RAC+DG+OGG)、MySQL培训班已开讲,只讲实用内容】

注册时间:2012-09-23

  • 博文量
    1586
  • 访问量
    9170510