Spotting Unusually Small Backups Against Each Database’s Average

One unexpectedly small backup is a reason to inspect the context. Unusually small backups can reflect missing data, but they can also reflect legitimate cleanup or changed compression. Compare the same size definition and backup type before raising an alarm.

Build a History Set for Unusually Small Backups

Use full database backups rather than mixing log, differential, and full sizes. Keep source database identity, backup dates, and copy-only status available. A separate ad hoc backup can belong to a different destination or operational purpose even when the database name matches.

I choose the uncompressed backup_size for the primary comparison. That avoids treating a compression improvement as an immediate reduction in the logical backup content. Keep compressed_backup_size alongside it so the stored-file difference remains explainable. Neither number alone proves the application loaded every expected record.

SELECT backup_set_id,database_name,backup_start_date,backup_finish_date,
       backup_size,compressed_backup_size,is_copy_only
INTO #BackupSizeHistory
FROM msdb.dbo.backupset
WHERE type='D' AND backup_finish_date IS NOT NULL
 AND backup_start_date>=DATEADD(day,-30,GETDATE());

The date range is an example review horizon. Check the retained history before interpreting it as thirty days of coverage. Purged records or an infrequent full-backup schedule can leave only a few observations. The report should expose that sample size rather than hiding it behind an average.

Calculate Each Database's Average

A window function attaches the database-specific average to every backup row. The outer query then filters that calculated value. This separates aggregation from row filtering cleanly. COUNT supplies context for databases with very little retained history.

WITH Compared AS
(
 SELECT *,AVG(CONVERT(decimal(28,2),backup_size)) OVER
   (PARTITION BY database_name) AS AverageBackupBytes,
 COUNT(*) OVER(PARTITION BY database_name) AS HistoryCount
 FROM #BackupSizeHistory
)
SELECT database_name,backup_start_date,backup_size,
       compressed_backup_size,AverageBackupBytes,HistoryCount
FROM Compared
WHERE backup_size<AverageBackupBytes*0.5
ORDER BY database_name,backup_start_date;

The half-average threshold is a screening rule, not a SQL definition of failure. Choose a threshold and minimum sample appropriate to the workload. A rapidly growing database or a recent deliberate shrink can make a whole-window average less representative of the newest operation.

This average includes the candidate itself. An unusually small candidate therefore pulls the average down slightly. That is acceptable for a simple inventory if explained, but a previous-history baseline offers a more direct comparison when alerting on the latest backup.

Keep Aggregates Out of Direct Row WHERE Logic

A direct expression such as WHERE backup_size less than AVG(backup_size) mixes a row predicate with an aggregate at the wrong query level. SQL Server rejects that form with error 147. The fix is to calculate the aggregate in another query level or use HAVING for a grouped result.

WITH Averages AS
(
 SELECT database_name,AVG(CONVERT(decimal(28,2),backup_size)) AS AverageBytes
 FROM #BackupSizeHistory GROUP BY database_name
 HAVING COUNT(*)>=5
)
SELECT b.database_name,b.backup_start_date,b.backup_size,a.AverageBytes
FROM #BackupSizeHistory b
JOIN Averages a ON a.database_name=b.database_name
WHERE b.backup_size<a.AverageBytes*0.5;

HAVING here filters database groups by their retained observation count. The outer WHERE compares individual backup rows with the already computed average. Keep those responsibilities distinct. Do not move a row-level condition into HAVING without checking whether the result still represents individual backups.

Compare Unusually Small Backups With Earlier History

A preceding window excludes the current candidate. The following expression uses up to ten earlier full backups for each database. The first backup has no prior baseline, while early rows have a small sample. Require enough earlier observations before producing an alert.

WITH Prior AS
(
 SELECT *,AVG(CONVERT(decimal(28,2),backup_size)) OVER
 (PARTITION BY database_name ORDER BY backup_start_date,backup_set_id
  ROWS BETWEEN 10 PRECEDING AND 1 PRECEDING) AS PriorAverageBytes,
 COUNT(*) OVER
 (PARTITION BY database_name ORDER BY backup_start_date,backup_set_id
  ROWS BETWEEN 10 PRECEDING AND 1 PRECEDING) AS PriorCount
 FROM #BackupSizeHistory
)
SELECT database_name,backup_start_date,backup_size,PriorAverageBytes,PriorCount
FROM Prior WHERE PriorCount>=5 AND backup_size<PriorAverageBytes*0.5
ORDER BY database_name,backup_start_date;

Backup_set_id supplies a stable tie-breaker when dates match. This is an operation-count window rather than a fixed time interval. If the schedule changes, ten backups can describe a very different span of business activity. Keep that context in the alert design.

Trace Unusually Small Backups to Application Data

A flagged backup can follow a failed import, an unexpected truncation, or an operation against the wrong database. Compare the expected load outcome with application records and row-level evidence. The size report identifies where to look; it cannot identify which rows are missing.

I check the database identity and recent change history before inferring loss. Which approved operation occurred between the previous backup and this one? A planned retention purge can explain the decrease without any backup fault. An unplanned loading gap needs its own investigation and recovery decision.

Do not restore over the live database merely because the backup is smaller. Preserve evidence, verify the actual data issue, and follow the approved recovery process if needed. Size screening should make the next diagnostic step clearer, not trigger an automatic destructive action.

Explain Compression and Content Changes

A more compressible dataset can produce a much smaller stored backup while its uncompressed backup size remains comparable. Encryption and compression configuration changes also deserve review. Compare both byte quantities before interpreting the physical file size.

A real cleanup can reduce both quantities. Deleting unused application history, rebuilding storage, or changing the amount of allocated data can alter backup size legitimately. The exact effect depends on the operation and database layout. Keep the known change record beside the alert.

A database name can also be reused or attached to a different restored copy. Historical grouping by name then spans different contexts. Retain server identity and backup-set identifiers in exported records so the reviewer can distinguish a naming change from a content change.

Keep Alert Coverage Honest

Missing retained history means missing comparison coverage, not zero-size backups. A failed operation can also lack a completed backupset row. Pair successful-backup size screening with job failures and backup freshness monitoring. Those checks answer different questions.

Record the baseline period, observation count, size definition, and threshold with each alert. A small backup is an inspection ticket, not a verdict. Readers should know exactly which earlier records made it look unusual.

Verify the Backup Separately

Restore testing and backup verification remain part of the normal recovery process. A normal size cannot prove recoverability, and an unusual size cannot prove corruption. Keep the size trend as one signal among the established checks.

Test the report on known legitimate cleanup periods and known load changes before automating notifications. Tune its sample requirement to avoid misleading alerts from sparse history. Then retain the investigation outcome so later reviewers can distinguish a repeated normal pattern from a new unexplained decrease.

An investigation record should retain the alert's actual backup-set identifier. Later history cleanup can remove the row that originally triggered the question. Save the comparison values and the reviewing decision before that happens. This keeps a resolved alert understandable without requiring the current msdb tables to preserve every past observation indefinitely. Unusually small backups should retain their baseline and investigation outcome. Review that history before turning repeated unusually small backups into an automated claim of data loss.

Related reading on this blog: Small Backup for Large Database and Finding Compression Ratio of Backup.

An unusually small backup is not proof of missing data, it is a comparison result that needs operational and recovery context.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.