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 '数据库所有的还原操作都已完成！'

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

testrestoreop

@sourcefile

\\169.xxx.xxx.xxx\d$\sql_backfile

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的一些参数，因为我们在代码中有用到他们。

感谢：以上代码由作者本人和同事fly chen共同完成。

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