There comes a time in DBA's life to check a database for its backup and restore history. Usually a DBA also needs to know file name where the database was backed up or may be a DBA needs to know the source server name of a database which was recently restored.
1.0
select s.backup_set_id,s.user_name,s.backup_start_date,s.backup_finish_date,
datediff(mi,s.backup_start_date,s.backup_finish_date)as mins,
datediff(s,s.backup_start_date,s.backup_finish_date)as sec,s.type,s.backup_size,s.database_name,f.physical_device_name,s.server_name,s.recovery_model
from msdb..backupset s,msdb..backupmediafamily f
where s.media_set_id=f.media_set_id
and s.database_name='{Database name}'
order by s.backup_finish_date desc
select h.backup_set_id,h.destination_database_name,f.physical_device_name,h.restore_date ,h.user_name,h.restore_type,h.replace,s.server_name
from msdb..restorehistory h,msdb..backupmediafamily f,msdb..backupset s
where h.backup_set_id=s.backup_set_id and s.media_set_id=f.media_set_id
and h.destination_database_name ='{Database name}'
order by h.restore_date desc
blog comments powered by Disqus