بکاپ خودکار SQL Server؛ زمان‌بندی SQL Server Agent و آزمون Restore

-- SQL Server 2019/2022 on Windows, Standard/Enterprise/Developer.
-- DBA installation. No xp_cmdshell or undocumented folder procedures.
USE master;
GO
IF DB_ID(N'DBAOperations') IS NULL CREATE DATABASE [DBAOperations];
GO
USE [DBAOperations];
GO
IF OBJECT_ID(N'dbo.BackupWhitelist', N'U') IS NULL
CREATE TABLE dbo.BackupWhitelist
(
    DatabaseName sysname NOT NULL PRIMARY KEY,
    Enabled bit NOT NULL CONSTRAINT DF_BackupWhitelist_Enabled DEFAULT (1)
);
IF OBJECT_ID(N'dbo.BackupAudit', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.BackupAudit
    (
        AuditId bigint IDENTITY PRIMARY KEY,
        RunId uniqueidentifier NOT NULL,
        DatabaseName sysname NOT NULL,
        BackupType varchar(4) NOT NULL,
        StartedUtc datetime2(3) NOT NULL,
        FinishedUtc datetime2(3) NULL,
        BackupRoot nvarchar(200) NOT NULL,
        BackupFile nvarchar(260) NULL,
        Status varchar(12) NOT NULL,
        Verified bit NOT NULL CONSTRAINT DF_BackupAudit_Verified DEFAULT (0),
        ErrorNumber int NULL,
        ErrorMessage nvarchar(4000) NULL
    );
    CREATE INDEX IX_BackupAudit_Retention
        ON dbo.BackupAudit(DatabaseName, BackupType, Status, FinishedUtc);
END;
INSERT dbo.BackupWhitelist(DatabaseName)
SELECT v.DatabaseName FROM (VALUES
    (N'DB-Confluence'), (N'DB-Jira'), (N'DWConfiguration'), (N'DWDiagnostics'),
    (N'DWQueue'), (N'ORACLE_VIEW'), (N'RAYDANA_DB'), (N'STLA')
) v(DatabaseName)
WHERE NOT EXISTS
    (SELECT 1 FROM dbo.BackupWhitelist w WHERE w.DatabaseName = v.DatabaseName);
GO
CREATE OR ALTER PROCEDURE dbo.usp_BackupWhitelist
    @BackupType varchar(4),
    @BackupRoot nvarchar(200) = N'D:\SQLBackup',
    @Verify bit = 1,
    @EncryptionCertificate sysname = NULL
AS
BEGIN
    SET NOCOUNT ON;
    IF @@TRANCOUNT > 0 THROW 51000, 'Run backups outside a user transaction.', 1;
    SET @BackupType = UPPER(@BackupType);
    IF @BackupType IS NULL OR @BackupType NOT IN ('FULL','DIFF','LOG')
        THROW 51001, 'BackupType must be FULL, DIFF or LOG.', 1;
    IF @Verify IS NULL THROW 51002, 'Verify must be 0 or 1.', 1;
    IF @BackupRoot IS NULL OR LEN(@BackupRoot) > 100
       OR @BackupRoot NOT LIKE N'[A-Za-z]:\%'
       OR CHARINDEX(N'..', @BackupRoot) > 0 OR CHARINDEX(N'/', @BackupRoot) > 0
        THROW 51003, 'Use a local absolute backup root, up to 100 characters.', 1;
    WHILE RIGHT(@BackupRoot, 1) = N'\'
        SET @BackupRoot = LEFT(@BackupRoot, LEN(@BackupRoot)-1);
    IF LEN(@BackupRoot) < 4 THROW 51004, 'Do not use a drive root.', 1;
    IF NOT EXISTS (SELECT 1 FROM dbo.BackupWhitelist WHERE Enabled = 1)
        THROW 51005, 'Whitelist is empty; no backups were taken.', 1;
    IF @EncryptionCertificate IS NOT NULL AND NOT EXISTS
       (SELECT 1 FROM master.sys.certificates WHERE name = @EncryptionCertificate)
        THROW 51006, 'Encryption certificate is missing in master.', 1;

    DECLARE @RunId uniqueidentifier = NEWID(), @Database sysname,
            @AuditId bigint, @File nvarchar(260), @Sql nvarchar(max),
            @Stamp varchar(20), @Recovery nvarchar(60), @DatabaseId int,
            @State nvarchar(60), @SourceId int, @Failures int = 0,
            @LockResult int, @LockResource nvarchar(255), @Locked bit;
    DECLARE databases CURSOR LOCAL FAST_FORWARD FOR
        SELECT DatabaseName FROM dbo.BackupWhitelist WHERE Enabled = 1
        ORDER BY DatabaseName;
    OPEN databases;
    FETCH NEXT FROM databases INTO @Database;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @File = NULL;
        SET @Locked = 0;
        INSERT dbo.BackupAudit(RunId,DatabaseName,BackupType,StartedUtc,BackupRoot,Status)
        VALUES(@RunId,@Database,@BackupType,SYSUTCDATETIME(),@BackupRoot,'RUNNING');
        SET @AuditId = SCOPE_IDENTITY();
        BEGIN TRY
            IF @Database COLLATE Latin1_General_100_BIN2 LIKE N'%[^A-Za-z0-9_-]%'
               OR UPPER(@Database) IN
                  (N'CON',N'PRN',N'AUX',N'NUL',N'COM1',N'COM2',N'COM3',N'COM4',
                   N'COM5',N'COM6',N'COM7',N'COM8',N'COM9',N'LPT1',N'LPT2',N'LPT3',
                   N'LPT4',N'LPT5',N'LPT6',N'LPT7',N'LPT8',N'LPT9')
                THROW 51007, 'Whitelist name is not safe for this folder layout.', 1;
            SELECT @DatabaseId = NULL, @Recovery = NULL, @State = NULL, @SourceId = NULL;
            SELECT @DatabaseId=database_id, @Recovery=recovery_model_desc,
                   @State=state_desc, @SourceId=source_database_id
            FROM sys.databases WHERE name = @Database;
            IF @DatabaseId IS NULL THROW 51008, 'Whitelist database does not exist.', 1;
            IF @DatabaseId <= 4 THROW 51009, 'System databases are excluded.', 1;
            IF @State <> N'ONLINE' OR @SourceId IS NOT NULL
                THROW 51010, 'Database must be online and not a snapshot.', 1;
            IF @BackupType = 'LOG' AND @Recovery = N'SIMPLE'
                THROW 51011, 'SIMPLE recovery does not support log backups.', 1;
            IF EXISTS (SELECT 1 FROM sys.databases
                       WHERE database_id=@DatabaseId AND group_database_id IS NOT NULL)
                THROW 51012, 'Availability Group needs a replica-aware backup policy.', 1;
            -- FULL/DIFF serialize; LOG may run concurrently with a data backup.
            SET @LockResource = N'MeetAJ.Backup.' + @Database +
                CASE WHEN @BackupType='LOG' THEN N'.LOG' ELSE N'.DATA' END;
            EXEC @LockResult = sys.sp_getapplock
                @Resource=@LockResource, @LockMode='Exclusive',
                @LockOwner='Session', @LockTimeout=60000;
            IF @LockResult < 0 THROW 51013, 'Could not acquire the database backup lock.', 1;
            SET @Locked = 1;
            SET @Stamp = CONVERT(char(8),GETUTCDATE(),112) + '_' +
                         REPLACE(CONVERT(char(12),GETUTCDATE(),114),':','');
            IF LEN(@BackupRoot)+LEN(@Database)*2+LEN(@BackupType)+65 > 259
                THROW 51014, 'Backup path would exceed 259 characters.', 1;
            SET @File = @BackupRoot + N'\' + @Database + N'\' + @BackupType +
                        N'\' + @Database + N'_' + @BackupType + N'_' + @Stamp + N'_' +
                        CONVERT(nvarchar(36),NEWID()) +
                        CASE WHEN @BackupType='LOG' THEN N'.trn' ELSE N'.bak' END;
            UPDATE dbo.BackupAudit SET BackupFile=@File WHERE AuditId=@AuditId;
            SET @Sql = CASE WHEN @BackupType='LOG' THEN N'BACKUP LOG ' ELSE N'BACKUP DATABASE ' END
                + QUOTENAME(@Database) + N' TO DISK=@Path WITH '
                + CASE WHEN @BackupType='DIFF' THEN N'DIFFERENTIAL, ' ELSE N'' END
                + N'COMPRESSION, CHECKSUM, STOP_ON_ERROR, STATS=10'
                + CASE WHEN @EncryptionCertificate IS NULL THEN N'' ELSE
                    N', ENCRYPTION (ALGORITHM=AES_256, SERVER CERTIFICATE='
                    + QUOTENAME(@EncryptionCertificate) + N')' END + N';';
            EXEC sys.sp_executesql @Sql, N'@Path nvarchar(260)', @Path=@File;
            IF @Verify=1
            BEGIN
                SET @Sql = N'RESTORE VERIFYONLY FROM DISK=@Path WITH CHECKSUM, STOP_ON_ERROR;';
                EXEC sys.sp_executesql @Sql, N'@Path nvarchar(260)', @Path=@File;
            END;
            UPDATE dbo.BackupAudit
                SET Status='SUCCEEDED',FinishedUtc=SYSUTCDATETIME(),Verified=@Verify
                WHERE AuditId=@AuditId;
        END TRY
        BEGIN CATCH
            SET @Failures += 1;
            UPDATE dbo.BackupAudit SET Status='FAILED',FinishedUtc=SYSUTCDATETIME(),
                ErrorNumber=ERROR_NUMBER(),ErrorMessage=ERROR_MESSAGE()
                WHERE AuditId=@AuditId;
        END CATCH;
        IF @Locked=1
            EXEC sys.sp_releaseapplock @Resource=@LockResource, @LockOwner='Session';
        FETCH NEXT FROM databases INTO @Database;
    END;
    CLOSE databases;
    DEALLOCATE databases;
    IF @Failures > 0
        THROW 51015, 'One or more backups failed. Check DBAOperations.dbo.BackupAudit.', 1;
END;
GO

# Windows PowerShell 5.1. Provision folders without enabling xp_cmdshell.
param([string]$ServerInstance = 'localhost', [string]$BackupRoot = 'D:\SQLBackup')
$ErrorActionPreference = 'Stop'
$rootPath = [IO.Path]::GetFullPath($BackupRoot).TrimEnd('\')
if ($rootPath -notmatch '^[A-Za-z]:\\.+' -or $rootPath.Length -gt 100) {
    throw 'Use a local absolute backup folder, not a drive root.'
}
function Assert-NoReparsePath([string]$Path) {
    $currentPath = [IO.Path]::GetFullPath($Path)
    while ($currentPath) {
        if (Test-Path -LiteralPath $currentPath) {
            $item = Get-Item -LiteralPath $currentPath -Force
            if ($item.Attributes -band [IO.FileAttributes]::ReparsePoint) {
                throw "Reparse points are not permitted: $currentPath"
            }
        }
        $parent = [IO.Directory]::GetParent($currentPath)
        if ($null -eq $parent) { break }
        $currentPath = $parent.FullName
    }
}
Assert-NoReparsePath $rootPath
$builder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
$builder.DataSource = $ServerInstance
$builder.InitialCatalog = 'DBAOperations'
$builder.IntegratedSecurity = $true
$builder.Encrypt = $true
$builder.TrustServerCertificate = $false
$connection = New-Object System.Data.SqlClient.SqlConnection $builder.ConnectionString
try {
    $connection.Open()
    $command = $connection.CreateCommand()
    $command.CommandText = 'SELECT DatabaseName FROM dbo.BackupWhitelist WHERE Enabled=1;'
    $reader = $command.ExecuteReader()
    $databases = @()
    while ($reader.Read()) { $databases += $reader.GetString(0) }
    $reader.Close()
    if ($databases.Count -eq 0) { throw 'Whitelist is empty.' }
    foreach ($database in $databases) {
        if ($database -notmatch '^[A-Za-z0-9_-]+$' -or
            $database -match '^(CON|PRN|AUX|NUL|COM[1-9]|LPT[1-9])$' -or
            $database -in @('master','model','msdb','tempdb')) {
            throw "Unsupported database folder name: $database"
        }
        foreach ($kind in @('FULL','DIFF','LOG')) {
            $directory = Join-Path (Join-Path $rootPath $database) $kind
            Assert-NoReparsePath $directory
            [IO.Directory]::CreateDirectory($directory) | Out-Null
        }
    }
} finally { $connection.Dispose() }

-- Provision SQLBackupFiles CmdExec proxy first; see article prerequisites.
-- Save 02-prepare-folders.ps1 as C:\DBA\Prepare-SqlBackupFolders.ps1.
USE msdb;
GO
DECLARE @Proxy sysname=N'SQLBackupFiles', @Operator sysname=NULL,
        @Owner sysname=SUSER_SNAME(), @Notify int,
        @Type varchar(4), @Job sysname, @Schedule sysname, @Command nvarchar(max),
        @Start int, @End int, @SubType int, @SubInterval int;
IF NOT EXISTS (SELECT 1 FROM dbo.sysproxies WHERE name=@Proxy AND enabled=1)
    THROW 51100, 'Create the enabled SQLBackupFiles CmdExec proxy first.', 1;
IF @Operator IS NOT NULL AND NOT EXISTS
   (SELECT 1 FROM dbo.sysoperators WHERE name=@Operator AND enabled=1)
    THROW 51101, 'Configured notification operator does not exist.', 1;
IF EXISTS (SELECT 1 FROM dbo.sysjobs
           WHERE name IN (N'MeetAJ - SQL FULL',N'MeetAJ - SQL DIFF',N'MeetAJ - SQL LOG'))
    THROW 51102, 'Jobs already exist. Review/update them; do not overwrite.', 1;
SET @Notify=CASE WHEN @Operator IS NULL THEN 0 ELSE 2 END;
BEGIN TRY
    BEGIN TRANSACTION;
    DECLARE types CURSOR LOCAL FAST_FORWARD FOR
        SELECT Kind FROM (VALUES ('FULL'),('DIFF'),('LOG')) t(Kind);
    OPEN types;
    FETCH NEXT FROM types INTO @Type;
    WHILE @@FETCH_STATUS=0
    BEGIN
        SET @Job=N'MeetAJ - SQL '+@Type;
        SET @Schedule=@Job+N' schedule';
        -- Jobs stay disabled until a manual FULL/DIFF/LOG test succeeds.
        EXEC dbo.sp_add_job @job_name=@Job, @enabled=0,
            @owner_login_name=@Owner, @notify_level_email=@Notify,
            @notify_email_operator_name=@Operator;
        EXEC dbo.sp_add_jobstep @job_name=@Job, @step_id=1,
            @step_name=N'Prepare whitelist folders', @subsystem=N'CmdExec',
            @proxy_name=@Proxy,
            @command=N'powershell.exe -NoProfile -NonInteractive -File "C:\DBA\Prepare-SqlBackupFolders.ps1" -ServerInstance "localhost" -BackupRoot "D:\SQLBackup"',
            @on_success_action=3, @on_fail_action=2, @retry_attempts=0;
        SET @Command=N'EXEC dbo.usp_BackupWhitelist @BackupType='''+@Type+
            N''', @BackupRoot=N''D:\SQLBackup'', @Verify=1;';
        EXEC dbo.sp_add_jobstep @job_name=@Job, @step_id=2,
            @step_name=N'Backup and verify', @subsystem=N'TSQL',
            @database_name=N'DBAOperations', @command=@Command,
            @on_success_action=1, @on_fail_action=2, @retry_attempts=0;
        SET @Start=CASE @Type WHEN 'FULL' THEN 010000 WHEN 'DIFF' THEN 070000 ELSE 000000 END;
        SET @End=CASE WHEN @Type='DIFF' THEN 190000 ELSE 235959 END;
        SET @SubType=CASE @Type WHEN 'FULL' THEN 1 WHEN 'DIFF' THEN 8 ELSE 4 END;
        SET @SubInterval=CASE @Type WHEN 'FULL' THEN 1 WHEN 'DIFF' THEN 6 ELSE 15 END;
        EXEC dbo.sp_add_jobschedule @job_name=@Job, @name=@Schedule,
            @enabled=1, @freq_type=4, @freq_interval=1,
            @freq_subday_type=@SubType, @freq_subday_interval=@SubInterval,
            @active_start_time=@Start, @active_end_time=@End;
        EXEC dbo.sp_add_jobserver @job_name=@Job, @server_name=N'(LOCAL)';
        FETCH NEXT FROM types INTO @Type;
    END;
    CLOSE types;
    DEALLOCATE types;
    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT>0 ROLLBACK;
    THROW;
END CATCH;
GO

# Windows PowerShell 5.1. Default is preview; -Delete authorizes actual cleanup.
# Retain the verified FULL before the 30-day boundary, plus ALL backups since it.
[CmdletBinding(SupportsShouldProcess=$true)]
param(
    [string]$ServerInstance = 'localhost',
    [string]$BackupRoot = 'D:\SQLBackup',
    [ValidateRange(30,3650)][int]$RetentionDays = 30,
    [switch]$Delete
)
$ErrorActionPreference = 'Stop'
$rootPath = [IO.Path]::GetFullPath($BackupRoot).TrimEnd('\')
if ($rootPath -notmatch '^[A-Za-z]:\\.+' -or $rootPath.Length -gt 100) {
    throw 'Use a dedicated local backup root, not a drive root.'
}
function Assert-NoReparsePath([string]$Path) {
    $currentPath = [IO.Path]::GetFullPath($Path)
    while ($currentPath) {
        if (Test-Path -LiteralPath $currentPath) {
            $item = Get-Item -LiteralPath $currentPath -Force
            if ($item.Attributes -band [IO.FileAttributes]::ReparsePoint) {
                throw "Reparse point blocked: $currentPath"
            }
        }
        $parent = [IO.Directory]::GetParent($currentPath)
        if ($null -eq $parent) { break }
        $currentPath = $parent.FullName
    }
}
Assert-NoReparsePath $rootPath
$builder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
$builder.DataSource = $ServerInstance
$builder.InitialCatalog = 'DBAOperations'
$builder.IntegratedSecurity = $true
$builder.Encrypt = $true
$builder.TrustServerCertificate = $false
$connection = New-Object System.Data.SqlClient.SqlConnection $builder.ConnectionString
$candidates = New-Object System.Data.DataTable
try {
    $connection.Open()
    $command = $connection.CreateCommand()
    $command.CommandText = @'
DECLARE @Cutoff datetime2(3)=DATEADD(day,-@Days,SYSUTCDATETIME());
SELECT a.DatabaseName,a.BackupType,a.BackupFile,f.BackupFile AS AnchorFile
FROM dbo.BackupWhitelist w
CROSS APPLY (
    SELECT TOP (1) b.BackupFile,b.StartedUtc
    FROM dbo.BackupAudit b
    WHERE b.DatabaseName=w.DatabaseName AND b.BackupRoot=@Root
      AND b.BackupType='FULL' AND b.Status='SUCCEEDED' AND b.Verified=1
      AND b.FinishedUtc<=@Cutoff
    ORDER BY b.FinishedUtc DESC,b.AuditId DESC
) f
JOIN dbo.BackupAudit a ON a.DatabaseName=w.DatabaseName
WHERE w.Enabled=1 AND a.BackupRoot=@Root AND a.Status='SUCCEEDED'
  AND a.BackupType IN ('FULL','DIFF','LOG')
  AND a.FinishedUtc<f.StartedUtc AND a.FinishedUtc<@Cutoff
  AND a.BackupFile IS NOT NULL
ORDER BY a.DatabaseName,a.FinishedUtc;
'@
    $command.Parameters.Add('@Days',[Data.SqlDbType]::Int).Value=$RetentionDays
    $command.Parameters.Add('@Root',[Data.SqlDbType]::NVarChar,200).Value=$rootPath
    $reader=$command.ExecuteReader()
    $candidates.Load($reader)
    $reader.Close()
} finally { $connection.Dispose() }
$cutoffUtc=[DateTime]::UtcNow.AddDays(-$RetentionDays)
foreach ($row in $candidates.Rows) {
    $database=[string]$row.DatabaseName
    $kind=[string]$row.BackupType
    if ($database -notmatch '^[A-Za-z0-9_-]+$' -or
        $database -in @('master','model','msdb','tempdb')) {
        throw 'Invalid database name in cleanup manifest.'
    }
    $expectedDir=Join-Path (Join-Path $rootPath $database) $kind
    $filePath=[IO.Path]::GetFullPath([string]$row.BackupFile)
    $anchorPath=[IO.Path]::GetFullPath([string]$row.AnchorFile)
    $anchorDir=Join-Path (Join-Path $rootPath $database) 'FULL'
    if (-not [IO.Path]::GetDirectoryName($filePath).Equals($expectedDir,[StringComparison]::OrdinalIgnoreCase) -or
        -not [IO.Path]::GetDirectoryName($anchorPath).Equals($anchorDir,[StringComparison]::OrdinalIgnoreCase)) {
        throw "Manifest path is outside the expected backup directory: $filePath"
    }
    Assert-NoReparsePath $filePath
    Assert-NoReparsePath $anchorPath
    if (-not (Test-Path -LiteralPath $anchorPath -PathType Leaf)) {
        throw "Protected FULL backup is missing; cleanup stopped: $anchorPath"
    }
    $extension=if ($kind -eq 'LOG') { '.trn' } else { '.bak' }
    if ([IO.Path]::GetExtension($filePath) -ne $extension -or
        -not [IO.Path]::GetFileName($filePath).StartsWith($database+'_'+$kind+'_',[StringComparison]::OrdinalIgnoreCase)) {
        throw "Unexpected backup filename: $filePath"
    }
    if (-not (Test-Path -LiteralPath $filePath -PathType Leaf)) { continue }
    $file=Get-Item -LiteralPath $filePath -Force
    if ($file.LastWriteTimeUtc -ge $cutoffUtc) { continue }
    if (-not $Delete) { Write-Output "PREVIEW: $filePath"; continue }
    if ($PSCmdlet.ShouldProcess($filePath,'Delete expired SQL backup')) {
        Remove-Item -LiteralPath $filePath -ErrorAction Stop
        Write-Output "DELETED: $filePath"
    }
}

-- Save 04-cleanup.ps1 as C:\DBA\Cleanup-SqlBackups.ps1; preview before enabling.
USE msdb;
GO
IF EXISTS (SELECT 1 FROM dbo.sysjobs WHERE name=N'MeetAJ - SQL Cleanup')
    THROW 51200, 'Cleanup job exists; review it instead of overwriting.', 1;
IF NOT EXISTS (SELECT 1 FROM dbo.sysproxies WHERE name=N'SQLBackupFiles' AND enabled=1)
    THROW 51201, 'SQLBackupFiles proxy is missing.', 1;
BEGIN TRY
    BEGIN TRANSACTION;
    DECLARE @Owner sysname=SUSER_SNAME();
    EXEC dbo.sp_add_job @job_name=N'MeetAJ - SQL Cleanup',@enabled=0,@owner_login_name=@Owner;
    EXEC dbo.sp_add_jobstep @job_name=N'MeetAJ - SQL Cleanup',
        @step_name=N'Clean expired backups',@subsystem=N'CmdExec',@proxy_name=N'SQLBackupFiles',
        @command=N'powershell.exe -NoProfile -NonInteractive -File "C:\DBA\Cleanup-SqlBackups.ps1" -ServerInstance "localhost" -BackupRoot "D:\SQLBackup" -RetentionDays 30 -Delete',
        @on_success_action=1,@on_fail_action=2,@retry_attempts=0;
    EXEC dbo.sp_add_jobschedule @job_name=N'MeetAJ - SQL Cleanup',
        @name=N'MeetAJ - cleanup daily 05:00',@freq_type=4,@freq_interval=1,
        @active_start_time=050000;
    EXEC dbo.sp_add_jobserver @job_name=N'MeetAJ - SQL Cleanup',@server_name=N'(LOCAL)';
    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT>0 ROLLBACK;
    THROW;
END CATCH;
GO

-- Run on the source instance. msdb timestamps use server local time;
-- BackupAudit timestamps use UTC.
USE msdb;
GO
SELECT TOP (100) b.database_name,
    CASE b.type WHEN 'D' THEN 'FULL' WHEN 'I' THEN 'DIFF' WHEN 'L' THEN 'LOG' END AS BackupType,
    b.backup_start_date,b.backup_finish_date,b.is_copy_only,b.has_backup_checksums,
    CAST(b.backup_size/1048576.0 AS decimal(18,2)) AS OriginalMB,
    CAST(b.compressed_backup_size/1048576.0 AS decimal(18,2)) AS CompressedMB,
    m.physical_device_name,b.first_lsn,b.last_lsn,b.database_backup_lsn
FROM dbo.backupset b
JOIN dbo.backupmediafamily m ON m.media_set_id=b.media_set_id
JOIN DBAOperations.dbo.BackupWhitelist w ON w.DatabaseName=b.database_name AND w.Enabled=1
WHERE b.type IN ('D','I','L')
ORDER BY b.backup_finish_date DESC;
GO
-- OUTER APPLY keeps missing backups visible (NULL), instead of hiding the DB.
SELECT w.DatabaseName,d.recovery_model_desc,
       f.backup_finish_date AS LastFull,
       i.backup_finish_date AS LastDifferential,
       l.backup_finish_date AS LastLog,
       d.log_reuse_wait_desc
FROM DBAOperations.dbo.BackupWhitelist w
LEFT JOIN sys.databases d ON d.name=w.DatabaseName
OUTER APPLY (SELECT TOP (1) backup_finish_date FROM dbo.backupset
    WHERE database_name=w.DatabaseName AND type='D' AND is_copy_only=0
    ORDER BY backup_finish_date DESC) f
OUTER APPLY (SELECT TOP (1) backup_finish_date FROM dbo.backupset
    WHERE database_name=w.DatabaseName AND type='I'
    ORDER BY backup_finish_date DESC) i
OUTER APPLY (SELECT TOP (1) backup_finish_date FROM dbo.backupset
    WHERE database_name=w.DatabaseName AND type='L' AND is_copy_only=0
    ORDER BY backup_finish_date DESC) l
WHERE w.Enabled=1;
GO
SELECT TOP (100) * FROM DBAOperations.dbo.BackupAudit ORDER BY AuditId DESC;
GO
-- Verify the latest successful Jira FULL using its real generated file name.
DECLARE @File nvarchar(260);
SELECT TOP (1) @File=BackupFile FROM DBAOperations.dbo.BackupAudit
WHERE DatabaseName=N'DB-Jira' AND BackupType='FULL' AND Status='SUCCEEDED'
ORDER BY FinishedUtc DESC;
IF @File IS NULL THROW 51300, 'No successful Jira FULL backup found.', 1;
RESTORE VERIFYONLY FROM DISK=@File WITH CHECKSUM,STOP_ON_ERROR;
GO
-- Example freshness thresholds; integrate these rows with your monitoring.
-- A successful Agent execution is not a substitute for per-database checks.
SELECT w.DatabaseName,t.Kind,latest.FinishedUtc,
    CASE WHEN latest.FinishedUtc IS NULL THEN 'MISSING' ELSE 'STALE' END AS AlertReason
FROM DBAOperations.dbo.BackupWhitelist w
-- DIFF has a 12-hour overnight gap (19:00 to 07:00), so 7h would false-alert.
-- Use 13h here, or a schedule-aware check for stricter daytime detection.
CROSS JOIN (VALUES ('FULL',1560),('DIFF',780),('LOG',20)) t(Kind,MaxMinutes)
OUTER APPLY (
    SELECT TOP (1) a.FinishedUtc FROM DBAOperations.dbo.BackupAudit a
    WHERE a.DatabaseName=w.DatabaseName AND a.BackupType=t.Kind AND a.Status='SUCCEEDED'
    ORDER BY a.FinishedUtc DESC
) latest
WHERE w.Enabled=1 AND (latest.FinishedUtc IS NULL OR
    latest.FinishedUtc<DATEADD(minute,-t.MaxMinutes,SYSUTCDATETIME()));
GO