SQLServer 生成还原数据库脚本的存储过程！

手动还原备份时，比较麻烦，尤其日志文件多的时候！由于国庆时不在，同事需要查询历史数据，需要还原数据库时不敢还原，才想到写这个脚本。脚本中会误差5分钟，如果有日志备份时间小于5分钟的可以改下脚本。该脚本适合有完整+差异+日志的备份。

存储过程提供个参数:

还原的数据库:@DatabaseNVARCHAR(128)

还原后的别名:@RecoveryDBNameNVARCHAR(128)

恢复的时间点:@RecoveryTimeDATETIME

恢复到的路径:@RecoveryDBPathNVARCHAR(1000)

USE masterGO/*导出还原脚本 KK 2015-10-15DECLARE  @Database NVARCHAR(128),@RecoveryDBName NVARCHAR(128),@RecoveryTime DATETIME,@RecoveryDBPath NVARCHAR(1000)SET @Database = N'Demodb'SET @RecoveryDBName = N'Demodb20151012'SET @RecoveryTime = N'2015-09-29 10:35:00'SET @RecoveryDBPath = N'G:\DataBase\Demodb20151012\'EXEC DBO.OPT_GetDBRecoveryScript @Database,@RecoveryDBName,@RecoveryTime,@RecoveryDBPath*/ALTER PROCEDURE DBO.OPT_GetDBRecoveryScript(@Database NVARCHAR(128)    , @RecoveryDBName NVARCHAR(128)    , @RecoveryTime DATETIME    , @RecoveryDBPath NVARCHAR(1000))ASBEGINSET NOCOUNT ONSET @Database = LTRIM(RTRIM(ISNULL(@Database,'')))SET @RecoveryDBName = LTRIM(RTRIM(ISNULL(@RecoveryDBName,'')))SET @RecoveryDBPath = LTRIM(RTRIM(ISNULL(@RecoveryDBPath,'')))IF (@Database='' OR @RecoveryDBName='' OR @RecoveryDBPath='')BEGIN    PRINT N'输入不能为空！'    RETURN -1ENDIF NOT EXISTS(SELECT 1 FROM msdb.dbo.backupset     WHERE database_name = @Database AND backup_finish_date >= @RecoveryTime)BEGIN    PRINT N'该数据库当前时间无备份！'    RETURN -1ENDIF EXISTS(SELECT * FROM Master.sys.sysdatabases WHERE name = @RecoveryDBName)BEGIN    PRINT N'还原的数据库已存在，不可覆盖！'    RETURN -1ENDIF (RIGHT(@RecoveryDBPath,1)<>'\')    SET @RecoveryDBPath = @RecoveryDBPath + '\'/*DROP TABLE #FileExist*/CREATE TABLE #FileExist(ExistFile BIT,ExistPath BIT,ExistRoot BIT)INSERT INTO #FileExist EXEC master..xp_fileexist @RecoveryDBPathIF NOT EXISTS(SELECT * FROM #FileExist WHERE ExistRoot=1 AND ExistPath=1)BEGIN    PRINT N'还原的路径不存在！'    RETURN -1END/*利用最近的2次备份计算备份的间隔*/DECLARE @FullBackupInterval INTDECLARE @DiffBackupInterval INTDECLARE @LogBackupInterval  INT/*DROP TABLE #BackupDate*/CREATE TABLE #BackupDate(type VARCHAR(1),FinishDate DATETIME,ID tinyint)INSERT INTO #BackupDate(type,FinishDate,ID)SELECT TOP(2) type,backup_finish_date,ROW_NUMBER()OVER(ORDER BY backup_finish_date DESC) IDFROM msdb.dbo.backupset(NOLOCK)WHERE database_name = @Database and type = 'D' ORDER BY backup_finish_date DESCINSERT INTO #BackupDate(type,FinishDate,ID)SELECT TOP(2) type,backup_finish_date,ROW_NUMBER()OVER(ORDER BY backup_finish_date DESC) IDFROM msdb.dbo.backupset(NOLOCK)WHERE database_name = @Database and type = 'I' ORDER BY backup_finish_date DESCINSERT INTO #BackupDate(type,FinishDate,ID)SELECT TOP(2) type,backup_finish_date,ROW_NUMBER()OVER(ORDER BY backup_finish_date DESC) IDFROM msdb.dbo.backupset(NOLOCK)WHERE database_name = @Database and type = 'L' ORDER BY backup_finish_date DESC/*用于备份的间隔计算,增加5分钟减少误差*/SELECT  @FullBackupInterval=DateDiff(SS,D2_FinishDate,D1_FinishDate)+300,@DiffBackupInterval=DateDiff(SS,I2_FinishDate,I1_FinishDate)+300,@LogBackupInterval=DateDiff(SS,L2_FinishDate,L1_FinishDate)+300FROM(SELECT MAX(CASE WHEN type='D' AND ID=1 THEN FinishDate ELSE '1900-01-01' END) D1_FinishDate,MAX(CASE WHEN type='D' AND ID=2 THEN FinishDate ELSE '1900-01-01' END) D2_FinishDate,MAX(CASE WHEN type='I' AND ID=1 THEN FinishDate ELSE '1900-01-01' END) I1_FinishDate,MAX(CASE WHEN type='I' AND ID=2 THEN FinishDate ELSE '1900-01-01' END) I2_FinishDate,MAX(CASE WHEN type='L' AND ID=1 THEN FinishDate ELSE '1900-01-01' END) L1_FinishDate,MAX(CASE WHEN type='L' AND ID=2 THEN FinishDate ELSE '1900-01-01' END) L2_FinishDateFROM #BackupDate) AS TAB/*考虑几种情况: 完整 , 完整+日志 , 完整+差异 , 完整+差异+日志 */DECLARE @SCRIPT NVARCHAR(4000)DECLARE @FullBackupDatetime DatetimeDECLARE @DiffBackupDatetime DatetimeDECLARE @LogBackupDatetime Datetime/*取最接近还原时间的完整备份时间*/SELECT @FullBackupDatetime=MAX(backup_finish_date) FROM msdb.dbo.backupset (NOLOCK)WHERE database_name = @Database AND type = 'D'AND backup_finish_date <= @RecoveryTimeAND backup_finish_date > DATEADD(SS,-@FullBackupInterval,@RecoveryTime)SELECT @SCRIPT=physical_device_name FROM msdb.dbo.backupmediafamily (NOLOCK)WHERE media_set_id IN(SELECT media_set_id FROM msdb.dbo.backupset (NOLOCK)WHERE database_name = @Database AND type = 'D' AND backup_finish_date = @FullBackupDatetime)SET @SCRIPT =   N'RESTORE DATABASE ['+@RecoveryDBName+N'] '+CHAR(10)+ N'FROM  DISK = N'''+@SCRIPT+''' '+CHAR(10)+ N'WITH  FILE = 1, '+CHAR(10)SELECT @SCRIPT = @SCRIPT + N'MOVE N'''+name+N''' TO N'''+@RecoveryDBPath+REVERSE(LEFT(REVERSE(physical_name),CHARINDEX('\',REVERSE(physical_name))-1))+''','+CHAR(10)FROM sys.master_files WHERE database_id = DB_ID(@Database); /*还原时间是否刚好为完整备份时间,并输出脚本*/IF EXISTS(SELECT 1 FROM msdb.dbo.backupset(NOLOCK)     WHERE database_name = @Database AND type = 'D' AND backup_finish_date = @RecoveryTime)BEGINSET @SCRIPT = @SCRIPT + N'RECOVERY,  NOUNLOAD,  STATS = 10 '+CHAR(10)+N'GO'+CHAR(10)PRINT '/*完整备份还原*/'PRINT @SCRIPTRETURN 0ENDELSEBEGINSET @SCRIPT = @SCRIPT + N'NORECOVERY,  NOUNLOAD,  STATS = 10 '+CHAR(10)+N'GO'+CHAR(10)PRINT '/*完整备份还原*/'PRINT @SCRIPTENDSET @SCRIPT = ''/*取最接近还原时间的差异备份时间*/SELECT @DiffBackupDatetime=MAX(backup_finish_date) FROM msdb.dbo.backupset (NOLOCK)WHERE database_name = @Database AND type = 'I'AND backup_finish_date <= @RecoveryTimeAND backup_finish_date > DATEADD(SS,-@DiffBackupInterval,@RecoveryTime)AND backup_finish_date > @FullBackupDatetime/*并大于完整备份时间*/SELECT @SCRIPT=physical_device_name FROM msdb.dbo.backupmediafamily (NOLOCK)WHERE media_set_id IN(SELECT media_set_id FROM msdb.dbo.backupset (NOLOCK)WHERE database_name = @Database AND type = 'I' AND backup_finish_date > @FullBackupDatetimeAND backup_finish_date = @DiffBackupDatetime )IF (ISNULL(@SCRIPT,'')<>'')BEGINPRINT '/*差异备份还原*/'/*还原时间是否刚好为差异备份时间,并输出脚本*/IF EXISTS(SELECT 1 FROM msdb.dbo.backupset(NOLOCK)    WHERE database_name = @Database AND type = 'I' AND backup_finish_date = @RecoveryTime)BEGIN/*完整+差异*/SET @SCRIPT = N'RESTORE DATABASE ['+@RecoveryDBName+N'] '+CHAR(10)+N'FROM  DISK = N''' +@SCRIPT +N''''+CHAR(10)+N'WITH  FILE = 1,  RECOVERY,  NOUNLOAD,  STATS = 10 '+CHAR(10)+N'GO'+CHAR(10)PRINT @SCRIPTRETURN 0ENDELSEBEGIN/*完整+差异+日志*/SET @SCRIPT = N'RESTORE DATABASE ['+@RecoveryDBName+N'] '+CHAR(10)+N'FROM  DISK = N''' +@SCRIPT +N''''+CHAR(10)+N'WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10 '+CHAR(10)+N'GO'+CHAR(10)PRINT @SCRIPTENDENDELSE /*完整+日志*/BEGINSET @DiffBackupDatetime = @FullBackupDatetimeENDSET @SCRIPT = ''/*取最接近还原时间的日志备份时间*/SELECT @SCRIPT = @SCRIPT+N'RESTORE LOG ['+@RecoveryDBName+N'] '+CHAR(10)+N'FROM  DISK = N'''+physical_device_name+N''''+CHAR(10)+N'WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10'+CHAR(10)+N'GO'+CHAR(10) FROM msdb.dbo.backupmediafamily (NOLOCK)WHERE media_set_id IN(SELECT media_set_id FROM msdb.dbo.backupset (NOLOCK)WHERE database_name = @Database AND type = 'L' AND backup_finish_date > @DiffBackupDatetimeAND backup_finish_date < @RecoveryTime )PRINT '/*日志备份还原*/'PRINT @SCRIPTSET @SCRIPT = ''/*还原时间刚好为日志备份时间*/IF EXISTS(SELECT 1 FROM msdb.dbo.backupset (NOLOCK)     WHERE database_name = @Database AND type = 'L' AND backup_finish_date = @RecoveryTime)BEGINSELECT @SCRIPT = +N'RESTORE LOG ['+@RecoveryDBName+N'] '+CHAR(10)+N'FROM  DISK = N'''+physical_device_name+N''''+CHAR(10)+N'WITH  FILE = 1,  RECOVERY,  NOUNLOAD,  STATS = 10'+CHAR(10)+N'GO'+CHAR(10) FROM msdb.dbo.backupmediafamily (NOLOCK)WHERE media_set_id IN(SELECT media_set_id FROM msdb.dbo.backupset (NOLOCK)WHERE database_name = @Database AND type = 'L' AND backup_finish_date > @DiffBackupDatetimeAND backup_finish_date = @RecoveryTime )PRINT @SCRIPTENDELSE/*否则为时间点恢复*/BEGIN/*取下一个日志的备份时间*/SELECT @LogBackupDatetime=MIN(backup_finish_date) FROM msdb.dbo.backupset (NOLOCK)WHERE database_name = @Database AND type = 'L'AND backup_finish_date < DATEADD(SS,@LogBackupInterval,@RecoveryTime)AND backup_finish_date > @RecoveryTimeAND backup_finish_date > @DiffBackupDatetime/*并大于差异备份时间*/SELECT @SCRIPT = +N'RESTORE LOG ['+@RecoveryDBName+N'] '+CHAR(10)+N'FROM  DISK = N'''+physical_device_name+N''''+CHAR(10)+N'WITH  FILE = 1, RECOVERY, NOUNLOAD, STATS = 10 , STOPAT = N'''+CONVERT(varchar(30),@RecoveryTime,126)+''''+CHAR(10)+N'GO'+CHAR(10) FROM msdb.dbo.backupmediafamily (NOLOCK)WHERE media_set_id IN(SELECT media_set_id FROM msdb.dbo.backupset (NOLOCK)WHERE database_name = @Database AND type = 'L' AND backup_finish_date = @LogBackupDatetime )PRINT @SCRIPTENDSET NOCOUNT OFFRETURN 0ENDGO

示例：

DECLARE  @Database NVARCHAR(128),@RecoveryDBName NVARCHAR(128),@RecoveryTime DATETIME,@RecoveryDBPath NVARCHAR(1000)SET @Database = N'SourseDB'SET @RecoveryDBName = N'RecoDB'SET @RecoveryTime = N'2015-10-2 5:00:00'SET @RecoveryDBPath = N'G:\数据库备份\'EXEC DBO.OPT_GetDBRecoveryScript @Database,@RecoveryDBName,@RecoveryTime,@RecoveryDBPath

/*完整备份还原*/RESTORE DATABASE [RecoDB] FROM  DISK = N'F:\数据库自动备份\完全备份\SourseDB\SourseDB_backup_2015_09_27_003001_9737603.bak' WITH  FILE = 1, MOVE N'SourseDB' TO N'G:\数据库备份\SourseDB.mdf',MOVE N'SourseDB_log' TO N'G:\数据库备份\SourseDB_log.ldf',MOVE N'file_1' TO N'G:\数据库备份\file_1.ndf',MOVE N'file_2' TO N'G:\数据库备份\file_2.ndf',MOVE N'file_3' TO N'G:\数据库备份\file_3.ndf',MOVE N'file_4' TO N'G:\数据库备份\file_4.ndf',MOVE N'file_5' TO N'G:\数据库备份\file_5.ndf',MOVE N'file_6' TO N'G:\数据库备份\file_6.ndf',MOVE N'file_7' TO N'G:\数据库备份\file_7.ndf',MOVE N'file_8' TO N'G:\数据库备份\file_8.ndf',MOVE N'file_9' TO N'G:\数据库备份\file_9.ndf',MOVE N'file_10' TO N'G:\数据库备份\file_10.ndf',MOVE N'file_11' TO N'G:\数据库备份\file_11.ndf',MOVE N'file_12' TO N'G:\数据库备份\file_12.ndf',NORECOVERY,  NOUNLOAD,  STATS = 10 GO/*差异备份还原*/RESTORE DATABASE [RecoDB] FROM  DISK = N'F:\数据库自动备份\差异备份\SourseDB\SourseDB_backup_2015_10_02_000002_1624226.bak'WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10 GO/*日志备份还原*/RESTORE LOG [RecoDB] FROM  DISK = N'F:\数据库自动备份\日志备份\SourseDB\SourseDB_backup_2015_10_02_010001_9672276.trn'WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10GORESTORE LOG [RecoDB] FROM  DISK = N'F:\数据库自动备份\日志备份\SourseDB\SourseDB_backup_2015_10_02_020002_2641026.trn'WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10GORESTORE LOG [RecoDB] FROM  DISK = N'F:\数据库自动备份\日志备份\SourseDB\SourseDB_backup_2015_10_02_030001_7328526.trn'WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10GORESTORE LOG [RecoDB] FROM  DISK = N'F:\数据库自动备份\日志备份\SourseDB\SourseDB_backup_2015_10_02_040001_9203526.trn'WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10GORESTORE LOG [RecoDB] FROM  DISK = N'F:\数据库自动备份\日志备份\SourseDB\SourseDB_backup_2015_10_02_050001_6078526.trn'WITH  FILE = 1, RECOVERY, NOUNLOAD, STATS = 10 , STOPAT = N'2015-10-02T05:00:00'GO

Hello.KK （SQL Server）:  http://blog.csdn.net/kk185800961/article/details/49154637