SQL SERVER 无需备份恢复数据

轻梦

已于 2023-11-20 09:52:22 修改

阅读量114

点赞数

文章标签：数据库 sqlserver golang

于 2023-11-20 09:38:46 首次发布

本文链接：https://blog.csdn.net/tianyuso/article/details/134500405

版权

前提准备：

本项目使用golang语言开发，无需备份，借助fn_dblog函数解析sqlserver 日志，返回前滚SQL和回滚SQL.类似借助mysql binlog，实现mysql数据回滚的原理。下载地址：GitHub - tianyuso/mssql2sqlContribute to tianyuso/mssql2sql development by creating an account on GitHub.https://github.com/tianyuso/mssql2sql或者tianyuso/mslog2sqlhttps://gitee.com/tianyuso/mslog2sql

1、理论上支持sqlserver 2008-2019版本，未完全测试所有版本。

2、暂时不支持image,varbinary,xml类型解析，后续会慢慢添加

3、暂时不支持列存储格式，图类型等，后续会慢慢添加

4、只支持DML语句（update,insert,delete）

5、ldf过大时，fn_dblog读取会日志会有很大的影响，恢复速度会比较慢。

6、源码后续考虑开源。

7、防止恶意程序，请执行前核对md5值是否和程序内的.MD5文件一致，

linux MD5命令：md5sum mslog2sql

windows MD5命令：certutil -hashfile mslog2sql.exe MD5

8、支持windows和linux平台使用

[使用举例]
1、准备sqlserver 环境，以sqlserver 2017为例
2、测试表如下：
CREATE TABLE [dbo].[t_cdc_t6](
[id] [int] NOT NULL,
[name] varchar NULL,
[addr] nvarchar NULL,
[ttime] [datetime] NULL,
[mess] varchar NULL,
[newcon] nvarchar NULL,
[col1] [bit] NULL,
[col2] nchar NULL,
[ctime] datetime2 NULL,
[tint] [tinyint] NULL,
[flcol] [float] NULL,
[numer] [decimal](6, 2) NULL,
[dmoney] [money] NULL,
CONSTRAINT [PK_tcdc_t6] PRIMARY KEY NONCLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
3、测试dml语句如下：
insert into [t_cdc_t6] ([Id], [name], [addr], [ttime], [mess], [newcon], [col1],[col2],[ctime],tint,flcol,numer,dmoney)
values('1','ac','武汉','2023-09-06 09:55:52','tohi','new3',1,'col2s1','2023-09-07 09:55:52.322211',11,98.32,1001.32,1001.32);
insert into [t_cdc_t6] ([Id], [name], [addr], [ttime], [mess], [newcon], [col1],[col2],[ctime],tint,flcol,numer,dmoney)
values('2','tc','广州','2023-09-06 09:55:52','hi','new1',1,'col2s2','2023-09-08 09:55:52.322211',11,99.32,1002.32,1002.32);
insert into [t_cdc_t6] ([Id], [name], [addr], [ttime], [mess], [newcon], [col1],[col2],[ctime],tint,flcol,numer,dmoney)
values('3','ab','武汉','2023-09-06 09:55:52','hi1','new2',1,'col2s3','2023-09-10 09:55:52.322211',11,100.32,1003.32,1003.32);
insert into [t_cdc_t6] ([Id], [name], [addr], [ttime], [mess], [newcon], [col1],[col2],[ctime],tint,flcol,numer,dmoney)
values('4','bc','长沙','2023-09-06 09:55:52','go','new5',1,'col2s4','2023-09-11 09:55:52.322211',11,101.32,1004.32,1004.32);
insert into [t_cdc_t6] ([Id], [name], [addr], [ttime], [mess], [newcon], [col1],[col2],[ctime],tint,flcol,numer,dmoney)
values('5','ca','邯郸','2023-09-06 09:55:52','hi','new1',1,'col2s5','2023-09-12 09:55:52.322211',11,102.32,1005.32,1005.32);
insert into [t_cdc_t6] ([Id], [name], [addr], [ttime], [mess], [newcon], [col1],[col2],[ctime],tint,flcol,numer,dmoney)
values('6','cb','深圳','2023-09-25 09:55:52','hi1','new1',1,'col2s6','2023-09-13 09:55:52.322211',11,103.32,1006.32,1006.32);
insert into [t_cdc_t6] ([Id], [name], [addr], [ttime], [mess], [newcon], [col1],[col2],[ctime],tint,flcol,numer,dmoney)
values('7','cc','北京','2023-09-25 09:55:52','hi1','new1',1,'col2s7','2023-09-14 09:55:52.322211',11,104.32,1007.32,1007.32);
insert into [t_cdc_t6] ([Id], [name], [addr], [ttime], [mess], [newcon], [col1],[col2],[ctime],tint,flcol,numer,dmoney)
values('8','cd','天津','2023-09-25 09:55:52','hi1','new1',1,'col2s8','2023-09-15 09:55:52.322211',11,107.32,1008.32,1008.32);
update [t_cdc_t6] set addr ='hangzhou' where name ='ca';
update [t_cdc_t6] set addr ='shenzhen' where name ='cd';
update [t_cdc_t6] set addr ='handan' where name ='ca';
insert into [t_cdc_t6] ([Id], [name], [addr], [ttime], [mess], [newcon], [col1],[col2],[ctime],tint,flcol,numer,dmoney)
values('9','aa','苏州','2023-09-25 09:55:52','hi1','new1',1,'col2s9','2023-09-15 09:55:52.322211',11,100.32,1009.32,1009.32);
insert into [t_cdc_t6] ([Id], [name], [addr], [ttime], [mess], [newcon], [col1],[col2],[ctime],tint,flcol,numer,dmoney)
values('10','aa','杭州','2023-09-25 09:55:52','hi1','new1',1,'col2s10','2023-09-15 09:55:52.322211',11,100.32,1010.32,1010.32);
insert into [t_cdc_t6] ([Id], [name], [addr], [ttime], [mess], [newcon], [col1],[col2],[ctime],tint,flcol,numer,dmoney)
values('11','cq','重庆','2023-09-25 09:55:52','hi1','new1',1,'col2s11','2023-09-15 09:55:52.322211',11,101.32,1010.32,1011.32);
delete from t_cdc_t6 where name='cc';

3、配置config.toml参数如下：
[mssql]
username = "sc" #建议使用sys权限用户，最低权限需要能创建临时表，已经查询fn_dblog权限
password = "password"
host = "10.10.10.12"
port = 1433
dbname = "test1"
schema = "dbo"
#start time for analyze, format: yyyy-MM-dd HH:mm:ss
starttime="2023-10-31 14:40:02"
#end time for analyze, format: yyyy-MM-dd HH:mm:ss
endtime ="2023-10-31 14:47:30"
include-table = ["t_cdc_t6"]
#easycopy=true
4、执行 ./mslog2sql
[root@hana01 mslog2sql]# ./mslog2sql
2023/11/17 15:52:35 ------------------ begin print parameter ---------------------------------
Host:10.10.10.12
Port:1433
Dbname:test1
Table:dbo.t_cdc_t6
Username:sc
Password:password
StartTime:2023-10-31 14:40:02
EndTime:2023-10-31 14:47:30
EasyCopy:false
2023/11/17 15:52:35 ------------------ end print paramete ---------------------------------
2023/11/17 15:52:36 get transaction log range success.. 2023/11/17 15:52:37 get transaction log success.. 2023/11/17 15:52:37 begin to parse transaction log .. 2023/11/17 15:52:37 [-- begin to analyze transac:0000:00000456 operation:LOP_INSERT_ROWS --] 2023/11/17 15:52:37 [-- end transac:0000:00000456 operation:LOP_INSERT_ROWS --] 2023/11/17 15:52:37 [-- begin to analyze transac:0000:00000455 operation:LOP_DELETE_ROWS --] 2023/11/17 15:52:37 [-- end transac:0000:00000455 operation:LOP_DELETE_ROWS --] 2023/11/17 15:52:37 [-- begin to analyze transac:0000:00000455 operation:LOP_DELETE_ROWS --] 2023/11/17 15:52:37 [-- end transac:0000:00000455 operation:LOP_DELETE_ROWS --] 2023/11/17 15:52:37 [-- begin to analyze transac:0000:00000454 operation:LOP_INSERT_ROWS --] 2023/11/17 15:52:37 [-- end transac:0000:00000454 operation:LOP_INSERT_ROWS --] 2023/11/17 15:52:37 [-- begin to analyze transac:0000:00000453 operation:LOP_INSERT_ROWS --] 2023/11/17 15:52:37 [-- end transac:0000:00000453 operation:LOP_INSERT_ROWS --] 2023/11/17 15:52:37 [-- begin to analyze transac:0000:00000452 operation:LOP_MODIFY_ROW --] 2023/11/17 15:52:37 [-- end transac:0000:00000452 operation:LOP_MODIFY_ROW --] 2023/11/17 15:52:37 [-- begin to analyze transac:0000:00000451 operation:LOP_MODIFY_ROW --] 2023/11/17 15:52:37 [-- end transac:0000:00000451 operation:LOP_MODIFY_ROW --] 2023/11/17 15:52:37 [-- begin to analyze transac:0000:00000450 operation:LOP_MODIFY_ROW --] 2023/11/17 15:52:37 [-- end transac:0000:00000450 operation:LOP_MODIFY_ROW --] 2023/11/17 15:52:37 [-- begin to analyze transac:0000:0000044f operation:LOP_INSERT_ROWS --] 2023/11/17 15:52:37 [-- end transac:0000:0000044f operation:LOP_INSERT_ROWS --] 2023/11/17 15:52:37 [-- begin to analyze transac:0000:0000044e operation:LOP_INSERT_ROWS --] 2023/11/17 15:52:37 [-- end transac:0000:0000044e operation:LOP_INSERT_ROWS --] 2023/11/17 15:52:37 [-- begin to analyze transac:0000:0000044d operation:LOP_INSERT_ROWS --] 2023/11/17 15:52:37 [-- end transac:0000:0000044d operation:LOP_INSERT_ROWS --] 2023/11/17 15:52:37 [-- begin to analyze transac:0000:0000044c operation:LOP_INSERT_ROWS --] 2023/11/17 15:52:37 [-- end transac:0000:0000044c operation:LOP_INSERT_ROWS --] 2023/11/17 15:52:37 [-- begin to analyze transac:0000:0000044b operation:LOP_INSERT_ROWS --] 2023/11/17 15:52:37 [-- end transac:0000:0000044b operation:LOP_INSERT_ROWS --] 2023/11/17 15:52:37 [-- begin to analyze transac:0000:0000044a operation:LOP_INSERT_ROWS --] 2023/11/17 15:52:37 [-- end transac:0000:0000044a operation:LOP_INSERT_ROWS --] 2023/11/17 15:52:37 [-- begin to analyze transac:0000:00000449 operation:LOP_INSERT_ROWS --] 2023/11/17 15:52:37 [-- end transac:0000:00000449 operation:LOP_INSERT_ROWS --] 2023/11/17 15:52:37 [-- begin to analyze transac:0000:00000445 operation:LOP_FORMAT_PAGE --] 2023/11/17 15:52:37 [-- end transac:0000:00000445 operation:LOP_FORMAT_PAGE --] 2023/11/17 15:52:37 [-- begin to analyze transac:0000:00000445 operation:LOP_FORMAT_PAGE --] 2023/11/17 15:52:38 [-- end transac:0000:00000445 operation:LOP_FORMAT_PAGE --] 2023/11/17 15:52:38 [-- begin to analyze transac:0000:00000444 operation:LOP_INSERT_ROWS --] 2023/11/17 15:52:38 [-- end transac:0000:00000444 operation:LOP_INSERT_ROWS --] 2023/11/17 15:52:38 parse transaction log finished

5、将config.toml文件#easycopy=true修改为easycopy=true，再次执行./mslog2sql，会单独将回滚SQL输出，方便执行回滚操作。
Easy EXEC SQL:
[--------------------------------- begin -------------------------------------------------]
// LSN:2023/10/31 14:46:58, Transcation BeginTime:00000024:00000589:0005 UNDOSQL:
delete from [dbo].[t_cdc_t6] where [id]=11;
// LSN:2023/10/31 14:46:16, Transcation BeginTime:00000024:00000587:0005 UNDOSQL:
insert into [dbo].t_cdc_t6 values(7,'cc',N'北京','2023-09-25 09:55:52.000','hi1',N'new1',1,N'col2s7','2023-09-14 09:55:52.3222110',11,104.32,1007.32,1007.3200);
// LSN:2023/10/31 14:45:26, Transcation BeginTime:00000024:00000585:0002 UNDOSQL:
delete from [dbo].[t_cdc_t6] where [id]=10;
// LSN:2023/10/31 14:45:26, Transcation BeginTime:00000024:00000583:0002 UNDOSQL:
delete from [dbo].[t_cdc_t6] where [id]=9;
// LSN:2023/10/31 14:45:18, Transcation BeginTime:00000024:00000582:0002 UNDOSQL:
update [dbo].[t_cdc_t6] set [addr]=N'hangzhou' where [id]=5;
// LSN:2023/10/31 14:45:13, Transcation BeginTime:00000024:00000581:0002 UNDOSQL:
update [dbo].[t_cdc_t6] set [addr]=N'天津' where [id]=8;
// LSN:2023/10/31 14:44:48, Transcation BeginTime:00000024:00000580:0002 UNDOSQL:
update [dbo].[t_cdc_t6] set [addr]=N'邯郸' where [id]=5;
// LSN:2023/10/31 14:44:41, Transcation BeginTime:00000024:0000057e:0002 UNDOSQL:
delete from [dbo].[t_cdc_t6] where [id]=8;
// LSN:2023/10/31 14:44:41, Transcation BeginTime:00000024:0000057c:0002 UNDOSQL:
delete from [dbo].[t_cdc_t6] where [id]=7;
// LSN:2023/10/31 14:44:35, Transcation BeginTime:00000024:0000057a:0002 UNDOSQL:
delete from [dbo].[t_cdc_t6] where [id]=6;
// LSN:2023/10/31 14:44:35, Transcation BeginTime:00000024:00000578:0002 UNDOSQL:
delete from [dbo].[t_cdc_t6] where [id]=5;
// LSN:2023/10/31 14:44:35, Transcation BeginTime:00000024:00000576:0002 UNDOSQL:
delete from [dbo].[t_cdc_t6] where [id]=4;
// LSN:2023/10/31 14:44:19, Transcation BeginTime:00000024:00000574:0002 UNDOSQL:
delete from [dbo].[t_cdc_t6] where [id]=3;
// LSN:2023/10/31 14:44:19, Transcation BeginTime:00000024:00000572:0002 UNDOSQL:
delete from [dbo].[t_cdc_t6] where [id]=2;
// LSN:2023/10/31 14:44:19, Transcation BeginTime:00000024:00000569:001a UNDOSQL:
delete from [dbo].[t_cdc_t6] where [id]=1;
[--------------------------------- end -------------------------------------------------]

6、命令行模式,需要将mode参数设置为cmd，可以使用命令行模式执行恢复程序，举例如下：
./mslog2sql -mode='cmd' -db='test1' -host='10.10.10.12' -port=1433 -user 'sc' -pwd 'password' -schema 'dbo' -table 't_cdc_test6' -starttime '2023-10-30 17:45:02' -endtime '2023-11-15 17:50:30' -easycopy true