Preskočiť na obsah
Skript

Posledné zálohy každej databázy

Kedy prebehla posledná FULL / DIFF / LOG záloha – a ktoré DB sú bez zálohy.

#audit #backup #msdb
posledne-zalohy-kazdej-databazy.sql 27 riadkov · T-SQL
Parametre skriptu doplň a kopíruj hotový skript
DECLARE @AlertDays int = {{AlertDays:1}};

SELECT  x.database_name,
        x.recovery_model_desc,
        x.last_full,
        x.last_diff,
        x.last_log,
        CASE WHEN x.needs_backup = 1 THEN N'⚠ treba zálohovať' ELSE N'OK' END AS status
FROM (
    SELECT  d.name                                   AS database_name,
            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,
            -- bez FULL / DIFF zálohy alebo posledná staršia ako @AlertDays dní
            CASE WHEN MAX(CASE WHEN b.type IN ('D','I') THEN b.backup_finish_date END) IS NULL
                   OR MAX(CASE WHEN b.type IN ('D','I') THEN b.backup_finish_date END)
                      < DATEADD(DAY, -@AlertDays, SYSDATETIME())
                 THEN 1 ELSE 0 END               AS needs_backup
    FROM    sys.databases AS d
    LEFT JOIN msdb.dbo.backupset AS b
           ON b.database_name = d.name
          AND b.is_copy_only = 0
    WHERE   d.name <> 'tempdb'
    GROUP BY d.name, d.recovery_model_desc
) AS x
ORDER BY x.needs_backup DESC, x.database_name;