January 6, 2011 at 5:05 am
Hi, I want to know when the specific database was restored last time?
What dynamic views or system tables can give me this information?
thanks in advance.
January 6, 2011 at 5:14 am
You need to query the backupset, backupmediafamily and backupfile tables in msdb. The following query should work on all versions of SQL Server and return the most recent backup of each type for each database.
SELECT
r.DBName
,r.type
,r.BackupStart
,s.backup_finish_date
,s.backup_size
,m.physical_device_name
FROM ( --gets most recent of each type of backup for each DB
SELECT
d.name AS DBName
,b.type
,max(b.backup_start_date) AS BackupStart
FROM master.dbo.sysdatabases d
LEFT JOIN msdb.dbo.backupset b
ON d.name = b.database_name
JOIN msdb..backupmediafamily f
ON b.media_set_id = f.media_set_id
GROUP BY d.name, b.type) r
JOIN msdb.dbo.backupset s
ON r.DBName = s.database_name
AND r.type = s.type
AND r.BackupStart = s.backup_start_date
JOIN msdb.dbo.backupmediafamily m
ON s.media_set_id = m.media_set_id
John
January 6, 2011 at 6:51 am
for restores you need to use table msdb..restorehistory
--returns info on when databases were restored and from which backup file
SELECT [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
---------------------------------------------------------------------
January 9, 2011 at 4:58 am
thank you very much
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply