SQL Server 数据库基于备份文件的【一键还原】
程序员文章站
2022-03-24 17:56:43
1. 备份与还原的基础说明 我们知道在DBA的日常工作中，SQL Server 数据库的恢复请求偶有发生，可能是用作数据的追踪，可也可能能是数据库的灾难恢复。 数据库常用的备份命令如下： 备份文件的命名格式为：数据库名字_备份类型（Full或Diff或Trn的一种）_时间格式.文件类型（bak或tr ......
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共同完成。
未经作者同意不得转载,谢谢配合！！！
上一篇： MS SQL 锁与事务
下一篇： 好消息！首届社群团购供应链大会将开幕啦
推荐阅读
-
SQL Server 2005数据库还原错误的经典解决方案
-
基于Python的SQL Server数据库实现对象同步轻量级
-
SQL SERVER 2005数据库还原的方法
-
JavaScript基于activexobject连接远程数据库SQL Server 2014的方法
-
SQL Server2012数据库备份和还原的教程
-
在程序中压缩sql server2000的数据库备份文件的代码
-
SQL Server 2008 备份数据库、还原数据库的方法
-
sql Server 2008 R2还原或删除数据库时总是出错的解决方法
-
sql server 2000数据库备份还原的图文教程
-
SQL Server 2005数据库还原错误的经典解决方案