Monitoring Database Backups Details using T-SQL SELECT Statement

Introduction

As a database administrator, one of the crucial tasks is to ensure regular backups of databases. Monitoring the Database Backups Details including backup status and locations of different types of backups can be challenging in a large SQL Server environment. In this blog post, we will explore a T-SQL SELECT statement that provides comprehensive information about the backup status of databases, including full, differential, and transaction log backups. By executing this query, DBAs can quickly assess the backup situation and take appropriate actions if necessary.

T-SQL SELECT Statement for Monitoring Database Backups

/*
Author: [https://ourtechideas.com/]
Date: [27/05/2022]
Description: [Monitoring Database Backups Details using T-SQL SELECT Statement]
*/
SELECT
    CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
    d.name AS DatabaseName,
    d.database_id AS ID,
    d.state_desc AS Status,
    MAX(CASE WHEN bu.TYPE = 'D' THEN bu.LastBackupDate END) AS FullDBBackupStatus,
    MAX(CASE WHEN bu.TYPE = 'I' THEN bu.LastBackupDate END) AS DifferentialDBBackupStatus,
    MAX(CASE WHEN bu.TYPE = 'L' THEN bu.LastBackupDate END) AS TransactionDBBackupStatus,
    CASE d.recovery_model
        WHEN 1 THEN 'Full'
        WHEN 2 THEN 'Bulk Logged'
        WHEN 3 THEN 'Simple'
    END AS RecoveryModel,
    MAX(CASE WHEN bu.TYPE = 'D' THEN bu.LastBackupFileLocation END) AS FullDBBackupLocation,
    MAX(CASE WHEN bu.TYPE = 'I' THEN bu.LastBackupFileLocation END) AS DifferentialDBBackupLocation,
    MAX(CASE WHEN bu.TYPE = 'L' THEN bu.LastBackupFileLocation END) AS TransactionDBBackupLocation
FROM sys.databases d -- Retrieve database information from sys.databases
LEFT JOIN (
    SELECT database_name, TYPE, backup_start_date AS LastBackupDate,
        physical_device_name AS LastBackupFileLocation,
        ROW_NUMBER() OVER (PARTITION BY database_name, TYPE ORDER BY backup_start_date DESC) AS BackupRank
    FROM msdb.dbo.backupset bs -- Retrieve backup information from msdb.dbo.backupset
    JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id -- Join backupset and backupmediafamily using media_set_id
) AS bu ON d.name = bu.database_name AND bu.BackupRank = 1 -- Join the main query with the subquery to get the latest backup for each database and type
GROUP BY d.name, d.database_id, d.state_desc, d.recovery_model; -- Group the results by database name, ID, status, and recovery model

Explanation

1. The CONVERT(CHAR(100), SERVERPROPERTY('Servername')) function retrieves the server name and converts it to a character data type with a length of 100 characters. This provides information about the server where the query is executed.
2. The main query starts by selecting the necessary columns from the sys.databases system view, including the database name, ID, status, and recovery model.
3. The query then performs a LEFT JOIN operation with a subquery that retrieves backup information from the msdb.dbo.backupset table. The subquery uses the ROW_NUMBER() function to determine the latest backup for each database and backup type (full, differential, transaction log) based on the backup start date.
4. Within the main query, three MAX functions combined with CASE statements are used to retrieve the last backup dates for each type of backup (full, differential, transaction log). If no backup of a particular type exists for a database, NULL is returned.
5. Similarly, three MAX functions are used with CASE statements to retrieve the corresponding backup file locations for each backup type.
6. The CASE statement inside the main query maps the recovery model values (1, 2, 3) to their respective names (Full, Bulk Logged, Simple) for better readability.
7. Finally, the results are grouped by the database name, ID, status, and recovery model using the GROUP BY clause.

Conclusion

The provided T-SQL SELECT statement allows database administrators to monitor the backup status and locations of their SQL Server databases. By executing this query, DBAs can quickly identify databases without recent backups or those that require attention. Monitoring database backups is crucial for ensuring data integrity, disaster recovery preparedness, and compliance with organizational backup policies. With the information obtained from this query, DBAs can take appropriate actions to address any backup-related issues promptly.