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
GO

SET NOCOUNT ON
GO

SELECT
  database_name,
  backup_finish_date = CASE
    WHEN db_backup_finish_date > log_backup_finish_date THEN db_backup_finish_date
    ELSE log_backup_finish_date
    END
FROM (
  SELECT
    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
Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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