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

1. 备份与还原的基础说明

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

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

----完整备份
declare @fullfilename varchar(200)
declare @fileflag varchar(20)
set @fileflag=replace(convert(varchar(10), getdate(), 120), '-','')+ replace(convert(varchar(10), getdate(), 8), ':', '')
set @fullfilename='文档路径\数据库名字_full'+@fileflag+'.bak'
backup database 数据库名字 to disk=@fullfilename with init

----差异备份
declare @difffilename varchar(200)
declare @fileflag varchar(200)
set @fileflag=replace(convert(varchar(10), getdate(), 120), '-','')+ replace(convert(varchar(10), getdate(), 8), ':', '')
set @difffilename='文档路径\数据库名字_diff_'+@fileflag+'.bak'
backup database 数据库名字 to disk=@difffilename with init,differential 

----事务日志备份
declare @filename varchar(200)
declare @fileflag varchar(20)
set @fileflag=replace(convert(varchar(10), getdate(), 120), '-','')+ replace(convert(varchar(10), getdate(), 8), ':', '')
set @filename='文档路径\数据库名字_trn_'+@fileflag+'.trn'
backup log 数据库名字 to disk=@filename with init

 

备份文件的命名格式为：数据库名字_备份类型（full或diff或trn的一种）_时间格式.文件类型（bak或trn的一种） 

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

例如，数据库testrestoreop的备份文件如下：

 

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

 

相应的还原命令如下：

----完整备份还原
restore database 数据库名字 from 
disk = '完整备份的文件'---'ttttttt.bak' 
with norecovery, move '数据库名字_data' to 'd:\指定路径\数据库名字_data.mdf',
move '数据库名字_log' to 'd:\指定路径\数据库名字_log.ldf'

----差异备份还原
restore database 数据库名字 from 
disk = '差异备份的文件'------'sssssssss.bak' 
with norecovery, move '数据库名字_data' to 'd:\指定路径\数据库名字_data.mdf',
move '数据库名字_log' to 'd:\指定路径\数据库名字_log.ldf'

----log备份还原
restore log 数据库名字 
from disk ='事务日志备份的文件' -----'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)
     declare @dbname sysname 
     set @dbname='testrestoreop'

--- 1 --定义远程备份文件所在目录
    declare @sourcefile nvarchar(500)  
    set @sourcefile = '\\169.xxx.xxx.xxx\d$\sql_backfile'

----2 -- 将远程备份的目录，copy至本地 d:\sql_restorefile 目录下，maxage:3 代表copy最近3天的文件
    set @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
        declare @path varchar(260)
        set @path = 'd:\sql_restorefile'
        if right(@path, 1) <> '\'
         set @path = @path + '\'
         print @path
      ----判断表backupfile是否已经存在,不存在则创建
          if (select count(*) from sys.sysobjects where name='backupfile'and xtype='u')=0
            begin
                create table backupfile
                (
                  id int ,  --编号
                  directory varchar(260) ,  --路径
                  depth int , --深度，相对与@path
                  isfile bit ,
                  filename varchar(260),
                  isrestore int ,--是否还原
                )--0文件夹1文件名成
            end 

            truncate table backupfile

      -----判断表tmp_backupfile是否已经存在,存在则删除再创建
          if (select count(*) from sys.sysobjects where name='tmp_backupfile'and xtype='u')<>0
            begin
                drop table tmp_backupfile  
            end 
        create table tmp_backupfile
            (
              id int identity ,  --编号
              directory varchar(260) ,  --路径
              depth int , --深度，相对与@path
              isfile bit ,
              filename varchar(260),
              isrestore int ,--是否还原
            )--0文件夹1文件名成


       ----将@path 目录下结构读入到表tmp_backupfile中
        insert  tmp_backupfile
                ( directory ,
                  depth ,
                  isfile
                )
        
        exec master.dbo.xp_dirtree @path = @path, @depth = 0, @file = 1

        update tmp_backupfile set filename=directory,isrestore=0

       print '将需要还原的文件信息读入到表tmp_backupfile中'


        -----设置不需要还原的数据库文件,即删除
         delete from dbo.tmp_backupfile where directory  not like '%'+@dbname+'%'

        ----设置删除不符合日期规则的文件
        delete from dbo.tmp_backupfile where  left(right([filename],18),14)<'20190413015000'

        -----更新目录

        update  tmp_backupfile
        set     directory = @path + directory
        where   depth = 1

        ------
        if exists(  select * from tmp_backupfile  where   depth > 1)
        begin

        print 'error:备份文件所在的路径不对，或者@path包含了不应该存在的文件夹目录！'
        
        end  
        -------

        insert into backupfile (directory, depth, isfile, [filename], isrestore)
        select b.directory, b.depth, b.isfile, b.[filename], b.isrestore from tmp_backupfile b 
        left join backupfile e on b.[filename]=e.[filename] where e.[filename] is null 


---4 --定义5/6/7 步骤需要的参数

    declare @filename nvarchar(500) 
    declare @backuppath nvarchar(500)


-- 5 -- 找到需要还原的完整备份文件，进行完整还原

   select top 1  @filename =[filename] from backupfile where isrestore=0 and directory like '%full%.bak' order by left(right([filename],18),14) desc
   print @filename 
   
   print 'msg:完整备份文件：' + @filename  + '开始还原！'

   select @backuppath=directory 
   from backupfile where filename=@filename and  isrestore=0
   print @dbname
   set @cmd = 'restore database [' + @dbname + '] from disk = '''  
       + @backuppath + ''' with  file = 1,  move n'''+@dbname+''' to n''d:\sql_data\'+@dbname+'.mdf'', move n'''+@dbname+'_log'' to n''d:\sql_log\'+@dbname+'.ldf'',norecovery,    nounload,  stats = 5' 
   exec (@cmd)
   print @cmd

   update backupfile set isrestore=1 where filename=@filename and  isrestore=0

   print 'msg:完整备份文件：' + @filename  + '还原完成！'

-- 6 --- 找到需要还原的差异备份文件，进行增量还原

    select top 1 @filename=[filename] from backupfile where isrestore=0 and directory like '%diff%.bak'  order by left(right([filename],18),14) desc
    print @filename

    print 'msg:获取得知需要还原的差异备份文件：' + @filename  + ',此时将不需要还原的差异文件/日志文件设置为不需要还原'
    update  backupfile  set isrestore=10  
    where isrestore=0 and 
    (directory like '%diff%.bak' or directory like '%trn%.trn'  ) and left(right([filename],18),14)<left(right( @filename,18),14)

      print 'msg:差异备份文件：' + @filename  + '开始还原！'

    select @backuppath=directory from backupfile where filename=@filename and  isrestore=0
   
    set @cmd = 'restore database [' + @dbname + '] from disk = '''  
       + @backuppath + ''' with norecovery' 
    exec(@cmd)
    print @cmd

    update backupfile set isrestore=1 where filename=@filename and  isrestore=0

     print 'msg:差异备份文件：' + @filename  + '还原完成！'

-- 7  --日志备份文件还原
    declare filenames cursor for  
        select [filename] from backupfile where isrestore=0 and directory like '%trn%.trn'  order by left(right([filename],18),14) asc
    open filenames  
    -- loop through all the files for the database  
    fetch next from filenames into @filename  
    while @@fetch_status = 0  
    begin  

       print 'msg:日志备份文件：' + @filename  + '开始还原！'
    
       select @backuppath=directory from backupfile where filename=@filename and  isrestore=0
       set @cmd = 'restore log [' + @dbname + '] from disk = '''  
           +@backuppath+ ''' with norecovery' 
       exec(@cmd)
   
       update backupfile set isrestore=1 where filename=@filename and  isrestore=0

        print 'msg:日志备份文件：' + @filename  + '还原完成！'

       print @cmd
       fetch next from filenames into @filename  
    end 
    close filenames  
    deallocate filenames  

-- 8 -- 将数据库的状态由真正还原restore正常状态！ 

 print '将数据库的状态由真正还原restore正常状态！'
set @cmd = 'restore database [' + @dbname + '] with recovery' 
print @cmd

print '数据库所有的还原操作都已完成！'---0 --定义要还原的数据库名字
    declare @cmd varchar (1024)
     declare @dbname sysname 
     set @dbname='testrestoreop'

--- 1 --定义远程备份文件所在目录
    declare @sourcefile nvarchar(500)  
    set @sourcefile = '\\169.xxx.xxx.xxx\d$\sql_backfile'

----2 -- 将远程备份的目录，copy至本地 d:\sql_restorefile 目录下，maxage:3 代表copy最近3天的文件
    set @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
        declare @path varchar(260)
        set @path = 'd:\sql_restorefile'
        if right(@path, 1) <> '\'
         set @path = @path + '\'
         print @path
      ----判断表backupfile是否已经存在,不存在则创建
          if (select count(*) from sys.sysobjects where name='backupfile'and xtype='u')=0
            begin
                create table backupfile
                (
                  id int ,  --编号
                  directory varchar(260) ,  --路径
                  depth int , --深度，相对与@path
                  isfile bit ,
                  filename varchar(260),
                  isrestore int ,--是否还原
                )--0文件夹1文件名成
            end 

            truncate table backupfile

      -----判断表tmp_backupfile是否已经存在,存在则删除再创建
          if (select count(*) from sys.sysobjects where name='tmp_backupfile'and xtype='u')<>0
            begin
                drop table tmp_backupfile  
            end 
        create table tmp_backupfile
            (
              id int identity ,  --编号
              directory varchar(260) ,  --路径
              depth int , --深度，相对与@path
              isfile bit ,
              filename varchar(260),
              isrestore int ,--是否还原
            )--0文件夹1文件名成


       ----将@path 目录下结构读入到表tmp_backupfile中
        insert  tmp_backupfile
                ( directory ,
                  depth ,
                  isfile
                )
        
        exec master.dbo.xp_dirtree @path = @path, @depth = 0, @file = 1

        update tmp_backupfile set filename=directory,isrestore=0

       print '将需要还原的文件信息读入到表tmp_backupfile中'


        -----设置不需要还原的数据库文件,即删除
         delete from dbo.tmp_backupfile where directory  not like '%'+@dbname+'%'

        ----设置删除不符合日期规则的文件
        delete from dbo.tmp_backupfile where  left(right([filename],18),14)<'20190413015000'

        -----更新目录

        update  tmp_backupfile
        set     directory = @path + directory
        where   depth = 1

        ------
        if exists(  select * from tmp_backupfile  where   depth > 1)
        begin

        print 'error:备份文件所在的路径不对，或者@path包含了不应该存在的文件夹目录！'
        
        end  
        -------

        insert into backupfile (directory, depth, isfile, [filename], isrestore)
        select b.directory, b.depth, b.isfile, b.[filename], b.isrestore from tmp_backupfile b 
        left join backupfile e on b.[filename]=e.[filename] where e.[filename] is null 


---4 --定义5/6/7 步骤需要的参数

    declare @filename nvarchar(500) 
    declare @backuppath nvarchar(500)


-- 5 -- 找到需要还原的完整备份文件，进行完整还原

   select top 1  @filename =[filename] from backupfile where isrestore=0 and directory like '%full%.bak' order by left(right([filename],18),14) desc
   print @filename 
   
   print 'msg:完整备份文件：' + @filename  + '开始还原！'

   select @backuppath=directory 
   from backupfile where filename=@filename and  isrestore=0
   print @dbname
   set @cmd = 'restore database [' + @dbname + '] from disk = '''  
       + @backuppath + ''' with  file = 1,  move n'''+@dbname+''' to n''d:\sql_data\'+@dbname+'.mdf'', move n'''+@dbname+'_log'' to n''d:\sql_log\'+@dbname+'.ldf'',norecovery,    nounload,  stats = 5' 
   exec (@cmd)
   print @cmd

   update backupfile set isrestore=1 where filename=@filename and  isrestore=0

   print 'msg:完整备份文件：' + @filename  + '还原完成！'

-- 6 --- 找到需要还原的差异备份文件，进行增量还原

    select top 1 @filename=[filename] from backupfile where isrestore=0 and directory like '%diff%.bak'  order by left(right([filename],18),14) desc
    print @filename

    print 'msg:获取得知需要还原的差异备份文件：' + @filename  + ',此时将不需要还原的差异文件/日志文件设置为不需要还原'
    update  backupfile  set isrestore=10  
    where isrestore=0 and 
    (directory like '%diff%.bak' or directory like '%trn%.trn'  ) and left(right([filename],18),14)<left(right( @filename,18),14)

      print 'msg:差异备份文件：' + @filename  + '开始还原！'

    select @backuppath=directory from backupfile where filename=@filename and  isrestore=0
   
    set @cmd = 'restore database [' + @dbname + '] from disk = '''  
       + @backuppath + ''' with norecovery' 
    exec(@cmd)
    print @cmd

    update backupfile set isrestore=1 where filename=@filename and  isrestore=0

     print 'msg:差异备份文件：' + @filename  + '还原完成！'

-- 7  --日志备份文件还原
    declare filenames cursor for  
        select [filename] from backupfile where isrestore=0 and directory like '%trn%.trn'  order by left(right([filename],18),14) asc
    open filenames  
    -- loop through all the files for the database  
    fetch next from filenames into @filename  
    while @@fetch_status = 0  
    begin  

       print 'msg:日志备份文件：' + @filename  + '开始还原！'
    
       select @backuppath=directory from backupfile where filename=@filename and  isrestore=0
       set @cmd = 'restore log [' + @dbname + '] from disk = '''  
           +@backuppath+ ''' with norecovery' 
       exec(@cmd)
   
       update backupfile set isrestore=1 where filename=@filename and  isrestore=0

        print 'msg:日志备份文件：' + @filename  + '还原完成！'

       print @cmd
       fetch next from filenames into @filename  
    end 
    close filenames  
    deallocate filenames  

-- 8 -- 将数据库的状态由真正还原restore正常状态！ 

 print '将数据库的状态由真正还原restore正常状态！'
set @cmd = 'restore database [' + @dbname + '] with recovery' 
exec(@cmd)
print @cmd

print '数据库所有的还原操作都已完成！'

 

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

需要替换的参数数据
参数	代表含义
@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共同完成。

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