reporting of all databases last backup status

ppared1

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?

petey

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

ppared1

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.

petey

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