Here is the consolidated report script to get all server databases informaiton for the full and differential database backups.
# ==========================================
# 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')"
# Force TLS 1.2 for modern SMTP servers
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
$StartTime = Get-Date
# ==========================================
# SQL Query to Fetch Backup History
# ==========================================
$SqlQuery = @"
SELECT
d.name AS DatabaseName,
d.database_id,
d.recovery_model_desc AS RecoveryModel,
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,
MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS LastLogBackup
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, d.recovery_model_desc;
"@
# 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("#") }
if ($SqlServerList.Count -eq 0) {
Write-Warning "No servers found in $ServerListFile."
exit
}
foreach ($Server in $SqlServerList) {
$Server = $Server.Trim()
$TotalServers++
$ServerHasIssues = $false
$ServerExceptionsCount = 0
try {
$ConnectionString = "Server=$Server;Database=msdb;Integrated Security=SSPI;Connection Timeout=10;"
$Connection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)
$Connection.Open()
$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
$RecoveryModel = $Row.RecoveryModel
$LastFull = if ($Row.LastFullBackup -ne [DBNull]::Value) { [datetime]$Row.LastFullBackup } else { $null }
$LastDiff = if ($Row.LastDiffBackup -ne [DBNull]::Value) { [datetime]$Row.LastDiffBackup } else { $null }
$LastLog = if ($Row.LastLogBackup -ne [DBNull]::Value) { [datetime]$Row.LastLogBackup } else { $null }
$FullDaysOld = if ($LastFull) { ($StartTime - $LastFull).TotalDays } else { 9999 }
$DiffHoursOld = if ($LastDiff) { ($StartTime - $LastDiff).TotalHours } else { 9999 }
$LogHoursOld = if ($LastLog) { ($StartTime - $LastLog).TotalHours } else { 9999 }
$IssueDescription = $null
# Evaluation 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 {
# User DBs SLA Logic
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)"
}
# Added Log Backup Check for FULL recovery model
if ($RecoveryModel -eq 'FULL' -and $LogHoursOld -gt 2) {
$LogIssue = if ($null -eq $LastLog) { "Log backup NEVER performed" } else { "Log backup overdue (> 2 hours)" }
$IssueDescription = if ($IssueDescription) { "$IssueDescription | $LogIssue" } else { $LogIssue }
}
}
# Record Exceptions
if ($IssueDescription) {
$ServerHasIssues = $true
$ServerExceptionsCount++
# Pre-calculate strings to avoid hashtable syntax errors
$LastFullStr = if ($LastFull) { $LastFull.ToString("dd-MMM HH:mm") } else { "NEVER" }
$LastDiffStr = if ($DbType -eq 'System DB') { "N/A" } elseif ($LastDiff) { $LastDiff.ToString("dd-MMM HH:mm") } else { "NEVER" }
$ExceptionList += [PSCustomObject]@{
Server = $Server
Database = $DbName
Type = $DbType
IssueDescription = $IssueDescription
LastFull = $LastFullStr
LastDiff = $LastDiffStr
}
}
}
if ($ServerHasIssues) {
$ServersFailed++
$ServerResults += [PSCustomObject]@{
ServerName = $Server
Status = "ISSUES FOUND"
Details = "<span style='color: #c0392b; font-weight: bold;'>$ServerExceptionsCount database(s) failing backup SLA</span>"
}
} else {
$ServersHealthy++
$ServerResults += [PSCustomObject]@{
ServerName = $Server
Status = "ALL DB'S FINE"
Details = "<span style='color: #7f8c8d; font-style: italic;'>All databases met backup SLAs</span>"
}
}
} catch {
$ServersUnreachable++
# Sanitize error message to prevent HTML breaking
$ErrorMessage = [System.Net.WebUtility]::HtmlEncode($_.Exception.Message.Trim())
$ServerResults += [PSCustomObject]@{
ServerName = $Server
Status = "CHECK FAILED"
Details = "<div style='color: #c0392b; font-weight: bold;'>Connection / Execution Failure</div><div style='background: #fdf2e9; color: #c0392b; font-family: monospace; padding: 6px; border-left: 3px solid #e67e22; margin-top: 4px;'>$ErrorMessage</div>"
}
}
}
$EndTime = Get-Date
$Duration = "{0:hh\:mm\:ss}" -f ($EndTime - $StartTime)
$HostName = $env:COMPUTERNAME
# ==========================================
# HTML Email Generation (Unchanged from your original, just ensuring variables match)
# ==========================================
$HtmlHeader = @"
<!DOCTYPE html>
<html>
<head>
<style>
body { font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif; color: #333; background-color: #f4f6f9; margin: 0; padding: 20px; }
.container { background-color: #ffffff; padding: 25px; border-radius: 6px; max-width: 900px; margin: auto; box-shadow: 0 2px 5px rgba(0,0,0,0.1); }
h2 { color: #2c3e50; margin-bottom: 5px; }
.meta-info { font-size: 13px; color: #7f8c8d; margin-bottom: 20px; border-bottom: 2px solid #3498db; padding-bottom: 10px; }
.section-title { font-size: 16px; font-weight: bold; color: #2c3e50; margin-top: 25px; margin-bottom: 10px; }
table { width: 100%; border-collapse: collapse; margin-bottom: 15px; font-size: 13px; }
th { background-color: #2c3e50; color: #ffffff; text-align: left; padding: 10px; }
td { padding: 9px 10px; border-bottom: 1px solid #eef1f5; vertical-align: top; }
.badge { padding: 4px 8px; border-radius: 4px; font-weight: bold; font-size: 11px; display: inline-block; }
.badge-green { background-color: #e8f8f5; color: #27ae60; border: 1px solid #a3e4d7; }
.badge-orange { background-color: #fef9e7; color: #d35400; border: 1px solid #f9e79f; }
.badge-red { background-color: #fadbd8; color: #78281f; border: 1px solid #f5b7b1; }
.footer { font-size: 12px; color: #7f8c8d; margin-top: 30px; border-top: 1px solid #eef1f5; padding-top: 15px; }
</style>
</head>
<body>
<div class="container">
<h2>SQL Backup Monitoring Report</h2>
<div class="meta-info">
Generated on: <b>$(Get-Date -Format 'dd-MMM-yyyy HH:mm:ss')</b> | Server Host: <b>$HostName</b>
</div>
<div class="section-title">Execution Summary</div>
<table>
<tr><td style="width: 40%;">Total SQL Servers Checked</td><td><b>$TotalServers</b></td></tr>
<tr><td>Servers - All DBs Healthy</td><td><b style="color: #27ae60;">$ServersHealthy</b></td></tr>
<tr><td>Servers - Backup Issues Found</td><td><b style="color: #c0392b;">$ServersFailed</b></td></tr>
<tr><td>Servers - Connection / Check Failed</td><td><b style="color: #d35400;">$ServersUnreachable</b></td></tr>
<tr><td>Total Database Backup Exceptions</td><td><b style="color: #c0392b;">$($ExceptionList.Count)</b></td></tr>
<tr><td>Script Execution Time</td><td><b>$Duration</b></td></tr>
</table>
<div class="section-title">Server Status Overview</div>
<table>
<thead>
<tr>
<th style="width: 30%;">Server Name</th>
<th style="width: 25%;">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>
"@
if ($ExceptionList.Count -gt 0) {
$HtmlHeader += @"
<div class="section-title">Database Backup Exceptions</div>
<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 ($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: #c0392b; font-weight: bold;">$($ex.IssueDescription)</span></td>
<td>$($ex.LastFull)</td>
<td>$($ex.LastDiff)</td>
</tr>
"@
}
$HtmlHeader += @"
</tbody>
</table>
"@
}
$HtmlHeader += @"
<div class="footer">
<b>Central Database Automation Engine</b><br>
Database Infrastructure & Administration Team<br>
<span style="font-size: 11px;">This email was generated automatically by Central Monitoring Server ($HostName). 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
Post a Comment