Sql-server – Backup status report from multiple server in sql server 2005

backupsql server

Per daily routine activities of DBA, as we know backups are important to be monitored and to make sure they are corrupt free, i am trying to fetch a report to get such status.

Moreover, we have SQL safe installed on three servers which do a compress backup of larger databases.

I tried a lot to look for a report from SQL safe, to fetch the backup status but could not.

I used a script and put it in a job to fetch the backup status on single server, but for multiple instances where native as well as SQL safe like a third party backup is running, how can i get the backup status on daily basis.

Kindly suggest.

Thanks– Kapil

Best Answer

It is redundant, but i have put this on every server and had it send me an email. Most third party backup tools put a value in "Description", or you can tell based on the name or user name.

select 
    bs.name backup_name,
    backup_finish_date,
    bs.database_name,
    bs.backup_size ,
    cast(bs.backup_size/1024/1024 as int) 'Backup Size MB',
    cast(bs.backup_size/1024/1024/1024 as int) 'backup size gb',
    bs.compatibility_level,
    bs.Recovery_Model,
    BackupType = 
        CASE 
            WHEN bs.type = 'L' THEN 'Log'
            WHEN bs.type = 'D' THEN 'Full'
            WHEN bs.type = 'I' THEN 'Differential'
            WHEN bs.type = 'G' THEN 'File Differential'
            WHEN bs.type = 'P' THEN 'Partial'
            WHEN bs.type = 'Q' THEN 'Partial Differential'
        END,
        [user_name],
        [description],
        cast(bs.compressed_backup_size/1024/1024 as int) 'Compressed Backup Size MB',
    cast(bs.compressed_backup_size/1024/1024/1024 as int) 'Compressed Backup Size gb'

from 
backupset bs

where bs.backup_finish_date > '12/01/2014'
--and

--bs.database_name in('')
--either put a list of databases you care about, or do a select from   
--sys.databases...can change the logic to reflect dbs what haven't been backed up


order by bs.backup_finish_date desc
Related Question