SQL DB Backup consolidated report
Here is the consolidated report script to get all server databases informaiton for the full and differential database backups.
# =====================================================================
# SQL Server Database Backup Monitoring
# =====================================================================
# Backup Logic (per flowchart):
# - System DBs (master, model, msdb): Full Backup < 7 days (No Diff required)
# - User DBs: Full Backup < 7 days AND Differential Backup < 24 hours
# - Excludes: tempdb (system DB)
#
# Requirements:
# - PowerShell 7+
# - SqlServer PowerShell module
# - Database Mail configured on the target SQL Server
# =====================================================================
# -----------------------------
# CONFIGURATION
# -----------------------------
$ServerFile = "C:\SQLBackupMonitor\Servers.txt"
# SQL Server where Database Mail is configured
$MailServer = "SQLSERVER01"
# Database Mail profile
$MailProfile = "DBMailProfile"
# Email recipients
$Recipients = "dba-team@company.com"
# Optional CC
$CC = ""
# Email subject
$MailSubject = "SQL Backup Monitoring Report - $(Get-Date -Format 'dd-MMM-yyyy HH:mm')"
# Maximum number of SQL Servers checked simultaneously
$ThrottleLimit = 5
# SLA Thresholds
$FullThresholdDays = 7
$DiffThresholdHours = 24
# =====================================================================
# CHECK REQUIREMENTS
# =====================================================================
if (-not (Test-Path $ServerFile)) {
throw "Server list file not found: $ServerFile"
}
Import-Module SqlServer -ErrorAction Stop
$Servers = Get-Content $ServerFile |
Where-Object { $_.Trim() -ne "" } |
ForEach-Object { $_.Trim() } |
Sort-Object -Unique
if ($Servers.Count -eq 0) {
throw "No SQL Servers found in $ServerFile"
}
Write-Host ""
Write-Host "SQL Backup Monitoring Started" -ForegroundColor Cyan
Write-Host "Servers to check : $($Servers.Count)"
Write-Host "Started at : $(Get-Date)"
Write-Host ""
# =====================================================================
# T-SQL QUERY (System vs User DB Logic)
# =====================================================================
$Query = @"
SET NOCOUNT ON;
DECLARE @Now DATETIME = GETDATE();
DECLARE @FullCutoff DATETIME = DATEADD(DAY, -$FullThresholdDays, @Now);
DECLARE @DiffCutoff DATETIME = DATEADD(HOUR, -$DiffThresholdHours, @Now);
;WITH BackupInfo AS
(
SELECT
database_name,
MAX(CASE
WHEN type = 'D'
THEN backup_finish_date
END) AS LastFullBackup,
MAX(CASE
WHEN type = 'I'
THEN backup_finish_date
END) AS LastDifferentialBackup
FROM msdb.dbo.backupset
WHERE type IN ('D', 'I')
GROUP BY database_name
),
DatabaseStatus AS
(
SELECT
@@SERVERNAME AS ServerName,
d.name AS DatabaseName,
CASE
WHEN d.database_id <= 4 THEN 'System DB'
ELSE 'User DB'
END AS DBType,
b.LastFullBackup,
b.LastDifferentialBackup,
CASE
-- System DB Logic: Requires only Full Backup < 7 days
WHEN d.database_id <= 4 AND (b.LastFullBackup IS NULL OR b.LastFullBackup < @FullCutoff)
THEN 'Full backup overdue (> 7 days)'
-- User DB Logic: Requires Full < 7 days AND Diff < 24 hours
WHEN d.database_id > 4 AND (b.LastFullBackup IS NULL OR b.LastFullBackup < @FullCutoff)
THEN 'Full backup overdue (> 7 days)'
WHEN d.database_id > 4 AND (b.LastDifferentialBackup IS NULL OR b.LastDifferentialBackup < @DiffCutoff)
THEN 'Differential backup overdue (> 24 hours)'
ELSE NULL
END AS BackupIssue
FROM sys.databases d
LEFT JOIN BackupInfo b
ON d.name = b.database_name
WHERE d.name <> 'tempdb' -- Exclude tempdb
AND d.state_desc = 'ONLINE' -- Only online databases
)
SELECT
ServerName,
DatabaseName,
DBType,
LastFullBackup,
LastDifferentialBackup,
BackupIssue
FROM DatabaseStatus
WHERE BackupIssue IS NOT NULL
ORDER BY DBType DESC, ServerName, DatabaseName;
"@
# =====================================================================
# CHECK ALL SQL SERVERS IN PARALLEL
# =====================================================================
$StartTime = Get-Date
$Results = $Servers | ForEach-Object -Parallel {
$Server = $_
$Query = $using:Query
Write-Host "Checking $Server ..."
try {
# Force array casting using @() syntax
$Rows = @(Invoke-Sqlcmd `
-ServerInstance $Server `
-Database "master" `
-Query $Query `
-QueryTimeout 120 `
-ConnectionTimeout 15 `
-ErrorAction Stop)
if ($Rows.Count -eq 0) {
[PSCustomObject]@{
ServerName = $Server
ResultType = "SERVER"
DatabaseName = ""
DBType = ""
LastFullBackup = $null
LastDifferentialBackup = $null
BackupIssue = ""
Status = "ALL DB'S FINE"
}
}
else {
foreach ($Row in $Rows) {
[PSCustomObject]@{
ServerName = $Row.ServerName
ResultType = "DATABASE"
DatabaseName = $Row.DatabaseName
DBType = $Row.DBType
LastFullBackup = $Row.LastFullBackup
LastDifferentialBackup = $Row.LastDifferentialBackup
BackupIssue = $Row.BackupIssue
Status = "ISSUES FOUND"
}
}
}
}
catch {
[PSCustomObject]@{
ServerName = $Server
ResultType = "SERVER"
DatabaseName = ""
DBType = ""
LastFullBackup = $null
LastDifferentialBackup = $null
BackupIssue = $_.Exception.Message
Status = "SERVER CHECK FAILED"
}
}
} -ThrottleLimit $ThrottleLimit
$EndTime = Get-Date
$Duration = $EndTime - $StartTime
# =====================================================================
# SUMMARY METRICS
# =====================================================================
$ServerResults = $Results | Where-Object { $_.ResultType -eq "SERVER" }
$DatabaseIssues = $Results | Where-Object { $_.ResultType -eq "DATABASE" }
$TotalServers = $Servers.Count
$ServersOK = @($ServerResults | Where-Object { $_.Status -eq "ALL DB'S FINE" }).Count
$ServersFailed = @($ServerResults | Where-Object { $_.Status -eq "SERVER CHECK FAILED" }).Count
$ServersWithIssues = @($DatabaseIssues | Select-Object -ExpandProperty ServerName -Unique).Count
$TotalDatabaseIssues = $DatabaseIssues.Count
# =====================================================================
# GENERATE HTML EMAIL REPORT
# =====================================================================
$Html = @"
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<style>
body {
font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, Helvetica, Arial, sans-serif;
font-size: 13px;
color: #2d3748;
background-color: #f4f6f8;
margin: 0;
padding: 20px;
}
.email-container {
background-color: #ffffff;
border-radius: 8px;
border: 1px solid #e2e8f0;
padding: 24px;
max-width: 800px;
margin: 0 auto;
}
.header {
border-bottom: 2px solid #3182ce;
padding-bottom: 12px;
margin-bottom: 20px;
}
.header h1 {
font-size: 20px;
color: #1a202c;
margin: 0 0 6px 0;
font-weight: 700;
}
.meta-info {
font-size: 12px;
color: #718096;
margin: 0;
}
.section-title {
font-size: 14px;
font-weight: 700;
color: #2d3748;
margin: 20px 0 10px 0;
padding-bottom: 4px;
border-bottom: 1px solid #edf2f7;
}
table.summary-table {
width: 100%;
border-collapse: collapse;
margin-bottom: 20px;
}
table.summary-table td {
padding: 8px 12px;
border: 1px solid #e2e8f0;
font-size: 13px;
}
table.summary-table td.label {
font-weight: 600;
background-color: #f8fafc;
width: 40%;
color: #4a5568;
}
table.summary-table td.value {
font-weight: 700;
color: #1a202c;
}
table.data-table {
width: 100%;
border-collapse: collapse;
margin-bottom: 20px;
font-size: 12px;
}
table.data-table th {
background-color: #2d3748;
color: #ffffff;
font-weight: 600;
text-align: left;
padding: 8px 10px;
border: 1px solid #2d3748;
}
table.data-table td {
padding: 8px 10px;
border: 1px solid #e2e8f0;
vertical-align: top;
}
table.data-table tr:nth-child(even) {
background-color: #f8fafc;
}
.badge {
display: inline-block;
padding: 3px 8px;
border-radius: 4px;
font-size: 11px;
font-weight: 700;
text-transform: uppercase;
}
.badge-ok {
background-color: #def7ec;
color: #03543f;
border: 1px solid #84e1bc;
}
.badge-issue {
background-color: #fde8e8;
color: #9b1c1c;
border: 1px solid #f8b4b4;
}
.badge-failed {
background-color: #ffe8d6;
color: #9a3412;
border: 1px solid #fdba74;
}
.text-red { color: #c81e1e; font-weight: 600; }
.text-muted { color: #718096; font-style: italic; }
.error-box {
font-family: "Courier New", Courier, monospace;
font-size: 11px;
color: #c81e1e;
background-color: #fff5f5;
padding: 6px 8px;
border-radius: 4px;
border-left: 3px solid #f05252;
margin-top: 4px;
}
.signature {
margin-top: 30px;
padding-top: 16px;
border-top: 2px solid #e2e8f0;
font-size: 12px;
color: #4a5568;
}
.signature-title {
font-weight: 700;
color: #2b6cb0;
}
</style>
</head>
<body>
<div class="email-container">
<div class="header">
<h1>SQL Backup Monitoring Report</h1>
<p class="meta-info">Generated on: <b>$(Get-Date -Format "dd-MMM-yyyy HH:mm:ss")</b> | Server Host: <b>$env:COMPUTERNAME</b></p>
</div>
<div class="section-title">Execution Summary</div>
<table class="summary-table">
<tr><td class="label">Total SQL Servers Checked</td><td class="value">$TotalServers</td></tr>
<tr><td class="label">Servers - All DBs Healthy</td><td class="value" style="color: #0e9f6e;">$ServersOK</td></tr>
<tr><td class="label">Servers - Backup Issues Found</td><td class="value" style="color: #f05252;">$ServersWithIssues</td></tr>
<tr><td class="label">Servers - Connection / Check Failed</td><td class="value" style="color: #c2410c;">$ServersFailed</td></tr>
<tr><td class="label">Total Database Backup Exceptions</td><td class="value" style="color: #f05252;">$TotalDatabaseIssues</td></tr>
<tr><td class="label">Script Execution Time</td><td class="value">$($Duration.ToString("hh\:mm\:ss"))</td></tr>
</table>
<div class="section-title">Server Status Overview</div>
<table class="data-table">
<thead>
<tr>
<th style="width: 35%;">Server Name</th>
<th style="width: 25%;">Status</th>
<th style="width: 40%;">Details</th>
</tr>
</thead>
<tbody>
"@
# --- Server Overview Rows ---
foreach ($Server in $Servers) {
$ServerData = $Results | Where-Object { $_.ServerName -eq $Server }
$HasFailure = $ServerData | Where-Object { $_.Status -eq "SERVER CHECK FAILED" }
$HasIssues = $ServerData | Where-Object { $_.Status -eq "ISSUES FOUND" }
if ($HasFailure) {
$ErrorMessage = ($HasFailure | Select-Object -First 1).BackupIssue
$Html += @"
<tr>
<td><strong>$Server</strong></td>
<td><span class="badge badge-failed">CHECK FAILED</span></td>
<td>
Connection / Execution Failure
<div class="error-box">$ErrorMessage</div>
</td>
</tr>
"@
}
elseif ($HasIssues) {
$IssueCount = @($HasIssues).Count
$Html += @"
<tr>
<td><strong>$Server</strong></td>
<td><span class="badge badge-issue">ISSUES FOUND</span></td>
<td class="text-red">$IssueCount database(s) failing backup SLA</td>
</tr>
"@
}
else {
$Html += @"
<tr>
<td><strong>$Server</strong></td>
<td><span class="badge badge-ok">ALL DB'S FINE</span></td>
<td class="text-muted">All databases met backup SLAs</td>
</tr>
"@
}
}
$Html += @"
</tbody>
</table>
<div class="section-title">Database Backup Exceptions</div>
"@
# --- Database Exceptions Table ---
if ($TotalDatabaseIssues -eq 0) {
$Html += @"
<p class="text-muted" style="color: #2e7d32; font-weight: 600;">
✔ No database backup exceptions detected across System or User databases.
</p>
"@
}
else {
$Html += @"
<table class="data-table">
<thead>
<tr>
<th>Server</th>
<th>Database</th>
<th>Type</th>
<th>Issue Description</th>
<th>Last Full</th>
<th>Last Diff</th>
</tr>
</thead>
<tbody>
"@
foreach ($Row in $DatabaseIssues) {
$FullBackup = if ($Row.LastFullBackup) { ([datetime]$Row.LastFullBackup).ToString("dd-MMM HH:mm") } else { "NEVER" }
$DiffBackup = if ($Row.DBType -eq "System DB") { "N/A" } elseif ($Row.LastDifferentialBackup) { ([datetime]$Row.LastDifferentialBackup).ToString("dd-MMM HH:mm") } else { "NEVER" }
$Html += @"
<tr>
<td><strong>$($Row.ServerName)</strong></td>
<td>$($Row.DatabaseName)</td>
<td><span class="text-muted">$($Row.DBType)</span></td>
<td class="text-red">$($Row.BackupIssue)</td>
<td>$FullBackup</td>
<td>$DiffBackup</td>
</tr>
"@
}
$Html += @"
</tbody>
</table>
"@
}
# --- Automated Signature ---
$Html += @"
<div class="signature">
<p style="margin: 0 0 4px 0;" class="signature-title">Central Database Automation Engine</p>
<p style="margin: 0 0 4px 0;">Database Infrastructure & Administration Team</p>
<p style="margin: 0; color: #a0aec0; font-size: 11px;">
This email was generated automatically by Central Monitoring Server (<b>$env:COMPUTERNAME</b>).<br>
Please do not reply directly to this message. For support, open a ticket with the DBA Infrastructure team.
</p>
</div>
</div>
</body>
</html>
"@
# =====================================================================
# SEND EMAIL VIA DATABASE MAIL
# =====================================================================
$SafeHtml = $Html.Replace("'", "''")
$SafeSubject = $MailSubject.Replace("'", "''")
$SafeRecipients = $Recipients.Replace("'", "''")
$MailQuery = @"
EXEC msdb.dbo.sp_send_dbmail
@profile_name = N'$MailProfile',
@recipients = N'$SafeRecipients',
$(if ($CC -ne "") { "@copy_recipients = N'$($CC.Replace("'", "''"))'," })
@subject = N'$SafeSubject',
@body = N'$SafeHtml',
@body_format = 'HTML';
"@
try {
Invoke-Sqlcmd `
-ServerInstance $MailServer `
-Database "msdb" `
-Query $MailQuery `
-QueryTimeout 120 `
-ErrorAction Stop
Write-Host ""
Write-Host "Email notification sent successfully." -ForegroundColor Green
}
catch {
Write-Host ""
Write-Host "ERROR: Failed to dispatch Database Mail." -ForegroundColor Red
Write-Host $_.Exception.Message -ForegroundColor Red
}
# =====================================================================
# CONSOLE SUMMARY
# =====================================================================
Write-Host ""
Write-Host "==================================================" -ForegroundColor Cyan
Write-Host "SQL BACKUP MONITORING COMPLETE" -ForegroundColor Cyan
Write-Host "==================================================" -ForegroundColor Cyan
Write-Host "Servers checked : $TotalServers"
Write-Host "Servers OK : $ServersOK"
Write-Host "Servers with issues : $ServersWithIssues"
Write-Host "Servers check failed : $ServersFailed"
Write-Host "Database issues : $TotalDatabaseIssues"
Write-Host "Execution time : $($Duration.ToString("hh\:mm\:ss"))"
Write-Host "==================================================" -ForegroundColor Cyan
We need to configure email on the central repository server.
Schedule this report so we will get email on specific times
