Backup status report from multiple server in sql server 2005

Posted on

Question :

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

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

There are multiple ways of building out the report you want. The two more popular or ones I likely chose from are below.

One option is to use Linked Servers from one central instance to keep it centralized. You use OPENQUERY or physically created linked servers for each server. This option would require a bit of setup on each instance but once done your done.

Option two that I would probably go with is using PowerShell to execute the query you built against each instance. I can then write that data out to a dynamic HTML report that could be emailed or saved out to a central network share.

Leave a Reply

Your email address will not be published. Required fields are marked *