#1 楼
这将列出服务器上每个数据库的所有“最新”还原:WITH LastRestores AS
(
SELECT
DatabaseName = [d].[name] ,
[d].[create_date] ,
[d].[compatibility_level] ,
[d].[collation_name] ,
r.*,
RowNum = ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY r.[restore_date] DESC)
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.Name
)
SELECT *
FROM [LastRestores]
WHERE [RowNum] = 1
评论
restorehistory中的记录可以由sp_delete_backuphistory清除。
–zhongxiao37
17年5月12日在7:24
#2 楼
可以使用的脚本是:declare @DB sysname = 'MyDB';
select * from msdb.dbo.restorehistory where destination_database_name = @DB;
#3 楼
用于此目的的主表是msdb..restorehistorySELECT [rs].[destination_database_name] ,
[rs].[restore_date] ,
[bs].[backup_start_date] ,
[bs].[backup_finish_date] ,
[bs].[database_name] AS [source_database_name] ,
[bmf].[physical_device_name] AS [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [rs].[restore_date] DESC
from mssqltips
评论
+1除了恢复日期外,它还返回备份日期,这对我来说也很有用
– Sarh
18年9月5日13:21
#4 楼
它会为您提供数据库的最新刷新日期:use [msdb]
select top 1 * from restorehistory
where destination_database_name='DB_NAME'
order by restore_history_id desc
评论
消息208,级别16,状态1,第2行无效的对象名称“ restorehistory”。
– John Waclawski
17年3月13日在15:22
#5 楼
添加有关OS用户名和计算机名的重要信息:Select Destination_database_name,
restore_date,
database_name as Source_database,
Physical_device_name as Backup_file_used_to_restore,
bs.user_name,
bs.machine_name
from msdb.dbo.restorehistory rh
inner join msdb.dbo.backupset bs
on rh.backup_set_id=bs.backup_set_id
inner join msdb.dbo.backupmediafamily bmf
on bs.media_set_id =bmf.media_set_id
ORDER BY [rh].[restore_date] DESC
#6 楼
是否有一个T-SQL查询显示特定数据库的最后还原日期时间?
嗨,
我生成了以下脚本,可以为您提供有关查询的更多详细信息。
查询以获取有关特定数据库的“最新还原”信息:
WITH MostRecentRestore AS
(
SELECT
RowNum = ROW_NUMBER() OVER (PARTITION BY RH.Destination_database_name ORDER BY RH.Restore_Date DESC),
RH.Restore_date,
BS.[database_name] as Source_Database,
RH.Destination_Database_Name,
BS.Backup_Start_Date,
BS.Backup_Finish_Date,
CASE WHEN RH.restore_type = 'D' THEN 'Database'
WHEN RH.restore_type = 'F' THEN 'File'
WHEN RH.restore_type = 'G' THEN 'Filegroup'
WHEN RH.restore_type = 'I' THEN 'Differential'
WHEN RH.restore_type = 'L' THEN 'Log'
WHEN RH.restore_type = 'V' THEN 'Verifyonly'
WHEN RH.restore_type = 'R' THEN 'Revert'
ELSE RH.restore_type
END AS Restore_Type,
RH.[Replace],
RH.[Recovery],
RH.Restore_Date AS Restored_On,
BMF.physical_device_name AS Restored_From,
RF.destination_phys_name AS Current_DB_File_Location,
RH.user_name AS Restored_By,
BS.machine_name,
BS.Server_Name
FROM msdb.dbo.RestoreHistory RH
INNER JOIN msdb.dbo.BackupSet BS ON RH.backup_set_id = BS.backup_set_id
INNER JOIN msdb.dbo.restorefile RF ON RH.Restore_History_id = RF.Restore_History_id
INNER JOIN msdb.dbo.Backupmediafamily BMF ON bs.media_set_id = bmf.media_set_id
)
SELECT *
FROM MostRecentRestore
WHERE [RowNum] = 1 AND destination_database_name = 'YourDatabaseName'
#7 楼
Select Destination_database_name,
restore_date,database_name as Source_database,
Physical_device_name as Backup_file_used_to_restore
from msdb.dbo.restorehistory rh
inner join msdb.dbo.backupset bs
on rh.backup_set_id=bs.backup_set_id
inner join msdb.dbo.backupmediafamily bmf
on bs.media_set_id =bmf.media_set_id
ORDER BY [rh].[restore_date] DESC
有关详细信息,您可以检查以下链接:
http://www.passionforsql.com/how-to-find-when-last-database-backuprestore- was-done /
评论
第2次点击:mssqltips.com/sqlservertip/1724/…