Last Backup Dates

Following script lists last full, diff and transaction log backup dates and some useful details of all databases in an instance.

select db_name,backup_type,backup_size_in_MB,
backup_start_date,backup_finish_date,is_copy_only,
physical_device_name,recovery_model_desc
from (
select d.database_id,
d.name as db_name,
case backup_types.Backup_Type
when 'D' then 'Full Backup'
when 'I' then 'Differential Backup'
when 'L' then 'Transaction Log Backup'
end as 'backup_type',
cast(bs.compressed_backup_size/1048576 as decimal(15,2)) as 'backup_size_in_MB',
bs.backup_start_date,
bs.backup_finish_date,
bs.is_copy_only,
bmf.physical_device_name,
d.recovery_model_desc,
row_number() over(partition by d.database_id,backup_types.Backup_Type order by bs.backup_start_date desc) as rwn
from sys.databases as d
cross join(
select 'D' as Backup_Type
union
select 'I'
union
select 'L'
) as backup_types
left join msdb.dbo.backupset as bs on (d.name=bs.database_name and backup_types.Backup_Type=bs.type)
left join msdb.dbo.backupmediafamily as bmf on bs.media_set_id=bmf.media_set_id
where isnull(bs.is_copy_only,0)=0 --- if you want to filter copy_only backups
) as tbl
where tbl.rwn=1
and tbl.database_id>4 --- eliminate system databases
order by tbl.database_id asc

If you want to filter system databases and copy_only backups, mind the comments in the script.

You can notice the below result set some columns contain NULL. Does that mean there are no backups have been performed? May be. Please ensure that backup and restore history cleanup script has not been run recently in msdb database. Maybe later I will explain why do we need cleaning backup/restore history tables and how to clean up?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.