When was your last backup run?

One of the first things I query when I take on a contract -- what type of backups are being run, and when?  On one of my recent contracts, no names mentioned, I found that no backups were being run at all!!  Run this little ditty on a single instance, or across multiple instances through your CMS.  It returns a helpful summary on the status of your backups.

  SET NOCOUNT ON;

  DECLARE @Results TABLE (
    ServerName VARCHAR(100) NULL,
    DatabaseName VARCHAR(100) NULL,
    RecoveryModel VARCHAR(100) NULL,
    LastFullBkupTime DATETIME NULL,
    DaysSinceLastFull INT NULL,
    DaysSinceLastDiff INT NULL,
    HoursSinceLastLogBkup INT,
    DBStatus VARCHAR (100) NULL,
    BkupFile VARCHAR(1000) NULL,
    Media INT
  )

  /* Get Server and database names. */

  INSERT @Results(ServerName,DatabaseName)
  SELECT CONVERT(VARCHAR,SERVERPROPERTY('ServerName')),a.name
  FROM master..sysdatabases a
  WHERE a.name <> 'tempdb'

  /* Last full bkup time and media. */

  UPDATE @Results
  SET
      LastFullBkupTime = b.backup_start_date,
      DaysSinceLastFull = DATEDIFF(dd,b.backup_start_date,GETDATE()),
      Media = b.media_SET_id
  FROM @Results a,(
    SELECT database_name, MAX(media_SET_id)media_SET_id, MAX(backup_start_date) backup_start_date
    FROM msdb..backupset
    WHERE TYPE = 'D'
    GROUP BY database_name) b
  WHERE
    a.DatabaseName = b.database_name

  /* Database status */

  UPDATE @Results
  SET DBStatus = CONVERT(sysname,DatabasePropertyEx(DatabaseName,'Status'))

 

  /* Recovery model */

  UPDATE @Results
  SET RecoveryModel = CONVERT(sysname,DatabasePropertyEx(DatabaseName,'Recovery'))

  /* Backup file location. */

  UPDATE d
  SET d.BkupFile = b.physical_device_name
  FROM @Results d, msdb..backupmediafamily b
  WHERE d.Media = b.media_SET_id
 
  /* Days since last diff bkup. */
  UPDATE d
  SET d.DaysSinceLastDiff = DATEDIFF(dd,b.backup_finish_date,GETDATE())
  FROM @Results d, (
     SELECT database_name,MAX(backup_finish_date) backup_finish_date
     FROM msdb..backupset
     WHERE TYPE  = 'I' 
     GROUP BY database_name) b
  WHERE d.DatabaseName = b.database_name

  /* Hours since last log dump. */

  UPDATE d
  SET d.HoursSinceLastLogBkup = DATEDIFF(hh,b.backup_finish_date,GETDATE())
  FROM @Results d, (
     SELECT database_name,MAX(backup_finish_date) backup_finish_date
     FROM msdb..backupset
     WHERE TYPE ='L' 
     GROUP BY database_name) b
  WHERE d.DatabaseName = b.database_name
  AND d.RecoveryModel <> 'SIMPLE'

  /* Bring it back for review. */

SELECT
    ServerName,
    DatabaseName,
    RecoveryModel,
    LastFullBkupTime,
    DaysSinceLastFull,
    DaysSinceLastDiff,
    HoursSinceLastLogBkup, 
    DBStatus,
    BkupFile,
    Media
FROM
    @Results

  SET NOCOUNT OFF;