【SQL】存储过程--数据库自动还原②

系列文章

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_RestoreTotal] (@AddData_filename varchar(100))
AS

declare @LogGuid varchar(40),@BackUpDateTime datetime,@ToBakData_date datetime,@ToBaseData_date datetime
declare @ExceptCheck_date datetime,@ExceptCheck_count int,@SumNumCheck_date datetime,@SumNumCheck_count int
declare @RestoreAddData_date datetime,@BackUpGuid varchar(40),@RestoreGuid 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,@restore_date datetime,@BackUpLocation varchar(40),@RestoreLocation varchar(40)
declare @Add_BackUpLocation varchar(40),@Add_RestoreLocation varchar(40),@Add_RestoreGuid 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 @restore_date=(select restore_date from wsbakconfig..BakSysPar)
set @BackUpGuid=(select backup_guid from wsbakconfig..BakSysPar)
set @RestoreGuid=(select restore_guid from wsbakconfig..BakSysPar)
set @BackUpLocation=(select database_location from wsbakconfig..BakSysPar)
set @RestoreLocation=(select restore_location from wsbakconfig..BakSysPar)

if @IsReadOnly=1 
begin
  
    set @LogGuid=NEWID()
    set @BackUpDateTime=GETDATE()

    ---1.1解压、还原数据库
    declare @RestoreResult int
    exec PBak_RESTOREAddData @DataBaseAdd=@dataadd_name,@TempPath=@Temp_Path,@AddFileName=@AddData_filename,
                             @RarPath=@rar_Path,@Result=@RestoreResult output
    set @RestoreAddData_date=GETDATE()
    
    if  @RestoreResult=1 
    begin  
      
      if OBJECT_ID ('tempdb..#tbBakRestoreGuid') is not null
      begin
        drop table #tbBakRestoreGuid
      end
      create table #tbBakRestoreGuid
        (backup_location varchar(40) null,
         restore_location varchar(40) null,
         restore_guid varchar(40) null,
        )
      set @sql='insert into #tbBakRestoreGuid (backup_location,restore_location,restore_guid)'+char(13) 
              +' select backup_location,restore_location,restore_guid from '+@dataadd_name+'..BakRestoreGuid '
      exec (@sql)
      set @Add_BackUpLocation=(select backup_location from #tbBakRestoreGuid)
      set @Add_RestoreLocation=(select restore_location from #tbBakRestoreGuid)
      set @Add_RestoreGuid=(select restore_guid from #tbBakRestoreGuid)
      if @BackUpLocation=@Add_RestoreLocation and @RestoreLocation=@Add_BackUpLocation and @RestoreGuid<>@Add_RestoreGuid
      begin
        ---1.2还原Bak增量数据
        exec PBak_ToBakData @DataBaseAdd=@dataadd_name,@DataBaseBak=@databak_name
        set @ToBakData_date=GETDATE()
                                 
        ---1.3设置数据库可写
        exec [PBak_SetReadWrite] @DataBase=@database_name        

        ---1.4还原Base增量数据
        exec PBak_ToBakData @DataBaseAdd=@dataadd_name,@DataBaseBak=@database_name
        set @ToBaseData_date=GETDATE()

        ---1.5减法归零Except比对检查
        exec PBak_ExceptCheck @DataBase=@database_name,@DataBaseBak=@databak_name,@CheckCount=@ExceptCheck_count output
        set @ExceptCheck_date=GETDATE()
        
        ---1.6合计数量比对检查
        exec PBak_SumNumCheck @DataBase=@database_name,@DataBaseBak=@databak_name,@CheckCount=@SumNumCheck_count output
        set @SumNumCheck_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)+'Restore'+char(39)+' as [log_type],'+CHAR(13)
                +char(39)+ CONVERT(varchar(100), @BackUpDateTime, 20)+char(39)+' as [log_date],'+CHAR(13)
                +char(39)+char(39)+' as [ToAddData_date],'+CHAR(13)
                +char(39)+ CONVERT(varchar(100), @ToBakData_date, 20)+char(39)+' as [ToBakData_date],'+CHAR(13)
                +char(39)+ CONVERT(varchar(100), @ToBaseData_date, 20)+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)+CHAR(39)+' as [BackUpAddData_date],'+CHAR(13)
                +char(39)+@AddData_filename+char(39)+' as [AddData_filename],'+CHAR(13)
                +CHAR(39)+ CONVERT(varchar(100), @RestoreAddData_date, 20)+CHAR(39)+' as [RESTOREAddData_date]'
        exec (@Sql)
        set @Sql='update main set restore_guid='+CHAR(39)+@LogGuid+CHAR(39)
                +',add_file_name='+CHAR(39)+@AddData_filename+CHAR(39)
                +',restore_date='+char(39)+ CONVERT(varchar(100), @BackUpDateTime, 20)+char(39)
                +',is_readonly=0 from wsbakconfig..BakSysPar main '
        exec (@Sql)

        set @Sql='update main set restore_guid='+CHAR(39)+@LogGuid+CHAR(39)+','
                +'restore_date='+char(39)+ CONVERT(varchar(100), @BackUpDateTime, 20)+char(39)
                +' from '+@dataadd_name+'..BakRestoreGuid main'
        exec (@Sql)
        
        select '备份数据还原完成！' as msg 
      end
      else
      begin
        select '增量备份文件版本不正确，请检查！' as msg
      end
    end
end
else
begin
  if @BackUpGuid<>''
  begin
    select '在'+CONVERT(varchar(100), @restore_date, 20)+'已经还原完，不能再次还原！' as msg
  end
  else
  begin
    select '刚完成初始化设置，没有可还原的备份记录！' as msg
  end
  
end


GO