DBAtlas · Database operations atlas

Backup history from msdb and missing backup check

SQL ServerBackup & Log

Command

-- Last FULL / DIFF / LOG per database (never backed up first):
SELECT d.name AS db,
       d.recovery_model_desc,
       MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS last_full,
       MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS last_diff,
       MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS last_log,
       DATEDIFF(MINUTE, MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END), GETDATE()) AS log_age_min
FROM   sys.databases d
  LEFT JOIN msdb.dbo.backupset b
         ON b.database_name = d.name AND b.is_copy_only = 0
WHERE  d.name <> N'tempdb'
GROUP BY d.name, d.recovery_model_desc
ORDER BY last_full;

-- Chain for one DB over the last 7 days (LSN, size, file):
SELECT b.backup_start_date, b.backup_finish_date, b.type, b.is_copy_only,
       b.first_lsn, b.last_lsn, b.database_backup_lsn,
       CAST(b.backup_size / 1048576.0 AS DECIMAL(12,1))            AS size_mb,
       CAST(b.compressed_backup_size / 1048576.0 AS DECIMAL(12,1)) AS compressed_mb,
       b.has_backup_checksums, mf.physical_device_name
FROM   msdb.dbo.backupset b
  JOIN msdb.dbo.backupmediafamily mf ON mf.media_set_id = b.media_set_id
WHERE  b.database_name = N'Musteri'
  AND  b.backup_start_date >= DATEADD(DAY, -7, GETDATE())
ORDER BY b.backup_start_date;

What it does

Lists the last FULL/DIFF/LOG backup time and log backup age for every database; for one database, the backup chain with LSN sequence, compressed size, checksum flag and file path.

When to use it

In the morning check, to find which files a restore needs, to answer "when was the last backup" in an audit, or to see whether log backups are running during a log-full alert.

Caution

msdb only knows backups taken by this instance; backups taken on another server or on another AG replica are not visible (check every replica in an AG). A log backup's first_lsn must equal the previous one's last_lsn; a gap means the chain is broken. In FULL recovery a NULL last_log means the log is never backed up and will keep growing. Read-only query; safe.

Open in DBAtlas →

Related