SET NOCOUNT ON DECLARE @db_name sysname, /*需要恢复的数据库名称*/@restore_db_name sysname, /*还原后的数据库名称*/@recover_to DATETIME, /*要还原的时间点*/@trn_file NVARCHAR(2000),@pre_full_set_id BIGINT,@diff_backup_set_id bigint,@backup_file_name nvarchar(512),@restore_files nvarchar(4000),@sql NVARCHAR(MAX)/*需要指定的3个参数*/SET @db_name = 'testdb'SET @restore_db_name= 'testdb_1119'SET @recover_to = '-11-19 2:33:00'DECLARE @T TABLE (backup_set_id BIGINT PRIMARY KEY,[type] VARCHAR(10), physical_device_name NVARCHAR(2000),is_last int)DECLARE @F TABLE (file_number int,backup_set_id BIGINT ,logical_name varchar(256),physical_name nvarchar(256),file_type varchar(10))IF @recover_to > getdate() BEGIN SELECT 2 as flg,'请输入当前时间点之前的日期' as msg ;RETURNEND IF DB_ID('@restore_db_name') IS NOT NULLBEGIN SELECT 3 as flg,'要恢复的数据库名称已存在' as msg ;RETURNEND -- 是否存在完整备份记录 SELECT TOP 1 @pre_full_set_id=backup_set_id FROM msdb.dbo.backupsetWHERE [database_name] = @db_nameAND recovery_model = 'FULL'AND [type] = 'D'AND backup_start_date < @recover_to ORDER BY backup_set_id DESC IF @pre_full_set_id IS NULL BEGINSELECT 1 AS flg,'不存在有效的完整备份' AS msg;RETURNEND-- 是否需要新做事务日志备份IF NOT EXISTS(SELECT 1FROM msdb.dbo.backupsetWHERE [database_name] = @db_nameAND [type] = 'L' AND backup_start_date >= @recover_to)BEGIN-- 如果找到上次的备份文件，参照生成备份脚本，没有不生成。SELECT TOP 1@trn_file = bf.physical_device_nameFROM msdb.dbo.backupset bINNER JOIN msdb.dbo.backupmediafamily bfON b.media_set_id = bf.media_set_idWHERE [database_name] = @db_nameAND [type] = 'L'AND backup_start_date < @recover_toORDER BY backup_set_id DESCIF @trn_file IS NOT NULL BEGIN SELECT @trn_file=REVERSE( STUFF(REVERSE(@trn_file),1,CHARINDEX('',REVERSE(@trn_file),1)-1,''))SET @trn_file= @trn_file + @db_name + '_log_' + CONVERT(VARCHAR(8), GETDATE(), 112) + '_'+ REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', '') + '_' + LEFT(CEILING(RAND() * 100000), 4) + '.trn'SET @sql=N'BACKUP LOG '+QUOTENAME(@db_name)+'TO DISK = N'''+@trn_file+'''WITH NOFORMAT,NOINIT,NAME = N'''+@db_name+'-完整 数据库 备份'',SKIP,NOREWIND,NOUNLOAD,STATS = 10'PRINT @sqlEND SELECT 4 AS flg,'恢复的时间点，需要操作一次事务日志备份' AS msg;RETURNEND/*支持事务日志、差异日志备份文件*/-- 条件都满足之后，依次生成，完整还原、事务还原、时间点还原的脚本INSERT INTO @T (backup_set_id,[type],physical_device_name,is_last)SELECT backup_set_id,[type], bf.physical_device_name,0 as is_lastFROM msdb.dbo.backupset bINNER JOIN msdb.dbo.backupmediafamily bfON b.media_set_id = bf.media_set_idWHERE [database_name] = @db_nameAND [type] IN ('D','I','L')AND b.backup_set_id>=@pre_full_set_idAND backup_start_date < @recover_to UNION ALL SELECT TOP 1 backup_set_id,[type], bf.physical_device_name,1 as is_last FROM msdb.dbo.backupset bINNER JOIN msdb.dbo.backupmediafamily bfON b.media_set_id = bf.media_set_idWHERE [database_name] = @db_nameAND [type] = 'L'AND backup_start_date >= @recover_toORDER BY backup_set_id -- 获取备份时原始数据库文件列表INSERT INTO @F(file_number,backup_set_id,logical_name,physical_name,file_type)SELECT file_number,@pre_full_set_id,logical_name,physical_name,file_type FROM msdb.dbo.backupfile where backup_set_id=@pre_full_set_id SELECT @backup_file_name = physical_device_nameFROM @T LWHERE backup_set_id = @pre_full_set_idSET @SQL='RESTORE DATABASE '+QUOTENAME(@restore_db_name)+N'FROM DISK = N'''+@backup_file_name+'''WITH FILE = 1,'SET @restore_files=(SELECT 'MOVE N'''+logical_name+''''+CHAR(10)+' TO N'''+REVERSE( STUFF(REVERSE(physical_name),1,CHARINDEX('',REVERSE(physical_name),1)-1,''))+@restore_db_name+'_'+LTRIM(file_number)+'.'+CASE WHEN file_type='L' THEN 'log'WHEN file_type='D' THEN 'mdf'ELSE '' END +''','+CHAR(10)FROM @F WHERE backup_set_id=@pre_full_set_id ORDER BY backup_set_idFOR XML PATH('') )SELECT @SQL=@SQL+@restore_files+N' NORECOVERY, NOUNLOAD, STATS = 5'+ char(10) PRINT '-- 还原完整备份' PRINT @SQLSELECT '-- 还原完整备份',@sql/*如果有差异备份。期间的事务备份不需要操作*/IF EXISTS (SELECT 1 FROM @T where [type]='I') BEGIN SELECT @diff_backup_set_id = MAX(backup_set_id)FROM @TWHERE [type] = 'I'DELETE @TWHERE backup_set_id > @pre_full_set_idAND backup_set_id < @diff_backup_set_idEND -- 指定时间之前的事务日志依次执行 SET @SQL=(SELECT N'USE [master]'+char(10)+'RESTORE '+CASE WHEN [type]='I' THEN 'DATABASE' ELSE 'LOG' END+' '+QUOTENAME(@restore_db_name)+' FROM DISK =N'''+physical_device_name+''''+ char(10)+'WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5 '+ char(10) + char(10) FROM @T WHERE is_last=0 AND backup_set_id>@pre_full_set_id AND [type] IN ('L','I')FOR XML PATH(''))PRINT '-- 还原事务备份' PRINT @SQLSELECT '-- 还原事务备份' ,@SQL -- 指定时间点的事务日志恢复SET @SQL=(SELECT 'USE [master]'+char(10)+'RESTORE LOG '+QUOTENAME(@restore_db_name)+' FROM DISK =N'''+physical_device_name+''''+ char(10)+'WITH FILE = 1, RECOVERY, NOUNLOAD, STATS = 5 , '+ 'STOPAT =N'''+CONVERT(VARCHAR(20),@recover_to,120)+''''+char(10) FROM @T WHERE is_last=1 ) PRINT '-- 还原到指定时间点' PRINT @SQLSELECT '-- 还原到指定时间点',@SQL
