История резервного копирования из msdb и проверка отсутствующих копий

Команда

-- 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;

Что делает

Выводит для каждой базы время последней FULL/DIFF/LOG-копии и возраст копии журнала; для одной базы — цепочку копий с последовательностью LSN, сжатым размером, флагом контрольной суммы и путём к файлу.

Когда использовать

При утренней проверке, чтобы найти файлы, нужные для восстановления, ответить на аудите "когда была последняя копия" или проверить, идут ли копии журнала при оповещении о заполнении журнала.

Внимание

msdb знает только о копиях, сделанных этим экземпляром; копии с другого сервера или другой реплики AG не видны (в AG проверяйте каждую реплику). first_lsn копии журнала должен совпадать с last_lsn предыдущей; разрыв означает, что цепочка нарушена. В модели FULL значение NULL в last_log означает, что журнал никогда не копируется и будет расти. Запрос только на чтение; безопасно.