Thursday, September 13, 2007

How old is the Database if restored from the Backup file

SELECT DISTINCT TOP 100
RH.destination_database_name,
RF.file_number,
RH.restore_date,
RF.destination_phys_drive,
RF.destination_phys_name,
[Backup Set Name]=BS.name,
BS.description,
BS.database_creation_date,
BS.backup_start_date,
BS.database_name,
BS.server_name,
RH.restore_type,
RH.replace,
RH.stop_at
FROM msdb.dbo.restorehistory AS RH
LEFT OUTER JOIN msdb.dbo.restorefile AS RF
ON RF.restore_history_id = RH.restore_history_id
LEFT OUTER JOIN msdb.dbo.restorefilegroup AS RG
ON RG.restore_history_id = RH.restore_history_id
LEFT OUTER JOIN msdb.dbo.backupset AS BS
ON BS.backup_set_id = RH.backup_set_id
WHERE 1=1
-- AND RH.destination_database_name = N'MyDatabaseName' -- SELECT DB_NAME()
-- restore_type : D=Full, I=Differential, L=Log, G=FileGroup, V=VerifyOnly
-- AND RH.restore_type = 'D'
ORDER BY RH.restore_date DESC,
RF.file_number DESC