June 13, 2019 at 10:13 pm
I am going to develop a dashboard to monitor various stats about our Testing Environments. One piece of information I want to pull is the date that a refresh from Production backup was completed. Is there a way to pull when a database was restored from a backup file in T-SQL?
June 13, 2019 at 11:13 pm
msdb has a restorehistory table that you may want to look at. It doesn't specify any details of the restore process, it just reports on restore operations.
Sue
June 14, 2019 at 12:56 am
Thank you. This is exactly what I needed.
June 14, 2019 at 10:01 am
msdb has a restorehistory table that you may want to look at. It doesn't specify any details of the restore process, it just reports on restore operations. Sue
This table has backup_set_id column which can be used for getting data from other tables - backupset & backupmediafamily
It gives a lot of additional info about backup file used, source server, etc.
For example:
select
/*Restore History*/
rh.restore_date [Restore 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 'Cancel'
else rh.restore_type end as [Restore Type]
, rh.destination_database_name [Destination DB]
, rh.[user_name] [Restored By]
/* backup media family */
, bmf.physical_device_name [Backup Device]
, case
when bmf.device_type=2 then 'Disk'
when bmf.device_type=5 then 'Tape'
when bmf.device_type=7 then 'Virtual Device'
when bmf.device_type=105 then 'Backup Device'
else convert(varchar(10),bmf.device_type) end [Device Type]
/*Backupset Info*/
, bs.name [Backupset Name]
, bs.[description] [Backupset Desc]
, case
when bs.[type] = 'D' then 'Database'
when bs.[type] = 'I' then 'Diff DB'
when bs.[type] = 'L' then 'Log'
when bs.[type] = 'F' then 'File or FG'
when bs.[type] = 'G' then 'Diff File'
when bs.[type] = 'P' then 'Partial'
when bs.[type] = 'Q' then 'Diff Part'
else bs.[type] end as [Backup Type]
, convert(decimal(10,1),bs.backup_size/1024./1024.) [Backup Size (MB)]
, convert(decimal(10,1),bs.compressed_backup_size/1024./1024.) [Backup Size Compressed (MB)]
, convert(decimal(5,1),100-bs.compressed_backup_size/bs.backup_size*100) [Compression %]
, bs.server_name [Source SQL]
, convert(varchar(10),bs.software_major_version)+'.'
+ convert(varchar(10),bs.software_minor_version)+'.'
+ convert(varchar(10),bs.software_build_version) [SQL Version]
, bs.[database_name] [Source DB (SDB)]
, bs.database_creation_date [SDB Created]
, bs.[compatibility_level] [SDB CompLevel]
, bs.[collation_name] [SDB Collation]
, bs.recovery_model [SDB Recovery]
, bs.[user_name] [Backup Made By]
, bs.backup_start_date [Backup Started]
, bs.backup_finish_date [Backup Finished]
/* LSNs */
, bs.database_backup_lsn
, bs.first_lsn
, bs.last_lsn
, bs.checkpoint_lsn
/*Restore Options*/
, rh.[replace] [With Replace]
, rh.[recovery] [With Recovery]
, rh.stop_at
, rh.stop_at_mark_name
, rh.stop_before
from msdb.dbo.restorehistory rh
join msdb.dbo.backupset bs on rh.backup_set_id=bs.backup_set_id
join msdb.dbo.backupmediafamily bmf on bs.media_set_id=bmf.media_set_id
June 14, 2019 at 2:49 pm
Very nice! Thank you!
June 14, 2019 at 3:27 pm
I am going to develop a dashboard to monitor various stats about our Testing Environments. One piece of information I want to pull is the date that a refresh from Production backup was completed. Is there a way to pull when a database was restored from a backup file in T-SQL?
We use this in conjunction with their customizable SQL Server sensors. Cheap, powerful, flexible.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply