Querying backup-related information in SQL Server

[Technical Editor]

Querying backup-related information in SQL Server involves accessing the msdb database, specifically the tables like backupset, backupmediafamily, etc. These tables store comprehensive details about backups performed in the SQL Server instance. Here are several scripts that query different aspects of backup information:

1. List of All Database Backups
This query provides information about all database backups, including the backup type, date, size, and duration.

1. List of All Database Backups

This query provides information about all database backups, including the backup type, date, size, and duration.

SELECT 
    bs.database_name, 
    bs.backup_start_date, 
    bs.backup_finish_date, 
    bs.backup_size, 
    bs.backup_type,
    DATEDIFF(MINUTE, bs.backup_start_date, bs.backup_finish_date) AS DurationMinutes,
    bmf.physical_device_name
FROM 
    msdb.dbo.backupset bs
JOIN 
    msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
ORDER BY 
    bs.backup_start_date DESC
        

2. Last Backup for Each Database

To find out when each database was last backed up:

SELECT
    bs.database_name,
    MAX(bs.backup_finish_date) AS LastBackupDate
FROM
    msdb.dbo.backupset bs
WHERE
    bs.type = 'D' -- D = Database, L = Log
GROUP BY
    bs.database_name
        

3. Backup History for a Specific Database

Replace ‘YourDatabaseName’ with the name of the database you’re interested in:

SELECT
    bs.backup_start_date,
    bs.backup_finish_date,
    bs.backup_size,
    bs.type,
    DATEDIFF(MINUTE, bs.backup_start_date, bs.backup_finish_date) AS DurationMinutes,
    bmf.physical_device_name
FROM
    msdb.dbo.backupset bs
JOIN
    msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE
    bs.database_name = 'YourDatabaseName'
ORDER BY
    bs.backup_start_date DESC
        

4. Checking for Missing Backups

This query helps identify databases that haven’t been backed up within a specific number of days (e.g., 7 days).

DECLARE @DaysWithoutBackup INT = 7
SELECT
    name AS DatabaseName,
    MAX(bs.backup_finish_date) AS LastBackupDate
FROM
    sys.databases d
LEFT JOIN
    msdb.dbo.backupset bs ON d.name = bs.database_name AND bs.type = 'D'
GROUP BY
    name
HAVING
    MAX(bs.backup_finish_date) IS NULL OR MAX(bs.backup_finish_date) < GETDATE() - @DaysWithoutBackup
        

5. Size and Growth Trend of Backups Over Time

This script will show the growth trend of database backups, which is useful for capacity planning.

SELECT CONVERT(VARCHAR(10), backup_start_date, 120) AS [Date],
    database_name,
    TYPE = CASE type 
             WHEN 'D' THEN 'Full'
             WHEN 'I' THEN 'Differential' 
             WHEN 'L' THEN 'Log' 
           END,
    SUM(backup_size/1024/1024) AS TotalSizeMB
FROM msdb.dbo.backupset
WHERE backup_start_date > GETDATE() - 90
GROUP BY
    CONVERT(VARCHAR(10), backup_start_date, 120),
    database_name,
    type
ORDER BY
    [Date], database_name, type
        

6. Detailed Log Backup Information

This script provides a detailed view of transaction log backups, crucial for databases in full recovery mode.

SELECT
    bs.database_name,
    bs.backup_start_date,
    bs.backup_finish_date,
    bs.backup_size/1024/1024 AS BackupSizeMB,
    bmf.physical_device_name
FROM
    msdb.dbo.backupset bs
JOIN
    msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE
    bs.type = 'L' -- Transaction log backups
ORDER BY
    bs.backup_start_date DESC
        

7. Oldest and Most Recent Backup of Each Database

This script helps identify the range of backups you have for each database.

SELECT
    database_name,
    MIN(backup_start_date) AS OldestBackupDate,
    MAX(backup_finish_date) AS NewestBackupDate
FROM
    msdb.dbo.backupset
GROUP BY
    database_name
        

8. Duration of Each Backup

Understanding how long each backup takes can be important for scheduling and performance tuning.

SELECT
    database_name,
    backup_start_date,
    backup_finish_date,
    DATEDIFF(MINUTE, backup_start_date, backup_finish_date) AS DurationInMinutes
FROM
    msdb.dbo.backupset
WHERE
    type = 'D' -- Change to 'I' for differential, 'L' for log
ORDER BY
    database_name, backup_start_date
        

9. Backup Failures

This script identifies any backups that have failed, which is critical for maintaining the reliability of your backup strategy.

SELECT
    bs.database_name,
    bs.backup_start_date,
    bs.backup_finish_date,
    bs.is_copy_only,
    bs.type,
    bmf.physical_device_name,
    bm.backup_start_date AS MirrorBackupStartDate,
    bm.backup_finish_date AS MirrorBackupFinishDate
FROM
    msdb.dbo.backupset bs
LEFT JOIN
    msdb.dbo.backupset bm ON bs.backup_set_id = bm.backup_set_id AND bm.is_copy_only = 1
JOIN
    msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE
    bs.backup_finish_date IS NULL OR bs.backup_finish_date < bs.backup_start_date
ORDER BY
    bs.backup_start_date DESC
        

[Author]

Posts