mssql自动生成恢复脚本

– mssql自动生成恢复脚本
– 适用于完全模式下，自动生成能够恢复到指定时点的数据库恢复脚本
– 建议异机使用（在原机直接使用执行有风险，会影响原库，除非你真的确认）
– 使用前注意检查日志
– SqlServer2012 测试通过
– 如有异常，请留言
– 2018-12-18

1. 创建一个备份视图，方便查看备份历史
   ‘’’
   USE master
   GO

IF OBJECT_ID(‘v_sm_backup_history’,‘v’) IS NOT NULL
DROP VIEW v_sm_backup_history;
GO
– =============================================
– Author: qin
– Create date: <2017-12-15>
– Description: 备份历史
– =============================================
CREATE VIEW v_sm_backup_history
WITH encryption
as
SELECT
bs.database_name,
bs.backup_start_date,
bs.backup_finish_date,
bs.expiration_date, --过期时间
bs.backup_set_id backup_setid,bs.name backup_setname,
bms.media_set_id media_setid,bms.name media_setname,
bmf.physical_device_name,
bs.position,
CASE bs.type
WHEN ‘D’ THEN ‘full’
WHEN ‘I’ THEN ‘differential’
WHEN ‘L’ THEN ‘log’
WHEN ‘F’ THEN ‘file / filegroup’
WHEN ‘G’ THEN ‘differential file’
WHEN ‘P’ THEN ‘partial’
WHEN ‘Q’ THEN ‘differential partial’
END AS type, --备份类型。可以是：D = 数据库 I = 差异数据库 L = 日志 F = 文件或文件组 G = 差异文件 P = 部分 Q = 差异部分 可以为 NULL。
round(bs.backup_size/1024.0/1024.0/1024.0,3) backup_sizeGB_total,
round(bs.compressed_backup_size/1024/1024/1024.0,3) compressed_backup_sizeGB_total

FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediaset bms
ON bs.media_set_id = bms.media_set_id
INNER JOIN msdb.dbo.backupmediafamily bmf
ON bs.media_set_id = bmf.media_set_id
GO

–验证视图
select * from v_sm_backup_history WHERE database_name = ‘test’ and backup_start_date between ‘2018-12-18 16:30:00.000’ and ‘2018-12-20 23:59:59.997’ order by backup_start_date asc
‘’’

2. 设置恢复参数，自动生成恢复链及操作脚本
   ‘’’
   USE master
   GO

set nocount off;
–此处设置参数
declare @sour_database varchar(64)=‘test3’ --原数据库名（用于查找备份链）
declare @dest_database varchar(64)=‘test3_bak’ --恢复后的数据库名称
declare @recover_time datetime = ‘2018-12-18 18:00:18.553’; --希望恢复的时点

–下面正式执行
declare @last_fullbacup_time datetime,@last_diffbacup_time datetime,@near_logbacup_time datetime;
if @recover_time > getdate()
begin
print ‘目标时间晚于系统时间，恢复无意义，退出！’;
return
end

–1.查找“恢复时刻前”的最近完整备份时点
select top 1 @last_fullbacup_time = backup_start_date from v_sm_backup_history WHERE type = ‘full’ and backup_start_date <= @recover_time and database_name = @sour_database order by backup_start_date desc;
if @last_fullbacup_time is null
print ‘不存在完整备份，无法恢复！’;

–2.查找“完备时间后，恢复时点前”的最近差异备份时点
select top 1 @last_diffbacup_time = backup_start_date from v_sm_backup_history WHERE type = ‘differential’ and backup_start_date between @last_fullbacup_time and @recover_time and database_name = @sour_database order by backup_start_date desc

–3.查找恢复时刻后最近日志备份时点
if @last_diffbacup_time is null or @last_diffbacup_time < @recover_time
select top 1 @near_logbacup_time = backup_start_date from v_sm_backup_history WHERE type = ‘log’ and backup_start_date >= @recover_time and database_name = @sour_database order by backup_start_date asc;

–几个关键恢复时间点
select @last_fullbacup_time last_fullbacup_time ,@last_diffbacup_time last_diffbacup_time,@recover_time recover_time,@near_logbacup_time near_logbacup_time

if @last_diffbacup_time < @recover_time and @near_logbacup_time is null
begin
print ‘需要对数据库备份日志！’;
return
end

–查看恢复脚本
;with cte as
(
select * from v_sm_backup_history WHERE type = ‘full’ and backup_start_date = @last_fullbacup_time and database_name = @sour_database --最近全备
union all
select * from v_sm_backup_history WHERE type = ‘differential’ and backup_start_date = @last_diffbacup_time and database_name = @sour_database --最近差备
union all
select * from v_sm_backup_history WHERE type = ‘log’ and backup_start_date > isnull(@last_diffbacup_time,@last_fullbacup_time) and backup_start_date <= @near_logbacup_time and database_name = @sour_database --差备后到恢复时点(含)之间的日志备份，恢复时点之后第一个日志备份恢复到时点
)
select
media_setid,backup_start_date,physical_device_name physical_backupfile,position,
‘RESTORE ’
+ CASE type
WHEN ‘full ’ THEN ‘DATABASE ’
WHEN ‘differential ’ THEN ‘DATABASE ’
WHEN ‘log ’ THEN ‘LOG ’
ELSE ‘’
END
+ @dest_database + ’ FROM DISK =’’’ + physical_device_name
+ CASE
WHEN backup_start_date <= @recover_time THEN ‘’’ WITH FILE = ’ + cast(position as varchar(10)) + ‘,NORECOVERY,NOUNLOAD;’
WHEN backup_start_date = @near_logbacup_time THEN ‘’’ WITH FILE = ’ + cast(position as varchar(10)) + ‘,NORECOVERY,NOUNLOAD,STOPAT = ‘’’ + convert(varchar(23),@recover_time,121) + ‘’’;’
ELSE ‘’
END RestoreSql
from cte
union all
select ‘99991231’ media_setid ,‘9999-12-31’ backup_start_date ,null physical_backupfile,null position,‘RESTORE log ’ + @dest_database + ’ WITH RECOVERY;’ RestoreSql
order by media_setid asc,backup_start_date asc

– 结果集显示了需要恢复的备份链及脚本，使用前请自行确认
‘’’

–3. 手动执行生成的恢复脚本
– 使用前检查需要用到的备份文件是否正确完整！
– 再次警告，生产或者其它重要环境请谨慎执行！！！