One of the most important tasks on a DBA’s to-do list is backing up databases on a regular basis. This is because reliable backups are the most important tool in ensuring data recovery. Therefore, it is important for the DBA to check for database backups and validate that they have been successfully created and saved in a secure location.

SQL Server keeps information about each successful backup in the msdb database. Information about each successful backup operation is stored in the backupset table, and information about each backup physical file in the backupmediafamily table. I wrote the following query, which can be used to check all databases’ backup status for any given SQL Server instance:

USE [msdb]
GO
;WITH [MostRecentBackupStatus_CTE]
AS
(
SELECT  bsfull.[server_name] ,
bsfull.[database_name] ,
bsfull.[backup_finish_date] AS [last_full_backup] ,
bsdiff.[backup_finish_date] AS [last_diff_backup] ,
bstlog.[backup_finish_date] AS [last_tran_backup] ,
DATEDIFF(dd, bsfull.[backup_finish_date], CURRENT_TIMESTAMP) AS [days_since_full_backup] ,
DATEDIFF(dd, bsdiff.[backup_finish_date], CURRENT_TIMESTAMP) AS [days_since_diff_backup] ,
DATEDIFF(hh, bstlog.[backup_finish_date], CURRENT_TIMESTAMP) AS [hours_since_tranlog_backup] ,
( SELECT    [physical_device_name]
  FROM      [msdb]..[backupmediafamily] bmf
  WHERE     bmf.[media_set_id] = bsfull.[media_set_id]
) AS [full_backup_location] ,
( SELECT    [physical_device_name]
  FROM      [msdb]..[backupmediafamily] bmf
  WHERE     bmf.[media_set_id] = bsdiff.[media_set_id]
) AS [diff_backup_location] ,
( SELECT    [physical_device_name]
  FROM      [msdb]..[backupmediafamily] bmf
  WHERE     bmf.[media_set_id] = bstlog.[media_set_id]
) AS [tlog_backup_location]
FROM    [msdb]..[backupset] AS bsfull
LEFT JOIN [msdb]..[backupset] AS bstlog ON bstlog.[database_name] = bsfull.[database_name]
   AND bstlog.[server_name] = bsfull.[server_name]
   AND bstlog.[type] = 'L'
   AND bstlog.[backup_finish_date] = ( (SELECTMAX([backup_finish_date])
FROM[msdb]..[backupset] b2
WHEREb2.[database_name] = bsfull.[database_name]
AND b2.[server_name] = bsfull.[server_name]
AND b2.[type] = 'L') )
LEFT JOIN [msdb]..[backupset] AS bsdiff ON bsdiff.[database_name] = bsfull.[database_name]
   AND bsdiff.[server_name] = bsfull.[server_name]
   AND bsdiff.[type] = 'I'
   AND bsdiff.[backup_finish_date] = ( (SELECTMAX([backup_finish_date])
FROM[msdb]..[backupset] b2
WHEREb2.[database_name] = bsfull.[database_name]
AND b2.[server_name] = bsfull.[server_name]
AND b2.[type] = N'I') )
WHERE   bsfull.[type] = N'D'
AND bsfull.[backup_finish_date] = ( (SELECT MAX([backup_finish_date])
 FROM   [msdb]..[backupset] b2
 WHERE  b2.[database_name] = bsfull.[database_name]
AND b2.[server_name] = bsfull.[server_name]
AND b2.[type] = N'D') )
AND EXISTS ( SELECT [name]
 FROM   [master].[sys].[databases]
 WHERE  [name] = bsfull.[database_name] )
AND bsfull.[database_name] <> N'tempdb'
)
SELECT  c.[server_name] ,
        c.[database_name] ,
        d.[recovery_model_desc] ,
        c.[last_full_backup] ,
        c.[last_diff_backup] ,
        c.[last_tran_backup] ,
        c.[days_since_full_backup] ,
        c.[days_since_diff_backup] ,
        c.[hours_since_tranlog_backup] ,
        c.[full_backup_location] ,
        c.[diff_backup_location] ,
        c.[tlog_backup_location]
FROM    [MostRecentBackupStatus_CTE] c
        INNER JOIN [master].[sys].[databases] d ON c.[database_name] = d.[name];
GO

This query returns the following columns:

• server_name – Specifies the name of the SQL Server
• database_name – Specifies the name of the database.
• recovery_model_desc – Specifies the recovery model of the database.
• last_full_backup – Specifies the date time of the latest full backup.
• last_diff_backup – Specifies the date time of the latest differential backup.
• last_tran_backup – Specifies the date time of the latest transactional log backup.
• days_since_full_backup – Number of days since the last successful full backup.
• days_since_diff_backup – Number of days since the last successful differential backup.
• hours_since_tranlog_backup – Number of days since the last successful log backup
• full_backup_location – Specifies the physical name of the full backup file.
• diff_backup_location – Specifies the physical name of the differential backup file.
• tlog_backup_location – Specifies the physical name of the transactional backup file.

I have created a SSIS package that runs this script on a daily basis on each of my Production SQL Servers and then copy that information into our CMS database. This approach helps me to quickly identify the databases where backup is not successful.

Please share your approach, which you use to accomplish this task. It will be interesting to know that.