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' 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共同完成。
未经作者同意不得转载,谢谢配合！！！
