reporting of all databases last backup status

ppared1ppared1 Posts: 3
edited September 17, 2010 8:33PM in SQL Backup Previous Versions
I would like to get an email every morning showing me each of the databases that are scheduled for backup and what is the status of the last backup and who long since the last successfull backup:
DatabaseName   Last Successfull backup   Status of last backup
==========  ===============   =============
Database1          09/09/2010                    Successfull

Where can I get/query the data to generate this report?
Is there a table I can query to obtain this info?
PParedes

Comments

  • peteypetey Posts: 2,358 New member
    It isn't easy to identify only databases that have been scheduled for backup, as you'll need to retrieve the job step command and look for the database names in it, and neither would it be easy to identify if a successful backup was the result of a job run, and not an ad-hoc backup. SQL Backup already has a feature to send an e-mail if a job run was unsuccessful.

    Here's a query that simply lists down the backup statuses for all databases:
    SELECT a.database_name, a.type, MAX(a.backup_finish_date) LastSuccessfulBackup, 
    CAST((GETDATE() - MAX(a.backup_finish_date)) AS NUMERIC(5, 2)) IntervalInDays
    FROM msdb..backupset a
    GROUP BY a.database_name, a.type
    ORDER BY a.database_name, a.type
    
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Peter,
    This information is great.
    I will put this on an SSIS package and send an email with this results.
    Thanks again for taking the time.
    PParedes
  • peteypetey Posts: 2,358 New member
    I actually posted the wrong query. That query works from the backupset table, so will not show you any databases that have never been backed up.

    This query works outwards from the sysdatabases table, but will not show you the backup history of any database that no longer exists.
    SELECT a.name, b.type, MAX(b.backup_finish_date) LastSuccessfulBackup, 
    CAST((GETDATE() - MAX(b.backup_finish_date)) AS NUMERIC(5, 2)) IntervalInDays 
    FROM master..sysdatabases a
    LEFT OUTER JOIN msdb..backupset b ON a.name = b.database_name
    GROUP BY a.name, b.type
    ORDER BY a.name, b.type
    
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.