How to view backup history:The insights of an SQL geek

The insights of an SQL geek: How to view backup history

Wednesday, January 7, 2009

How to view backup history

Hello all,

I found this little script that helped me to view all my backup history

SELECT, bkup.description, bkup.backup_finish_date,
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)
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
where backup_finish_date >
DATEADD(DAY, -60, (getdate()))
ORDER BY, bkup.backup_finish_date desc

thanks to homebrew01


1 comment: