This content originally appeared on HackerNoon and was authored by Deepesh Dhake (SQL Server DBA)
SQL Server backup history is typically like a troubleshooting artifact, something checked when an error occurs, not necessarily analyzed over time. In this article, we will go over how to implement a structured backup telemetry table based on data and use Isolation Forest (a machine learning model) in conjunction with simple rules to detect pre-incident performance drift, I/O degradation, and recoverability risks.
The Problem with Binary Backup Monitoring
Backup monitoring is typically binary. Either the backup is successful or not, and that is about as far as your analysis goes.
The issue with this approach is that most backup problems do not begin with failure. They take slow steps, such as longer run times, lower throughput, and lower compression ratios. A change that can be detected in the metrics long before it manifests into an operational incident. I have seen backups drift from 20 minutes to 55 minutes, and no one noticed until the maintenance window was breached over a period of three months.
As a first step, stop querying the MSDB database on demand and start persisting this backup history in a table structure that supports trend analysis.
The Architecture: Three Layers of Detection
\
Layer 1: Data Collection
Use a lightweight telemetry table to persist system metrics rather than querying it on-demand This creates an account which allows for the tracking of trends.
Layer 2: Anomaly Detection
Identify statistical outliers in the feature space using Isolation Forest (an unsupervised ML model). It captures patterns you would may not even think to write rules for. Supplemented with smart business rules that can tap into operational context beyond the purview of a model.
Layer 3: Intelligent Alerting
Aggregate model scores with rule based checks. Use order of priority: recoverability, violations of business rules, significance in statistics. This prevents alert fatigue.
Implementation
Step 1: Building the Telemetry Table
The table is intentionally lightweight. This is not simply copying system metadata but rather solidifying it so that it can be used in analysis.
\
CREATE TABLE dbo.BackupMonitoringHistory
(
BackupID BIGINT IDENTITY(1,1) PRIMARY KEY,
DatabaseName SYSNAME,
BackupStartDate DATETIME,
BackupFinishDate DATETIME,
BackupType VARCHAR(20),
DurationSeconds INT,
BackupSizeMB DECIMAL(18,2),
CompressedBackupSizeMB DECIMAL(18,2),
ChecksumStatus BIT,
LastLSN NUMERIC(25,0),
FirstLSN NUMERIC(25,0),
CollectionDate DATETIME DEFAULT GETDATE()
);
\ It selects data from the MSDB database in the following collection query.
INSERT INTO dbo.BackupMonitoringHistory
(
DatabaseName,
BackupStartDate,
BackupFinishDate,
BackupType,
DurationSeconds,
BackupSizeMB,
CompressedBackupSizeMB,
ChecksumStatus,
LastLSN,
FirstLSN
)
SELECT
bs.database_name,
bs.backup_start_date,
bs.backup_finish_date,
CASE bs.type
WHEN 'D' THEN 'FULL'
WHEN 'I' THEN 'DIFF'
WHEN 'L' THEN 'LOG'
ELSE 'OTHER'
END,
CASE
WHEN DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) = 0
THEN 1
ELSE DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date)
END,
CAST(bs.backup_size / 1048576.0 AS DECIMAL(18,2)),
CAST(bs.compressed_backup_size / 1048576.0 AS DECIMAL(18,2)),
CASE WHEN bs.has_backup_checksums = 1 THEN 1 ELSE 0 END,
bs.last_lsn,
bs.first_lsn
FROM msdb.dbo.backupset bs
WHERE bs.backup_finish_date IS NOT NULL
AND bs.backup_start_date >= DATEADD(HOUR, -1, GETDATE())
ORDER BY bs.backup_start_date;
\ This could be scheduled through a SQL Agent job, and after a few weeks, you have advice that tells you much more than "backup succeeded" in days to weeks.
Step 2: Deriving Operational Metrics
Duration and size are raw metrics that help us, but true signals are derived from indicators that compare each backup to its own recent history. This is where you are no longer looking at "what happened" and transition into the more ominous "is this regular?"
# Basic operational metrics
df["IO_Throughput_MBPS"] = df["BackupSizeMB"] / df["DurationSeconds"]
df["Compression_Ratio"] = df["BackupSizeMB"] / df["CompressedBackupSizeMB"].replace(0, 1)
# Growth rate: how much has size changed from the previous backup
df["PreviousBackupSizeMB"] = (
df.groupby(["DatabaseName", "BackupType"])["BackupSizeMB"].shift(1)
)
df["GrowthRate"] = (
(df["BackupSizeMB"] - df["PreviousBackupSizeMB"])
/ df["PreviousBackupSizeMB"].replace(0, np.nan)
)
df["GrowthRate_Capped"] = df["GrowthRate"].clip(-5, 5)
# Rolling baselines
df["RollingAvgDuration"] = (
df.groupby("DatabaseName")["DurationSeconds"]
.transform(lambda x: x.rolling(7, min_periods=1).mean())
)
df["RollingAvgThroughput"] = (
df.groupby("DatabaseName")["IO_Throughput_MBPS"]
.transform(lambda x: x.rolling(7, min_periods=1).mean())
)
# Deviation from rolling averages
df["ThroughputDeviation"] = (
(df["IO_Throughput_MBPS"] - df["RollingAvgThroughput"])
/ df["RollingAvgThroughput"].replace(0, 1)
)
df["DurationDeviation"] = (
(df["DurationSeconds"] - df["RollingAvgDuration"])
/ df["RollingAvgDuration"].replace(0, 1)
)
# Seconds per GB: isolates I/O speed from backup volume
df["SecondsPerGB"] = (
df["DurationSeconds"] / (df["BackupSizeMB"] / 1024).replace(0, 1)
)
# Combined I/O risk flag — all three conditions must be true
df["IO_Risk"] = np.where(
(df["ThroughputDeviation"] < -0.3) &
(df["DurationDeviation"] > 0.3) &
(df["GrowthRate_Capped"].abs() < 0.5),
"HIGH", "NORMAL"
)
\ The 7 rolling window smooths the daily change and still captures real changes. If the growth rate is higher than 3, then the backup has grown more than threefold — always worth checking.
With the relationship between throughput, duration, and backup size, you can get I/O health. The rationale is simple: if we see a drop in throughput and an increase in duration but backup size remains constant, then the issue must be I/O, not data growth.
But you need all three of those conditions to be true at the same time; we would get false positives from situations where the duration naturally gets longer because there was more data to write.
Step 3: Train Isolation Forest Per System
Isolation Forest isolates records that differ from what is expected. Model runs are done per database to prevent compounding workload behaviors.
features = [
"DurationSeconds", "BackupSizeMB", "CompressedBackupSizeMB","IO_Throughput_MBPS", "Compression_Ratio", "GrowthRate","ThroughputDeviation", "DurationDeviation", "SecondsPerGB"
]
for db_name in df["DatabaseName"].unique():
df_db = df[df["DatabaseName"] == db_name].copy()
model = IsolationForest(contamination=0.03, random_state=42)
df_db["Anomaly"] = model.fit_predict(df_db[features])
\ In fact, contamination=0.03 is a bit conservative — only about 3 percent of records become flagged as outliers. The strength of the model is that it identifies patterns you would not have thought to write a rule for. The weakness is that it cannot see the business context.
Step 4: Layer Business Rules on Top
# SLA breach
df_db["SLA_Risk"] = np.where(
df_db["DurationSeconds"] > 3600, "HIGH", "NORMAL"
)
# Failure risk patterns
df_db["PredictedFailureRisk"] = np.where(
(df_db["GrowthRate_Capped"] > 3) |
((df_db["IO_Throughput_MBPS"] < 20) & (df_db["DurationSeconds"] > 5)),
"HIGH", "LOW"
)
# LSN chain integrity
df_logs = df_db[df_db["BackupType"] == "LOG"].copy()
df_logs["PrevLastLSN"] = df_logs.groupby("DatabaseName")["LastLSN"].shift(1)
df_logs["BrokenLSNChain"] = np.where(
df_logs["PrevLastLSN"].isna(), 0,
np.where(np.isclose(df_logs["FirstLSN"], df_logs["PrevLastLSN"]), 0, 1)
)
\
Why the Model Is Not Enough
This makes Isolation Forest appealing, and it may be tempting to only use this and remove the rule-based checks as well. I gave this a go early on and soon found the holes.
But you think it is clear in the model, which records a backup that takes 61 minutes once every day–because, let me tell you otherwise: In the eyes of the machine, this looks completely normal. Though it still violates a 1-hour SLA. Duration, size, and throughput have no correlation with a broken log backup chain — the numbers show correctly, but you cannot restore to the specific point in time anymore. The model would never catch either of these.
Step 5: Prioritize and Route Alerts
The inclusion of two things(Root Cause Hints and Alert Priority) makes operational rather than merely analytical use of this output.
Here, the root cause function relates what you observed to the explanation that could create conditions for it. The order of the checks is important — the most specific has precedence over general ones:
\
def root_cause(row):
if row["Compression_Ratio"] < 1:
return "Compression may be disabled"
if row["IO_Risk"] == "HIGH":
return "I/O bottleneck: throughput dropped while size is stable"
if (row["IO_Throughput_MBPS"] < 20) and (row["DurationSeconds"] > 5):
return "Possible storage bottleneck"
if row["ThroughputDeviation"] < -0.4:
return "Significant throughput degradation"
if row["GrowthRate"] > 3:
return "Backup size increase detected"
return "No obvious issue"
\ If more than one signal fires, alert priority ensures that the most critical problems are reached first. Recoverability issue: It is actually imminent when the LSN chain is broken. A pattern that triggers an Isolation Forest anomaly may simply be an unusual but harmless incident.
\
def alert_priority(row):
if row["BrokenLSNChain"] == 1:
return "CRITICAL"
if row["SLA_Risk"] == "HIGH":
return "HIGH"
if row["PredictedFailureRisk"] == "HIGH":
return "HIGH"
if row["IO_Risk"] == "HIGH":
return "MEDIUM"
if row["Anomaly"] == -1:
return "LOW"
return "INFO"
The final output is sorted by priority, so critical items always appear at the top:
anomalies = df_db[
(df_db["Anomaly"] == -1) |
(df_db["SLA_Risk"] == "HIGH") |
(df_db["PredictedFailureRisk"] == "HIGH") |
(df_db["BrokenLSNChain"] == 1) |
(df_db["IO_Risk"] == "HIGH")
]
priority_order = {"CRITICAL": 0, "HIGH": 1, "MEDIUM": 2, "LOW": 3, "INFO": 4}
final_df["PrioritySort"] = final_df["AlertPriority"].map(priority_order)
final_df = final_df.sort_values(["PrioritySort", "BackupStartDate"])
\
Real-World Output
Here is a sample output of the backup monitoring system:
The report consists of anomalous SQL Server backups with severe throughput degradation and unusually long-duration backups per GB that may suggest a performance (or storage) bottleneck.
In Closing
Here, the goal is not to find a replacement for existing monitoring tools but to bridge the gap between "backup has succeeded and backup is healthy. The majority of backup failures do not start with a failure. They start with performance drift that is evident in the data weeks before it manifests as an incident.
Setting up the telemetry table takes five minutes. The Python analysis executes in seconds. In fact, they together form a metric that I have struggled to reproduce using threshold-based alerting in isolation, but which gives me a level of operational visibility. This leads to the effectiveness of combining a model with rules — the model catches things you never thought to write rules for, and those rules catch what your model is blind to. That mix has proven to be more reliable than either method alone.
- Full implementation: GitHub - SQL Backup Intelligence
- Isolation Forest: Scikit-Learn Documentation
- Time-series anomaly detection: Research on Isolation-based Anomaly Detection
\ \
This content originally appeared on HackerNoon and was authored by Deepesh Dhake (SQL Server DBA)
Deepesh Dhake (SQL Server DBA) | Sciencx (2026-05-22T13:01:15+00:00) A Hybrid ML and Rule-Based Approach to SQL Backup Monitoring. Retrieved from https://www.scien.cx/2026/05/22/a-hybrid-ml-and-rule-based-approach-to-sql-backup-monitoring/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.