Generátor RESTORE príkazov z histórie záloh

/* DeadlockCafe | Generátor RESTORE príkazov z histórie záloh
   Účel:  Z msdb poskladá poradie RESTORE príkazov: posledný FULL, DIFF a reťaz LOG záloh až k zvolenému času (STOPAT). Kontroluje diery v reťazi logov. Príkazy iba vypíše, nespúšťa.
   Režim: READ ONLY (príkazy len vypíše) | SQL Server 2016 – 2022, on-premises
*/
SET NOCOUNT ON;

DECLARE @DatabaseName sysname      = N'{{DatabaseName|Databáza, ktorej zálohy sa hľadajú v msdb}}';
DECLARE @StopAtText   nvarchar(40) = N'{{StopAt:teraz|Cieľový čas yyyy-mm-dd hh:mi:ss alebo teraz}}';
DECLARE @TargetName   sysname      = N'{{TargetName:*|Meno obnovenej databázy, * = pôvodné meno}}';

DECLARE @StopAt datetime = NULL;
IF @StopAtText <> N'teraz'
BEGIN
    SET @StopAt = TRY_CONVERT(datetime, @StopAtText, 120);
    IF @StopAt IS NULL
    BEGIN
        RAISERROR(N'StopAt: zadaj čas v tvare yyyy-mm-dd hh:mi:ss alebo slovo teraz.', 16, 1);
        RETURN;
    END;
END;
IF @TargetName = N'*' SET @TargetName = @DatabaseName;
DECLARE @Limit datetime = ISNULL(@StopAt, GETDATE());

-- 1) posledný FULL (bez COPY_ONLY) dokončený pred cieľovým časom
DECLARE @full_id int, @full_ckpt numeric(25,0), @full_last numeric(25,0);
SELECT TOP (1) @full_id = backup_set_id, @full_ckpt = checkpoint_lsn, @full_last = last_lsn
FROM msdb.dbo.backupset
WHERE database_name = @DatabaseName AND type = 'D' AND is_copy_only = 0
  AND backup_finish_date <= @Limit
ORDER BY backup_finish_date DESC, backup_set_id DESC;

IF @full_id IS NULL
BEGIN
    RAISERROR(N'V msdb nie je FULL záloha (bez COPY_ONLY) databázy %s dokončená pred cieľovým časom.', 16, 1, @DatabaseName);
    RETURN;
END;

-- 2) posledný DIFF postavený na tomto FULL (differential_base_lsn = checkpoint_lsn FULL zálohy)
DECLARE @diff_id int, @diff_last numeric(25,0);
SELECT TOP (1) @diff_id = backup_set_id, @diff_last = last_lsn
FROM msdb.dbo.backupset
WHERE database_name = @DatabaseName AND type = 'I'
  AND differential_base_lsn = @full_ckpt
  AND backup_finish_date <= @Limit
ORDER BY backup_finish_date DESC, backup_set_id DESC;

DECLARE @base_last numeric(25,0) = ISNULL(@diff_last, @full_last);

IF OBJECT_ID(N'tempdb..#plan') IS NOT NULL DROP TABLE #plan;
CREATE TABLE #plan
(
    step          int IDENTITY(1,1) PRIMARY KEY,
    backup_set_id int           NULL,
    backup_type   varchar(12)   NOT NULL,
    media_set_id  int           NULL,
    first_lsn     numeric(25,0) NULL,
    last_lsn      numeric(25,0) NULL,
    finished_at   datetime      NULL,
    note          nvarchar(400) NULL,
    command       nvarchar(max) NULL
);

INSERT INTO #plan (backup_set_id, backup_type, media_set_id, first_lsn, last_lsn, finished_at)
SELECT backup_set_id, CASE type WHEN 'D' THEN 'FULL' ELSE 'DIFF' END, media_set_id, first_lsn, last_lsn, backup_finish_date
FROM msdb.dbo.backupset
WHERE backup_set_id IN (@full_id, @diff_id)
ORDER BY backup_set_id;

-- 3) LOG zálohy za FULL / DIFF; so StopAt len po prvú, ktorá cieľový čas pokrýva
;WITH logs AS
(
    SELECT backup_set_id, media_set_id, first_lsn, last_lsn, backup_finish_date,
           ROW_NUMBER() OVER (ORDER BY first_lsn, backup_set_id) AS rn
    FROM msdb.dbo.backupset
    WHERE database_name = @DatabaseName AND type = 'L' AND is_copy_only = 0
      AND last_lsn > @base_last
)
INSERT INTO #plan (backup_set_id, backup_type, media_set_id, first_lsn, last_lsn, finished_at)
SELECT l.backup_set_id, 'LOG', l.media_set_id, l.first_lsn, l.last_lsn, l.backup_finish_date
FROM logs AS l
WHERE @StopAt IS NULL
   OR l.rn <= ISNULL((SELECT MIN(x.rn) FROM logs AS x WHERE x.backup_finish_date >= @StopAt), 2147483647)
ORDER BY l.rn;

-- 4) nadväznosť LSN: first_lsn každej LOG zálohy musí byť <= last_lsn predchádzajúcej
;WITH c AS
(
    SELECT step, backup_type, first_lsn,
           LAG(last_lsn)    OVER (ORDER BY step) AS prev_last_lsn,
           LAG(backup_type) OVER (ORDER BY step) AS prev_type
    FROM #plan
)
UPDATE p
SET note = CASE
               WHEN c.first_lsn > c.prev_last_lsn
                   THEN N'DIERA v reťazi: chýba LOG záloha s LSN ' + CONVERT(nvarchar(30), c.prev_last_lsn)
                        + N' až ' + CONVERT(nvarchar(30), c.first_lsn) + N' – za týmto bodom sa obnoviť nedá'
               WHEN c.prev_type = 'LOG' AND c.first_lsn < c.prev_last_lsn
                   THEN N'Prekryv s predchádzajúcou LOG zálohou (duplicitný záznam v msdb?) – over ručne'
           END
FROM #plan AS p
JOIN c ON c.step = p.step
WHERE c.backup_type = 'LOG';

IF @StopAt IS NOT NULL AND NOT EXISTS (SELECT 1 FROM #plan WHERE backup_type = 'LOG' AND finished_at >= @StopAt)
    INSERT INTO #plan (backup_type, note)
    VALUES ('VAROVANIE', N'Žiadna LOG záloha v msdb nepokrýva StopAt – treba novší log backup alebo tail-log backup.');

-- 5) príkazy (viac súborov na zálohu = viac DISK; pri inom mene cieľa MOVE so suffixom)
DECLARE @stop nvarchar(60) = CASE WHEN @StopAt IS NULL THEN N''
                                  ELSE N', STOPAT = N''' + CONVERT(nvarchar(19), @StopAt, 126) + N'''' END;

UPDATE p
SET command = CASE p.backup_type WHEN 'LOG' THEN N'RESTORE LOG ' ELSE N'RESTORE DATABASE ' END
            + QUOTENAME(@TargetName) + N' FROM ' + dev.list
            + N' WITH NORECOVERY'
            + CASE WHEN p.backup_type = 'FULL' THEN N', STATS = 10' + mv.list ELSE N'' END
            + CASE WHEN p.backup_type = 'LOG' THEN @stop ELSE N'' END
            + N';'
FROM #plan AS p
CROSS APPLY (SELECT STUFF((SELECT N', ' + CASE WHEN mf.device_type = 9 THEN N'URL' ELSE N'DISK' END
                                 + N' = N''' + REPLACE(mf.physical_device_name, N'''', N'''''') + N''''
                          FROM msdb.dbo.backupmediafamily AS mf
                          WHERE mf.media_set_id = p.media_set_id AND mf.mirror = 0
                          ORDER BY mf.family_sequence_number
                          FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 2, N'') AS list) AS dev
CROSS APPLY (SELECT CASE WHEN @TargetName = @DatabaseName THEN N''
                         ELSE ISNULL((SELECT NCHAR(13) + NCHAR(10) + N'    , MOVE N''' + REPLACE(bf.logical_name, N'''', N'''''')
                                             + N''' TO N''' + REPLACE(
                                                 CASE WHEN CHARINDEX(N'.', REVERSE(bf.physical_name)) > 0
                                                      THEN LEFT(bf.physical_name, LEN(bf.physical_name) - CHARINDEX(N'.', REVERSE(bf.physical_name)))
                                                           + N'_' + @TargetName
                                                           + RIGHT(bf.physical_name, CHARINDEX(N'.', REVERSE(bf.physical_name)))
                                                      ELSE bf.physical_name + N'_' + @TargetName END, N'''', N'''''') + N''''
                                      FROM msdb.dbo.backupfile AS bf
                                      WHERE bf.backup_set_id = p.backup_set_id AND bf.is_present = 1
                                      ORDER BY bf.file_type, bf.file_number
                                      FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), N'')
                    END AS list) AS mv
WHERE p.backup_type IN ('FULL', 'DIFF', 'LOG');

INSERT INTO #plan (backup_type, command)
VALUES ('RECOVERY', N'RESTORE DATABASE ' + QUOTENAME(@TargetName) + N' WITH RECOVERY;');

-- 6) výstup
SELECT @DatabaseName AS source_database,
       @TargetName   AS target_database,
       @StopAt       AS stop_at,
       (SELECT COUNT(*) FROM #plan WHERE backup_type = 'LOG')    AS log_backups,
       (SELECT COUNT(*) FROM #plan WHERE note LIKE N'DIERA%')    AS chain_gaps,
       CASE WHEN DB_ID(@TargetName) IS NOT NULL
            THEN N'Cieľová databáza existuje – RESTORE by ju prepísal (treba REPLACE alebo tail-log backup). Zváž obnovu pod iným menom.'
            ELSE N'' END AS warning;

SELECT step, backup_type, finished_at, first_lsn, last_lsn, note, command
FROM #plan
ORDER BY step;