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 all
select 1001,2,'Z001_2',150,180,'2015-01-02','cx5','2015-01-08 02:45:32','Yx3' union all
select 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=1
select * 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.OrderDetail
select * 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]=1
update dbo.OrderDetail set [ItemNumber]='Z001_2' where [OrderID]=1001 and [ItemID]=2
update 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之后的结果, 恢复完成!