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
· Share on Twitter

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
    · Share on Twitter
  • ppared1ppared1 Posts: 3
    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
    · Share on Twitter
  • 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
    · Share on Twitter
Sign In or Register to comment.