Database-BackupVerification.ps1

<#
.SYNOPSIS
    Verifies SQL Server backup integrity and reports on backup status.

.DESCRIPTION
    Checks backup recency against RPO requirements, verifies backup file
    integrity using RESTORE VERIFYONLY, and identifies databases without
    recent backups. Outputs a summary report.

.PARAMETER ServerInstance
    SQL Server instance name.

.PARAMETER RPOHours
    Recovery Point Objective in hours (default 24).

.PARAMETER LogPath
    Path to write the verification report.

.EXAMPLE
    .\Database-BackupVerification.ps1 -ServerInstance "SQLSERVER01" -RPOHours 24
#>

[CmdletBinding()]
param(
    [Parameter(Mandatory)]
    [string]$ServerInstance,

    [int]$RPOHours = 24,

    [string]$LogPath = "D:\DBA\Logs"
)

$ErrorActionPreference = "Stop"
$timestamp = Get-Date -Format "yyyyMMdd_HHmmss"
$logFile = Join-Path $LogPath "BackupVerification_$timestamp.log"

function Write-Log {
    param([string]$Message, [string]$Level = "INFO")
    $entry = "$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss') [$Level] $Message"
    Add-Content -Path $logFile -Value $entry
    Write-Host $entry
}

if (-not (Test-Path $LogPath)) {
    New-Item -ItemType Directory -Path $LogPath -Force | Out-Null
}

Write-Log "Backup verification started on $ServerInstance (RPO: $RPOHours hours)"

$issues = @()

try {
    # Check backup recency for all user databases
    $backupStatus = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query @"
        SELECT
            d.name AS DatabaseName,
            d.recovery_model_desc AS RecoveryModel,
            MAX(b.backup_finish_date) AS LastBackup,
            DATEDIFF(HOUR, MAX(b.backup_finish_date), GETDATE()) AS HoursSinceBackup
        FROM sys.databases d
        LEFT JOIN msdb.dbo.backupset b ON d.name = b.database_name AND b.type = 'D'
        WHERE d.database_id > 4 AND d.state = 0
        GROUP BY d.name, d.recovery_model_desc
        ORDER BY d.name
"@

    foreach ($db in $backupStatus) {
        $dbName = $db.DatabaseName
        $hours = $db.HoursSinceBackup

        if ($null -eq $db.LastBackup) {
            Write-Log "CRITICAL: $dbName - No backup found" -Level "ERROR"
            $issues += [PSCustomObject]@{ Database = $dbName; Issue = "No backup found"; Severity = "CRITICAL" }
        }
        elseif ($hours -gt $RPOHours) {
            Write-Log "WARNING: $dbName - Last backup $hours hours ago (RPO: $RPOHours)" -Level "WARN"
            $issues += [PSCustomObject]@{ Database = $dbName; Issue = "Exceeds RPO ($hours hours)"; Severity = "WARNING" }
        }
        else {
            Write-Log "OK: $dbName - Last backup $hours hours ago"
        }
    }

    # Verify latest full backup integrity
    Write-Log "Running RESTORE VERIFYONLY on latest backups..."

    $latestBackups = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query @"
        SELECT
            b.database_name,
            bmf.physical_device_name
        FROM msdb.dbo.backupset b
        INNER JOIN msdb.dbo.backupmediafamily bmf ON b.media_set_id = bmf.media_set_id
        WHERE b.type = 'D'
          AND b.backup_finish_date = (
              SELECT MAX(b2.backup_finish_date)
              FROM msdb.dbo.backupset b2
              WHERE b2.database_name = b.database_name AND b2.type = 'D'
          )
"@

    foreach ($backup in $latestBackups) {
        $dbName = $backup.database_name
        $path = $backup.physical_device_name

        try {
            Invoke-Sqlcmd -ServerInstance $ServerInstance -Query "RESTORE VERIFYONLY FROM DISK = N'$path' WITH CHECKSUM" -QueryTimeout 600
            Write-Log "VERIFIED: $dbName - $path"
        }
        catch {
            Write-Log "FAILED: $dbName - $path - $_" -Level "ERROR"
            $issues += [PSCustomObject]@{ Database = $dbName; Issue = "Backup verification failed"; Severity = "CRITICAL" }
        }
    }
}
catch {
    Write-Log "FATAL: $_" -Level "ERROR"
    throw
}

# Summary
Write-Log "========================================="
Write-Log "Backup Verification Summary"
Write-Log "Databases checked: $($backupStatus.Count)"
Write-Log "Issues found: $($issues.Count)"

if ($issues.Count -gt 0) {
    Write-Log "Issues:" -Level "WARN"
    foreach ($issue in $issues) {
        Write-Log "  [$($issue.Severity)] $($issue.Database): $($issue.Issue)" -Level "WARN"
    }
}

Write-Log "Verification complete"