3

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

Improve this question
4
  • What information are you trying to gather? Where they are? Sizes? If you're trying to validate that they work, the only way to do so is to actually restore the data. As a DBA, no backup is considered a valid backup unless it actually restores without error. Just tested mine about an hour ago, actually.
    – Kris Gruttemeyer
    Commented Dec 9, 2014 at 16:34
  • Kris@ Yes i am looking for a report which tells me where the backups are occuring, type of backup, sizes , date etc. For validation we run a process for restore verify. But for multiple servers how can i fetch such report of backups
    – KASQLDBA
    Commented Dec 9, 2014 at 17:03
  • Does Idera SQL Safe update the 5 msdb tables for backups: backupfile, ..., backupset? I would think so! Those tables should give you the information you need to restore from each server's backups.
    – RLF
    Commented Dec 9, 2014 at 18:24
  • RLF@ on the Target server for whom i am taking backups, no those 5 msdb tables are not getting updated. However on Repository where idera is installed , yes on that db server it is!
    – KASQLDBA
    Commented Dec 10, 2014 at 5:32

2 Answers 2

Reset to default
1

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
Improve this answer
2
  • @ Amanda thanks, but i believe this code works for 2k8 and above, unfortunately for me its 2k5 and also every time when i will get the results it will fetch from the same old date to current.
    – KASQLDBA
    Commented Dec 10, 2014 at 11:58
  • If you rem out the references to the backup compression, it should work fine in 2005. Sorry, I was in a hurry.
    – rottengeek
    Commented Dec 17, 2014 at 21:09
1

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.

Improve this answer
2
  • We use the linked server method to centralize our backup configuration. We deploy standard SQL Agent jobs to each server using the central database of backup configurations and implement the proceeses using Ola Hallengren's maintenance scripts. And we use a dedicated restore server to test our restores and run CHECKDB on them.
    – RLF
    Commented Dec 9, 2014 at 21:18
  • Shawn@ I believe i cant go with option two, being my servers 2005 version. However option 1 can be considered, can you share a link or doc on how to get that set up so that can be helpful, thanks!
    – KASQLDBA
    Commented Dec 10, 2014 at 5:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.