Tuesday 9 October 2012

Database Backup Status Script

---Script to find the Database backup details in last 10 days

select bs.server_name, bs.database_name,convert(decimal(7,2),(bs.backup_size/1024/1024/1024)) backup_size_GB,
bmf.device_type ,
bs.backup_start_date,
bs.backup_finish_date,bmf.physical_device_name,
bs.first_lsn,bs.last_lsn,bs.checkpoint_lsn,bs.database_backup_lsn
from msdb.dbo.backupset bs join
msdb.dbo.backupmediafamily bmf
on bs.media_set_id=bmf.media_set_id
where bs.backup_start_date>=(GETDATE()-10)
--and database_name='Master'
order by bs.database_name,bs.backup_start_date