SQL Server 数据库基于备份文件的【一键还原】

SQL Server 数据库基于备份文件的【一键还原】,第1张

概述1. 备份与还原的基础说明 我们知道在DBA的日常工作中,SQL Server 数据库的恢复请求偶有发生,可能是用作数据的追踪,可也可能能是数据库的灾难恢复。 数据库常用的备份命令如下: 备份文件的命 1. 备份与还原的基础说明

我们知道在DBA的日常工作中,sql Server 数据库的恢复请求偶有发生,可能是用作数据的追踪,可也可能能是数据库的灾难恢复。

数据库常用的备份命令如下:

----完整备份Declare @Fullfilename Varchar(200)@fileFlag varchar(20Set @fileFlag=REPLACE(CONVERT(VARCHAR(10),GETDATE(),120),1)">'-',1)">'')+ 8),1)">:''@Fullfilename文档路径\数据库名字_FulL'++.bak'BackUp DataBase 数据库名字 To disk=with init--差异备份@Difffilename @Difffilename文档路径\数据库名字_Diff_ init,differential --事务日志备份@filename @filename文档路径\数据库名字_Trn_.trnBackUp Log 数据库名字 with init

 

备份文件的命名格式为:数据库名字_备份类型(Full或Diff或Trn的一种)_时间格式.文件类型(bak或trn的一种) 

其中的时间格式为:年月日时分秒 ,例如:20190423140813。

例如,数据库TestRestoreOP的备份文件如下:

 

我们花费篇章来说明备份文件的格式化,主要是因为【一键】还原的基础是文件的标准化。

 

相应的还原命令如下:

--完整备份还原RESTORE DATABASE 数据库名字 FROM disK = 完整备份的文件'-'TTTTTTT.BAK' WITH norECOVERY,MOVE 数据库名字_Data' TO D:\指定路径\数据库名字_Data.mdf,MOVE 数据库名字_LogD:\指定路径\数据库名字_Log.ldf'--差异备份还原差异备份的文件----'SSSSSSSSS.BAK' --log备份还原RESTORE Log 数据库名字 FROM 事务日志备份的文件---'XXXXXXXX.trn'WITH norECOVERY
2.远程备份文件的【一键】还原

实际的生产中,我们常将备份文件copy至远程服务器上,所以还原的时候,还要将这些文件copy到指定服务上再进行还原。还有一种情况,就是Log还原可能需要逐一还原多个日志文件,有时候,甚至十几个文件需要还原。

针对这种这种情况,日常工作中,我们逐渐提炼成了以下sql,替换参数后,基本实现 一键还原。

主要实现的功能有四点:

(1)将远程Server 上的指定备份路径下的文件copy值本地指定路径;(如果文件以copy值本地,这一步可以省略,对应的代码为1和2部分)

(2) 将这些文件属性读到表Backupfile;

(3)根据文件命名的时间属性,还原最近的一个完整备份 和一个差异备份;

(4)还原差异备份后产生的所有日志备份。

3.代码实现
-0 --定义要还原的数据库名字    DECLARE @Cmd varchar (1024)     @dbname sysname      @dbnameTestRestoreOP- 1 --定义远程备份文件所在目录    @sourcefile NVARCHAR(500)      SET @sourcefile \169.XXX.XXX.XXX\d$\sql_Backfile--2 -- 将远程备份的目录,copy至本地 D:\sql_Restorefile 目录下,MAXAGE:3 代表copy最近3天的文件    @Cmd=(master.dbo.xp_cmdshell '''ROBOcopY.exe  "@sourcefile" "D:\sql_Restorefile" *.* /E /XC /XN /X /MAXAGE:3 /MINAGE:0 ''')    print @Cmd    EXEC (@Cmd)      Print 将远程需要还原的文件copy至本地--3 -- 获取还原文件List        @Path 260)        @Path D:\sql_Restorefile'        IF RIGHT(@Path,1); Font-weight: bold">1) <> \'         = Print @Path      --判断表Backupfile是否已经存在,不存在则创建          IF (SELECT COUNT(*) FROM SYS.sysobjects WHERE nameBackupfileand xtypeU')=0            BEGIN                CREATE table Backupfile                (                  ID INT,编号                  directory 260),1)">路径                  depth 深度,相对与@path                  Isfile BIT),IsRestore int,1)">是否还原                )0文件夹1文件名成            END             Truncate table Backupfile      ---判断表TMP_Backupfile是否已经存在,存在则删除再创建          TMP_Backupfile<>DROP  TMP_Backupfile              END          TMP_Backupfile            (              ID INT IDENTITY,1)">编号              directory 路径              depth 深度,相对与@path              Isfile 是否还原            )0文件夹1文件名成       --将@Path 目录下结构读入到表TMP_Backupfile中        INSERT  TMP_Backupfile                ( directory,depth,Isfile                )                EXEC master.dbo.xp_dirtree @path @depth = 0,1)">@file 1        update TMP_Backupfile set filename=directory,IsRestore0       将需要还原的文件信息读入到表TMP_Backupfile中'        ---设置不需要还原的数据库文件,即删除         DELETE FROM dbo.TMP_Backupfile WHERE directory  NOT liKE %'        --设置删除不符合日期规则的文件        WHERE  left(right([filename],1); Font-weight: bold">18),1); Font-weight: bold">14)<20190413015000---更新目录        UPDATE  TMP_Backupfile        SET     directory  directory        WHERE   depth ----        if exists(  select * from TMP_Backupfile  > 1begin        Error:备份文件所在的路径不对,或者@Path包含了不应该存在的文件夹目录!'                end          -----        INSERT INTO Backupfile (directory,Isfile,1)">]SELECT B.directory,B.depth,B.Isfile,B.FROM TMP_Backupfile B         left join Backupfile e on B.]=e.] where e.is null -4 --定义5/6/7 步骤需要的参数    @filename )     @backupPath ) 5 -- 找到需要还原的完整备份文件,进行完整还原   SELECT top 1  @filename FROM Backupfile WHERE IsRestore0 AND directory %FulL%.bakORDER BY 14) desc   @filename       Msg:完整备份文件:' + @filename  开始还原!'   @backupPathdirectory    WHERE filenameAND  IsRestore0   @dbname   @cmd RESTORE DATABASE [@dbname ] FROM disK = '''         @backupPath  WITH  file = 1,MOVE N''' TO N''D:\sql_data\.MDF_LogD:\sql_log\.LDF'    exec (@cmd)   PRINT @cmd   UPDATE Backupfile SET IsRestore1 0   还原完成! 6 --- 找到需要还原的差异备份文件,进行增量还原    @filename%Diff%.bak'  desc    @filename    Msg:获取得知需要还原的差异备份文件:'    update  Backupfile  set IsRestore10      AND     (directory or directory %TRN%.TRN'  ) and <left(right( @filename,1); Font-weight: bold">14Msg:差异备份文件:'    =directory 0        WITH norECOVERY'     exec(@cmd    0      7  --日志备份文件还原    DECLARE filenames CURSOR FOR          asc    OPEN filenames       Loop through all the files for the database      FETCH NEXT FROM filenames INTO @filename      WHILE @@FETCH_STATUS 0      BEGIN         Msg:日志备份文件:'           0       RESTORE LOG ['''             '        )          0        '       @cmd       END     CLOSEDEALLOCATE filenames   8 -- 将数据库的状态由真正还原Restore正常状态!  将数据库的状态由真正还原Restore正常状态!] WITH RECOVERY' @cmd数据库所有的还原 *** 作都已完成!'

 

 

如果在您工作中也有类似需求,需要执行上述代码,则需要替换的参数如下

需要替换的参数数据
参数代表含义
@dbname需还原的数据库名字,本例为;
TestRestoreOP
@sourcefile
备份文件在远程Server路径;本例为
\169.XXX.XXX.XXX\d$\sql_Backfile
没有设置参数备份文件copy至本地的路径;本例为
D:\sql_Restorefile
 没有设置参数
设置删除不符合日期规则的文件,指的是把旧的文件也copy到本地了,这还原表中因删除;本例为
20190413015000
 没有设置参数
设置还原数据库的数据文件所在文档路径:本例为 D:\sql_data\。建议不要修改,执行前请先创建。
 没有设置参数
设置还原数据库的日志文件所在文档路径;本例为 D:\sql_log\
。建议不要修改,执行前请先创建。

 

4.ROBOcopY.exe知识补充

备份文件的远程copy通过ROBOcopY.exe来实现。

 Robocopy.exe 是 微软在windows server 2003 Resource Kit Tools 里面提供的程序来做备份的,Vista,Win2008已经自带了。Microsoft windows 中内置的传统的“复制和粘贴”功能有一些局限性:它在执行简单的任务(将一个文档从一个目录移动到另一个目录等)时处理得还好,但缺乏 IT 专业人员在工作场所所需的高级功能。例如,复制和粘贴 *** 作不包括任何高级复原功能,所以不允许从短暂的网络中断后进行恢复。Robocopy 支持更多重要的文件复制任务,从而能够简化工作。Robocopy 还允许保留所有相关文件信息,包括日期和时间戳、安全访问控制列表 (ACL) 及更多内容。[更多内容请参考网络分享]

现在主要留意下ROBOcopY.exe的一些参数,因为我们在代码中有用到他们。

参数含义
/MAXAGE最长的文件存在时间 - 排除早于 n 天/日期的文件。(n代表指定参数)
/MINAGE最短的文件存在时间 - 排除晚于 n 天/日期的文件。
/S复制子目录,但不复制空的子目录。
/XC排除已更改的文件。
/XN排除较新的文件。
/X报告所有多余的文件,而不只是选中的文件。

 

 

感谢:以上代码由作者本人和同事Fly Chen共同完成。

未经作者同意不得转载,谢谢配合!!!

总结

以上是内存溢出为你收集整理的SQL Server 数据库基于备份文件的【一键还原】全部内容,希望文章能够帮你解决SQL Server 数据库基于备份文件的【一键还原】所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。

欢迎分享,转载请注明来源:内存溢出

原文地址: http://www.outofmemory.cn/sjk/1150527.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2022-05-31
下一篇 2022-05-31

发表评论

登录后才能评论

评论列表(0条)

保存