SELECT sysdb.name, bkup.description, bkup.backup_finish_date,
case
when type=’D‘ then ‚** FULL **‘
when type=’I‘ then ‚DIFFERENTIAL‘
when type=’L‘ then ‚LOG‘
end as Backup_Type,
(STR(ABS(DATEDIFF(day, GetDate(),(backup_finish_date))))) as ‚Days_Ago‘,
ceiling(bkup.backup_size /1048576) as ‚Size Meg‘ ,
cast((bkup.backup_size /1073741824) as decimal (9,2)) as ‚Gig‘,
server_name, sysdb.crdate
,datediff(minute, bkup.backup_start_date, bkup.backup_finish_date) as ‚Mins‘
,cast(cast(datediff(minute, bkup.backup_start_date, bkup.backup_finish_date)
as decimal (8,3))/60 as decimal (8,1)) as ‚Hours‘, first_lsn, last_lsn, checkpoint_lsn
FROM master.dbo.sysdatabases sysdb LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name = sysdb.name
where backup_finish_date > DATEADD(DAY, -60, (getdate())) — Last 60 days
–AND sysdb.name = ‚MY_DB_NAME‘
ORDER BY sysdb.name, bkup.backup_finish_date desc