SQL DB Backup verification report V2
This script gives failure backups which are older than 7 days for full and 24 hours older than the backup of the differential.
# ==========================================
# Configuration Parameters
# ==========================================
$ServerListFile = "C:\Data\Sqlserver.txt"
$SmtpServer = "smtp.yourcompany.com"
$SmtpPort = 25
$EmailFrom = "sqlmonitoring@yourcompany.com"
$EmailTo = "dba-team@yourcompany.com"
$EmailSubject = "SQL Backup Monitoring Report - $(Get-Date -Format 'dd-MMM-yyyy')"
$StartTime = Get-Date
# ==========================================
# SQL Query to Fetch Backup History
# ==========================================
$SqlQuery = @"
SELECT
d.name AS DatabaseName,
d.database_id,
CASE WHEN d.name IN ('master', 'model', 'msdb') THEN 'System DB' ELSE 'User DB' END AS DBType,
MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS LastFullBackup,
MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS LastDiffBackup
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset b ON d.name = b.database_name
WHERE d.name <> 'tempdb' AND d.state_desc = 'ONLINE'
GROUP BY d.name, d.database_id;
"@
# Data Collectors
$ServerResults = @()
$ExceptionList = @()
$TotalServers = 0
$ServersHealthy = 0
$ServersFailed = 0
$ServersUnreachable = 0
if (-not (Test-Path $ServerListFile)) {
Write-Error "Server list file not found at $ServerListFile"
exit
}
$SqlServerList = Get-Content $ServerListFile | Where-Object { $_ -and -not $_.StartsWith("#") }
foreach ($ServerInput in $SqlServerList) {
$ServerInput = $ServerInput.Trim()
$TotalServers++
$ServerHasIssues = $false
$ServerExceptionsCount = 0
$DisplayName = $ServerInput # Default to IP address if connection fails
try {
$ConnectionString = "Server=$ServerInput;Database=msdb;Integrated Security=SSPI;Connection Timeout=10;"
$Connection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)
$Connection.Open()
# Get actual SQL Instance Name (@@SERVERNAME)
$CmdInstance = $Connection.CreateCommand()
$CmdInstance.CommandText = "SELECT @@SERVERNAME AS SqlInstanceName;"
$DisplayName = $CmdInstance.ExecuteScalar()
# Run Backup Check Query
$Command = $Connection.CreateCommand()
$Command.CommandText = $SqlQuery
$Adapter = New-Object System.Data.SqlClient.SqlDataAdapter($Command)
$DataTable = New-Object System.Data.DataTable
[void]$Adapter.Fill($DataTable)
$Connection.Close()
foreach ($Row in $DataTable) {
$DbName = $Row.DatabaseName
$DbType = $Row.DBType
$LastFull = if ($Row.LastFullBackup -ne [DBNull]::Value) { [datetime]$Row.LastFullBackup } else { $null }
$LastDiff = if ($Row.LastDiffBackup -ne [DBNull]::Value) { [datetime]$Row.LastDiffBackup } else { $null }
$FullDaysOld = if ($LastFull) { ($StartTime - $LastFull).TotalDays } else { 9999 }
$DiffHoursOld = if ($LastDiff) { ($StartTime - $LastDiff).TotalHours } else { 9999 }
$IssueDescription = $null
# SLA Rules
if ($DbType -eq 'System DB') {
if ($FullDaysOld -gt 7) {
$IssueDescription = if ($null -eq $LastFull) { "Full backup NEVER performed" } else { "Full backup overdue (> 7 days)" }
}
} else {
if ($FullDaysOld -gt 7 -and $DiffHoursOld -gt 24) {
$IssueDescription = "Full (> 7 days) and Differential (> 24 hours) backups overdue"
} elseif ($FullDaysOld -gt 7) {
$IssueDescription = "Full backup overdue (> 7 days)"
} elseif ($DiffHoursOld -gt 24) {
$IssueDescription = "Differential backup overdue (> 24 hours)"
}
}
if ($IssueDescription) {
$ServerHasIssues = $true
$ServerExceptionsCount++
$ExceptionList += [PSCustomObject]@{
Server = $DisplayName # Displays Server\Instance Name
Database = $DbName
Type = $DbType
IssueDescription = $IssueDescription
LastFull = if ($LastFull) { $LastFull.ToString("dd-MMM HH:mm") } else { "NEVER" }
LastDiff = if ($DbType -eq 'System DB') { "N/A" } else { if ($LastDiff) { $LastDiff.ToString("dd-MMM HH:mm") } else { "NEVER" } }
}
}
}
if ($ServerHasIssues) {
$ServersFailed++
$ServerResults += [PSCustomObject]@{
ServerName = $DisplayName # Displays Server\Instance Name
Status = "ISSUES FOUND"
Details = "<span style='color: #dc2626; font-weight: bold;'>$ServerExceptionsCount database(s) failing backup SLA</span>"
}
} else {
$ServersHealthy++
$ServerResults += [PSCustomObject]@{
ServerName = $DisplayName # Displays Server\Instance Name
Status = "ALL DB'S FINE"
Details = "<span style='color: #64748b; font-style: italic;'>All databases met backup SLAs</span>"
}
}
} catch {
$ServersUnreachable++
$ErrorMessage = $_.Exception.Message.Trim()
$ServerResults += [PSCustomObject]@{
ServerName = "$ServerInput (Unreachable)" # Shows IP and failure status
Status = "CHECK FAILED"
Details = "<div style='color: #dc2626; font-weight: bold;'>Connection / Execution Failure</div><div style='background-color: #fff1f2; color: #991b1b; font-family: Consolas, monospace; font-size: 12px; padding: 8px 12px; border-left: 4px solid #f43f5e; border-radius: 4px; margin-top: 5px; word-break: break-all;'>$ErrorMessage</div>"
}
}
}
$EndTime = Get-Date
$Duration = "{0:hh}:{0:mm}:{0:ss}" -f ($EndTime - $StartTime)
$HostName = $env:COMPUTERNAME
# ==========================================
# Modern Outlook-Compatible HTML Email Body
# ==========================================
$HtmlHeader = @"
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<style>
body { font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif; color: #1e293b; background-color: #f1f5f9; margin: 0; padding: 20px; }
.container { background-color: #ffffff; padding: 30px; border-radius: 12px; max-width: 900px; margin: auto; border: 1px solid #e2e8f0; box-shadow: 0 4px 6px -1px rgba(0, 0, 0, 0.1); }
/* Centered Header with Modern Royal Blue Accent */
.header-card { text-align: center; background: linear-gradient(135deg, #1e40af, #3b82f6); color: #ffffff; padding: 24px; border-radius: 10px; margin-bottom: 25px; }
.header-card h2 { margin: 0 0 8px 0; font-size: 22px; font-weight: 700; color: #ffffff; letter-spacing: 0.5px; }
.header-card .meta-info { font-size: 13px; color: #dbeafe; font-weight: 400; margin: 0; }
.header-card .meta-info b { color: #ffffff; }
.section-title { font-size: 15px; font-weight: 700; color: #0f172a; margin-top: 25px; margin-bottom: 12px; text-transform: uppercase; letter-spacing: 0.5px; }
/* Rounded Table Wrappers */
.table-card { border: 1px solid #e2e8f0; border-radius: 8px; overflow: hidden; margin-bottom: 20px; background-color: #ffffff; }
table { width: 100%; border-collapse: collapse; font-size: 13px; }
th { background-color: #1e293b; color: #ffffff; text-align: left; padding: 12px 14px; font-weight: 600; font-size: 12px; letter-spacing: 0.3px; }
td { padding: 10px 14px; border-bottom: 1px solid #f1f5f9; vertical-align: middle; }
tr:last-child td { border-bottom: none; }
.summary-table td:first-child { width: 45%; color: #475569; font-weight: 500; }
.summary-table td:last-child { font-weight: 600; }
/* Modern Status Badges */
.badge { padding: 4px 10px; border-radius: 6px; font-weight: 700; font-size: 11px; display: inline-block; letter-spacing: 0.3px; }
.badge-green { background-color: #dcfce7; color: #15803d; border: 1px solid #bbf7d0; }
.badge-orange { background-color: #ffedd5; color: #c2410c; border: 1px solid #fed7aa; }
.badge-red { background-color: #fee2e2; color: #b91c1c; border: 1px solid #fca5a5; }
/* Rounded Light Signature Card */
.footer-card { background-color: #f8fafc; border: 1px solid #e2e8f0; border-radius: 8px; padding: 16px 20px; margin-top: 30px; font-size: 12px; color: #64748b; line-height: 1.5; }
.footer-title { font-weight: 700; color: #0284c7; font-size: 13px; margin-bottom: 2px; }
</style>
</head>
<body>
<div class="container">
<!-- Centered Header Card -->
<div class="header-card">
<h2>SQL Backup Monitoring Report</h2>
<p class="meta-info">Generated on: <b>$(Get-Date -Format 'dd-MMM-yyyy HH:mm:ss')</b> | Server Host: <b>$HostName</b></p>
</div>
<!-- Execution Summary -->
<div class="section-title">Execution Summary</div>
<div class="table-card">
<table class="summary-table">
<tr><td>Total SQL Servers Checked</td><td><b>$TotalServers</b></td></tr>
<tr><td>Servers - All DBs Healthy</td><td><span style="color: #16a34a;">$ServersHealthy</span></td></tr>
<tr><td>Servers - Backup Issues Found</td><td><span style="color: #dc2626;">$ServersFailed</span></td></tr>
<tr><td>Servers - Connection / Check Failed</td><td><span style="color: #ea580c;">$ServersUnreachable</span></td></tr>
<tr><td>Total Database Backup Exceptions</td><td><span style="color: #dc2626;">$($ExceptionList.Count)</span></td></tr>
<tr><td>Script Execution Time</td><td><b>$Duration</b></td></tr>
</table>
</div>
<!-- Server Status Overview -->
<div class="section-title">Server Status Overview</div>
<div class="table-card">
<table>
<thead>
<tr>
<th style="width: 28%;">Server Name</th>
<th style="width: 24%;">Status</th>
<th>Details</th>
</tr>
</thead>
<tbody>
"@
foreach ($res in $ServerResults) {
$StatusBadge = switch ($res.Status) {
"ALL DB'S FINE" { "<span class='badge badge-green'>ALL DB'S FINE</span>" }
"ISSUES FOUND" { "<span class='badge badge-red'>ISSUES FOUND</span>" }
"CHECK FAILED" { "<span class='badge badge-orange'>CHECK FAILED</span>" }
}
$HtmlHeader += @"
<tr>
<td><b>$($res.ServerName)</b></td>
<td>$StatusBadge</td>
<td>$($res.Details)</td>
</tr>
"@
}
$HtmlHeader += @"
</tbody>
</table>
</div>
"@
# Database Exceptions Section
if ($ExceptionList.Count -gt 0) {
$HtmlHeader += @"
<div class="section-title">Database Backup Exceptions</div>
<div class="table-card">
<table>
<thead>
<tr>
<th style="width: 18%;">Server</th>
<th style="width: 18%;">Database</th>
<th style="width: 14%;">Type</th>
<th style="width: 32%;">Issue Description</th>
<th style="width: 9%;">Last Full</th>
<th style="width: 9%;">Last Diff</th>
</tr>
</thead>
<tbody>
"@
foreach ($ex in $ExceptionList) {
$HtmlHeader += @"
<tr>
<td><b>$($ex.Server)</b></td>
<td>$($ex.Database)</td>
<td><i>$($ex.Type)</i></td>
<td><span style="color: #dc2626; font-weight: 600;">$($ex.IssueDescription)</span></td>
<td>$($ex.LastFull)</td>
<td>$($ex.LastDiff)</td>
</tr>
"@
}
$HtmlHeader += @"
</tbody>
</table>
</div>
"@
}
# Rounded Light Signature Card
$HtmlHeader += @"
<div class="footer-card">
<div class="footer-title">Central Database Automation Engine</div>
<b>Database Infrastructure & Administration Team</b><br>
<span style="font-size: 11px; color: #94a3b8;">This email was generated automatically by Central Monitoring Server (<b>$HostName</b>). Please do not reply directly to this message.</span>
</div>
</div>
</body>
</html>
"@
# Send Email
Send-MailMessage -SmtpServer $SmtpServer `
-Port $SmtpPort `
-From $EmailFrom `
-To $EmailTo `
-Subject $EmailSubject `
-Body $HtmlHeader `
-BodyAsHtml
# Code completed.
