SQL Server怎样批量自动还原日志

发布于 2020-08-07 12:13:35

请问SQL Server数据库如果备份文件很旧,中间过程积累了很多日志的情况下,如果手动一个个还原实在太累太长时间了,有什么方法可以批量自动还原?

查看更多

关注者
0
被浏览
186
1 个回答
admin
admin 2020-08-07
韬光养晦,厚积薄发!

可以采用下面脚本实现:

SELECT bs.database_name AS 'Database Name',
bs.backup_start_date AS 'Backup Start',
bs.backup_finish_date AS 'Backup Finished',
DATEDIFF(MINUTE, bs.backup_start_date, bs.backup_finish_date) AS 'Duration (min)',
'restore '+ 
CASE bs.[type] 
WHEN 'D' THEN 'database ' 
else 'log ' 
end
+bs.database_name +' from disk='''+ bmf.physical_device_name + ''' with norecovery;' AS 'Backup File',
CASE
WHEN bs.[type] = 'D' THEN 'Full Backup'
WHEN bs.[type] = 'I' THEN 'Differential Database'
WHEN bs.[type] = 'L' THEN 'Log'
WHEN bs.[type] = 'F' THEN 'File/Filegroup'
WHEN bs.[type] = 'G' THEN 'Differential File'
WHEN bs.[type] = 'P' THEN 'Partial'
WHEN bs.[type] = 'Q' THEN 'Differential partial'
END AS 'Backup Type'
FROM msdb.dbo.backupmediafamily bmf WITH(NOLOCK)
INNER JOIN msdb..backupset bs WITH(NOLOCK)
ON bmf.media_set_id = bs.media_set_id
WHERE bs.database_name = 'test' --请输入要还原的库名
and bs.backup_start_date>='2019-04-01 04:00:00.000' --请输入要还原的起始时间
and bs.backup_finish_date <= '2019-09-01 18:16:03.000' --请输入要还原的结束时间
ORDER BY bs.backup_start_date asc

图中时间可以自定义还原的范围,用上述SQL先查出结果后,执行复制到SQL窗口执行即可,但这里要注意后面部分,即最后一个日志要加上recovery结尾,表示还原结束。

撰写答案

请登录后再发布答案,点击登录

发布
问题

分享
好友

手机
浏览

扫码手机浏览