Last backups in a glance

As most other DBA’s I’m lazy and I try to make things as easy as possible for myself without missing out on anything. And the most important task of my day is to check the latest backups on all servers and I don’t think it can be done any easier than this. Unfortunately I don’t have the luxury of having all my servers registered in Management Studio because of security regulations but if you do, running this as a multi-server query should make things *reeeally* easy for you:

--> Latest backups
USE master


  backup_finish_date = CASE
    WHEN db_backup_finish_date > log_backup_finish_date THEN db_backup_finish_date
    ELSE log_backup_finish_date
    database_name = a.[name],
    db_backup_finish_date = 
      COALESCE(MAX(db_backup.backup_finish_date), '1900-01-01 00:00:00'),
    log_backup_finish_date = 
      COALESCE(MAX(log_backup.backup_finish_date), '1900-01-01 00:00:00')
  FROM sys.databases a
    LEFT OUTER JOIN msdb..backupset AS db_backup
      ON a.[name] = db_backup.database_name
        AND db_backup.[Type] = 'D'
    LEFT OUTER JOIN msdb..backupset AS log_backup
      ON db_backup.database_name = log_backup.database_name
        AND log_backup.[Type] = 'L'
  GROUP BY a.[name]
  ) AS dt

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s