reporting of all databases last backup status

• 
  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
  

  petey September 16, 2010 21:10

  - 0 +
• 
  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.

  ppared1 September 17, 2010 13:51

  - 0 +
• 
  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
  

  petey September 17, 2010 20:33

  - 0 +

Add comment

Please sign in to leave a comment.