When was Database Last Backed Up in SQL Server? – Interview Question of the Week #107

Question: When was a database last backed up in SQL Server?

Someone asked me this during a performance tuning training session. It was outside the session’s main topic, but I enjoy the questions that tell us whether a database can survive a bad day. SQL Server keeps backup history in msdb.

The original approach lists recent full, differential, and log backups with their finish times and durations. This version retains those useful columns without multiplying a backup when it has more than one media-family row:

SELECT TOP (100)
       bs.database_name AS DatabaseName,
       CASE bs.type WHEN 'D' THEN 'Full'
                    WHEN 'I' THEN 'Differential'
                    WHEN 'L' THEN 'Transaction Log' END AS BackupType,
       bs.backup_start_date AS StartedAt,
       bs.backup_finish_date AS FinishedAt,
       DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date)
           AS DurationSeconds,
       bs.backup_size / 1048576.0 AS BackupSizeMB,
       bs.server_name AS ServerName,
       bs.recovery_model AS RecoveryModel
FROM msdb.dbo.backupset AS bs
WHERE bs.type IN ('D', 'I', 'L')
ORDER BY bs.backup_finish_date DESC, bs.backup_set_id DESC;

Here is the earlier query that produced the historical result below. It includes the media device path and displays decimal megabytes (one million bytes), while the current query uses 1,048,576 bytes per displayed MB; the direct join can return more than one row for a backup with multiple media families, which is why the current query above reads backupset alone:

SELECT TOP 100 s.database_name,
       CASE s.[type] WHEN 'D' THEN 'Full'
                     WHEN 'I' THEN 'Differential'
                     WHEN 'L' THEN 'Transaction Log' END AS BackupType,
       m.physical_device_name,
       CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14))
           + ' MB' AS bkSize,
       CAST(DATEDIFF(SECOND, s.backup_start_date,
                     s.backup_finish_date) AS VARCHAR(4))
           + ' Seconds' AS TimeTaken,
       s.backup_start_date, s.server_name, s.recovery_model
FROM msdb.dbo.backupset AS s
JOIN msdb.dbo.backupmediafamily AS m
  ON s.media_set_id = m.media_set_id
ORDER BY s.backup_start_date DESC, s.backup_finish_date;

The saved SSMS result shows its columns and rows. The device path can be NULL for some backup arrangements. Your database names and dates will differ:

That answers “what ran recently?” To get the latest full, differential, and log backup for each database, rank the backup sets within each database and type:

WITH Latest AS
(
    SELECT bs.database_name, bs.type, bs.backup_finish_date,
           bs.is_copy_only,
           ROW_NUMBER() OVER
           (
               PARTITION BY bs.database_name, bs.type
               ORDER BY bs.backup_finish_date DESC, bs.backup_set_id DESC
           ) AS rn
    FROM msdb.dbo.backupset AS bs
    WHERE bs.type IN ('D', 'I', 'L')
)
SELECT database_name AS DatabaseName,
       CASE type WHEN 'D' THEN 'Full'
                 WHEN 'I' THEN 'Differential'
                 WHEN 'L' THEN 'Transaction Log' END AS BackupType,
       backup_finish_date AS LastFinishedAt,
       is_copy_only AS IsCopyOnly
FROM Latest
WHERE rn = 1
ORDER BY DatabaseName, BackupType;

Take care with what the result proves. It reports history recorded on this instance, including copy-only backups. It does not prove that a backup file is still present or restorable, and history can include VSS snapshot entries. For recovery planning, identify the correct backup chain and perform restore tests. The finish time alone is not a recovery guarantee.

For the bigger picture, my backup timeline walkthrough shows how full, differential, and log backups fit together.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.