系列文章
C#底层库--SQLBuilder脚本构建类(select、insert、update、in、带条件的SQL自动生成)
本文链接:[https://blog.csdn.net/youcheng_ge/article/details/129179216](https://blog.csdn.net/youcheng_ge/article/details/129179216)
C#底层库--MySQL数据库操作辅助类(推荐阅读)
本文链接:[https://blog.csdn.net/youcheng_ge/article/details/126886379](https://blog.csdn.net/youcheng_ge/article/details/126886379)
C#底层库--SQLite的使用(小型、本地数据库)
本文链接:[https://blog.csdn.net/youcheng_ge/article/details/123666958](https://blog.csdn.net/youcheng_ge/article/details/123666958)
【提高编程效率】Excel数据批量导入数据库
本文链接:[https://blog.csdn.net/youcheng_ge/article/details/126427323](https://blog.csdn.net/youcheng_ge/article/details/126427323)
【Oracle】数据库服务器限制ip访问
本文链接:[https://blog.csdn.net/youcheng_ge/article/details/122220930](https://blog.csdn.net/youcheng_ge/article/details/122220930)
【Oracle】Excel导入数据教程
本文链接:[https://blog.csdn.net/youcheng_ge/article/details/118722756](https://blog.csdn.net/youcheng_ge/article/details/118722756)
【Oracle】数据库还原教程_数据泵
本文链接:[https://blog.csdn.net/youcheng_ge/article/details/118054855](https://blog.csdn.net/youcheng_ge/article/details/118054855)
【SQL】如何查询表字段并识别主键
本文链接:[https://blog.csdn.net/youcheng_ge/article/details/110820405](https://blog.csdn.net/youcheng_ge/article/details/110820405)
【SQL】outer apply的用法
本文链接:[https://blog.csdn.net/youcheng_ge/article/details/79903489](https://blog.csdn.net/youcheng_ge/article/details/79903489)
【SQL】多表连接重复数据处理
本文链接:[https://blog.csdn.net/youcheng_ge/article/details/79903619](https://blog.csdn.net/youcheng_ge/article/details/79903619)
MySQL安装教程(详细)
本文链接:[https://blog.csdn.net/youcheng_ge/article/details/126037520](https://blog.csdn.net/youcheng_ge/article/details/126037520)
MySQL卸载教程(详细)
本文链接:[https://blog.csdn.net/youcheng_ge/article/details/129279265](https://blog.csdn.net/youcheng_ge/article/details/129279265)
【MySQL】group by分类汇总,如何增加“总计”字段?
本文链接:[https://blog.csdn.net/youcheng_ge/article/details/128217837](https://blog.csdn.net/youcheng_ge/article/details/128217837)
【MySQL】WITH CHECK OPTION的用法
本文链接:[https://blog.csdn.net/youcheng_ge/article/details/128147196](https://blog.csdn.net/youcheng_ge/article/details/128147196)
【MySQL】使用存储过程插入千万级数据如何提升效率?
本文链接:[https://blog.csdn.net/youcheng_ge/article/details/77728189](https://blog.csdn.net/youcheng_ge/article/details/77728189)
【MySQL】数据库表行列转置的实现
本文链接:[https://blog.csdn.net/youcheng_ge/article/details/77625052](https://blog.csdn.net/youcheng_ge/article/details/77625052)
【MySQL】查询中,NULL值转换为空字符串
本文链接:[https://blog.csdn.net/youcheng_ge/article/details/130381878](https://blog.csdn.net/youcheng_ge/article/details/130381878)
前言
本专栏为【数据库】,主要介绍SQL的功能与特点、SQL数据定义语言(表、视图、索引、约束)、SQL数据操作语言(数据检索、数据插入、数据删除、数据更新)、创建与删除触发器、SQL数据控制语言(安全性和授权、事务处理)以及嵌入式SQL。
如果你对本专辑感兴趣,持续关注吧。大家有任何问题,也可以评论区反馈,私信我。
一、技术介绍
增量查询
二、解决方案
alter PROC [dbo].[PBak_BackUpTotal]
AS
declare @LogGuid varchar(40),@BackUpDateTime datetime,@ToAddData_date datetime,@ToBakData_date datetime
declare @ExceptCheck_date datetime,@ExceptCheck_count int,@SumNumCheck_date datetime,@SumNumCheck_count int
declare @BackUpAddData_date datetime,@AddData_filename varchar(100),@BackUpGuid varchar(40)
declare @database_name varchar(50),@databak_name varchar(50),@dataadd_name varchar(50)
declare @Temp_Path varchar(200),@rar_Path varchar(200)
declare @Sql varchar(4000),@IsReadOnly int,@backup_date datetime,@BackUpLocation varchar(40),@RestoreLocation varchar(40)
set @database_name=(select database_name from wsbakconfig..BakSysPar)
set @databak_name=(select databak_name from wsbakconfig..BakSysPar)
set @dataadd_name=(select dataadd_name from wsbakconfig..BakSysPar)
set @Temp_Path=(select Temp_Path from wsbakconfig..BakSysPar)
set @rar_Path=(select rar_Path from wsbakconfig..BakSysPar)
set @IsReadOnly=(select is_readonly from wsbakconfig..BakSysPar)
set @backup_date=(select backup_date from wsbakconfig..BakSysPar)
set @BackUpGuid=(select backup_guid from wsbakconfig..BakSysPar)
set @AddData_filename=(select add_file_name from wsbakconfig..BakSysPar)
set @BackUpLocation=(select database_location from wsbakconfig..BakSysPar)
set @RestoreLocation=(select restore_location from wsbakconfig..BakSysPar)
if @IsReadOnly=0
begin
declare @Add_BackUpLocation varchar(40),@Add_RestoreLocation varchar(40),@Add_BackupGuid varchar(40)
if OBJECT_ID ('tempdb..#tbBakRestoreGuid') is not null
begin
drop table #tbBakRestoreGuid
end
create table #tbBakRestoreGuid
(backup_location varchar(40) null,
backup_guid varchar(40) null,
restore_location varchar(40) null
)
set @sql='insert into #tbBakRestoreGuid (backup_location,backup_guid,restore_location)'+char(13)
+' select backup_location,backup_guid,restore_location from '+@dataadd_name+'..BakRestoreGuid '
exec (@sql)
set @Add_BackUpLocation=(select backup_location from #tbBakRestoreGuid)
set @Add_RestoreLocation=(select restore_location from #tbBakRestoreGuid)
set @Add_BackupGuid=(select backup_guid from #tbBakRestoreGuid)
if @BackUpLocation<>@Add_BackUpLocation and @BackUpLocation=@Add_RestoreLocation and @BackUpGuid<>@Add_BackupGuid
begin
set @LogGuid=NEWID()
set @BackUpDateTime=GETDATE()
---1.1设置数据库只读
exec PBak_SetReadOnly @DataBase=@database_name
---1.2提取增量数据到Add
set @sql=' delete from '+@dataadd_name+'..BakRestoreGuid'
exec(@sql)
set @Sql=' insert into '+@dataadd_name+'..BakRestoreGuid '+char(13)
+'(backup_location,backup_guid,backup_date,restore_location,restore_guid,restore_date)'+CHAR(13)
+' select '+CHAR(39)+@BackUpLocation+CHAR(39)+' as backup_location,'+char(13)
+CHAR(39)+@LogGuid+CHAR(39)+' as backup_guid,'+char(13)
+CHAR(39)+CONVERT(varchar(100), @BackUpDateTime, 20)+CHAR(39)+' as backup_date,'+char(13)
+CHAR(39)+@RestoreLocation+CHAR(39)+' as restore_location,'+char(13)
+char(39)+char(39)+' as restore_guid,'+char(39)+char(39)+' as restore_date'
exec (@Sql)
exec PBak_ToAddData @DataBase=@database_name,@DataBaseAdd=@dataadd_name,@DataBaseBak=@databak_name
set @ToAddData_date=GETDATE()
---1.3还原Bak增量数据
exec PBak_ToBakData @DataBaseAdd=@dataadd_name,@DataBaseBak=@databak_name
set @ToBakData_date=GETDATE()
---1.4减法归零Except比对检查
exec PBak_ExceptCheck @DataBase=@database_name,@DataBaseBak=@databak_name,@CheckCount=@ExceptCheck_count output
if @ExceptCheck_count<>0
begin
select '减法归零Except比对检查存在错误数据!' + CAST(@SumNumCheck_count as varchar(100)) as msg
end
set @ExceptCheck_date=GETDATE()
---1.5合计数量比对检查
exec PBak_SumNumCheck @DataBase=@database_name,@DataBaseBak=@databak_name,@CheckCount=@SumNumCheck_count output
if @SumNumCheck_count<>0
begin
select '合计数量比对检查存在错误数据!' + CAST(@SumNumCheck_count as varchar(100)) as msg
end
set @SumNumCheck_date=GETDATE()
---1.6清理日志、备份及压缩数据库
exec PBak_BackUpAddData @DataBaseBak=@databak_name,@DataBaseAdd=@dataadd_name
,@TempPath=@Temp_Path,@RarPath=@rar_Path,@filename=@AddData_filename output
set @BackUpAddData_date=GETDATE()
---1.7更新操作日志
set @Sql='insert into wsbakconfig..BakSysLog '+char(13)
+' ([log_guid],[log_type],[log_date],[ToAddData_date],[ToBakData_date],[ToBasedata_date]'+CHAR(13)
+' ,[ExceptCheck_date],[ExceptCheck_count],[SumNumCheck_date],[SumNumCheck_count] '+CHAR(13)
+' ,[BackUpAddData_date],[AddData_filename],[RESTOREAddData_date]) '+CHAR(13)
+' SELECT '+char(39)+@LogGuid+CHAR(39)+' as [log_guid],'+char(39)+'BackUp'+char(39)+' as [log_type],'+CHAR(13)
+char(39)+ CONVERT(varchar(100), @BackUpDateTime, 20)+char(39)+' as [log_date],'+CHAR(13)
+char(39)+ CONVERT(varchar(100), @ToAddData_date, 20)+char(39)+' as [ToAddData_date],'+CHAR(13)
+char(39)+ CONVERT(varchar(100), @ToBakData_date, 20)+char(39)+' as [ToBakData_date],'+CHAR(13)
+char(39)+char(39)+' as [ToBasedata_date],'+CHAR(13)
+char(39)+ CONVERT(varchar(100), @ExceptCheck_date, 20)+char(39)+' as [ExceptCheck_date],'+CHAR(13)
+ CONVERT(varchar(10), @ExceptCheck_count)+' as [ExceptCheck_count],'+CHAR(13)
+char(39)+ CONVERT(varchar(100), @SumNumCheck_date, 20)+char(39)+' as [SumNumCheck_date],'+CHAR(13)
+ CONVERT(varchar(10), @SumNumCheck_count)+' as [SumNumCheck_count],'+CHAR(13)
+char(39)+CONVERT(varchar(100), @BackUpAddData_date, 20)+char(39)+' as [BackUpAddData_date],'+CHAR(13)
+char(39)+@AddData_filename+char(39)+' as [AddData_filename],'+CHAR(13)
+CHAR(39)+CHAR(39)+' as [RESTOREAddData_date]'
exec (@Sql)
set @Sql='update main set backup_guid='+CHAR(39)+@LogGuid+CHAR(39)
+',add_file_name='+CHAR(39)+@AddData_filename+CHAR(39)
+',backup_date='+char(39)+ CONVERT(varchar(100), @BackUpDateTime, 20)+char(39)
+',is_readonly=1 from wsbakconfig..BakSysPar main '
exec (@Sql)
select '备份完成!文件名为:'+@Temp_Path+@AddData_filename+'.rar!' as msg
end
else
begin
select '增量备份版本不正确,请检查!' as msg
end
end
else
begin
select '在'+CONVERT(varchar(100), @backup_date, 20)+'已经备份完,同步还原前不能再备份!备份文件名为:'+@Temp_Path+@AddData_filename+'.rar,' as msg
end
GO