Backup Retention: How Long to Keep What

Deleting old backup files is easy until the file you remove is the base for a restore you still need. Backup retention should begin with recoverable time points and obligations, then work backward to the full, differential, and log files that make those restores possible.

Define the Recovery Window First

A backup retention policy answers two separate questions. How far back must the organization be able to restore, and how much recent work can it afford to lose? The first determines the oldest recoverable point. The second influences backup frequency, especially transaction log frequency under the full recovery model. Neither answer is a universal number of days. Record the business owner, recovery objective, and tested restore process for each database.

I start with a timeline rather than a folder size. Pick an old date inside the promised window and list every file required to restore to it. Then pick a point near the present and repeat. If either path is missing a base backup or a log segment, a large collection of backup files still fails the promise.

Keep the Full Base Long Enough

A full database backup is the starting point for a common restore sequence. Keep a usable full backup that starts before the oldest recovery point you promise, plus the dependent backups that carry it forward. A policy that deletes a full backup simply because its file age exceeds a threshold can strand newer differentials. Retention therefore needs to understand relationships, not just timestamps.

A weekly full backup is one possible schedule, not a recommendation for every workload. Large databases and slow restore devices change the calculation. Test how long a real full restore takes and whether the backup exists in a separate failure domain. If the only retained copy sits beside the database files, a storage incident can remove both the data and the supposed recovery path.

Protect Differential Dependencies

A differential backup contains changes since its base full backup. To use a differential, restore the matching full first, then the selected differential. Later differentials do not depend on earlier differentials, but they do depend on that base. A copy-only full backup does not reset the usual differential base. Your deletion job must know which full backup each retained differential needs.

Differentials can reduce the number of log backups applied during a restore, which can shorten recovery. They also grow as more extents change after the base. Observe actual backup sizes and restore times before choosing a cadence. I keep the dependency explanation beside the cleanup policy so a future storage review does not treat a differential as a standalone daily backup.

Keep Log Backups in an Unbroken Sequence

Under the full recovery model, transaction log backups support point in time recovery after a suitable data backup. Restoring through a time period requires an unbroken sequence of log backups covering that period. Removing one required log backup breaks the path beyond that gap. A later full backup creates a new usable starting point, but it does not repair the lost earlier path.

Log files can be individually small and collectively expensive. Size the destination for peak write periods, not only a quiet week. If a backup fails, alert on it promptly and investigate the resulting chain before cleanup resumes. Do not infer recoverability from a successful backup job alone. The question is whether the selected full, optional differential, and required log sequence can all be restored.

SELECT database_name, type, backup_start_date,
       backup_finish_date, first_lsn, last_lsn,
       database_backup_lsn
FROM msdb.dbo.backupset
WHERE database_name = DB_NAME()
ORDER BY backup_finish_date DESC;

Separate Legal Backup Retention From Fast Recovery

Legal, contractual, or regulatory retention can be much longer than the operational recovery window. Ask the data owner and legal team for the exact record category, duration, jurisdiction, deletion rule, and access requirement. A long term archive of selected records is not necessarily the same as keeping every daily database backup for years. An encrypted backup can also become unusable if the certificate or key needed for restore is lost.

Document the difference between a quickly restorable copy and a deep archive. The first is designed for recovery time. The second is designed for retention and controlled access. Both need periodic readability checks and a clear owner. If a legal hold applies, suspend deletion for the affected material through a deliberate process rather than relying on a vague note in a job description.

Account for Backup Retention Storage and Security

Estimate retention cost using compressed backup sizes, schedule frequency, growth, redundancy, and the time needed to copy files off the server. Add capacity for overlap during a new full backup and for one failed cleanup cycle. Cheap storage does not excuse unmanaged access. Backups can contain sensitive data, so encrypt them where required, restrict access, and protect the restore keys independently.

This query reports the sizes SQL Server recorded for recent backups of the current database. It supports planning, but it does not prove that the physical file still exists or can be read. Use inventory from the storage system to reconcile retained files with backup history.

SELECT TOP (30) backup_finish_date, type,
       backup_size / 1048576.0 AS backup_mb,
       compressed_backup_size / 1048576.0 AS compressed_mb
FROM msdb.dbo.backupset
WHERE database_name = DB_NAME()
ORDER BY backup_finish_date DESC;

Test the Backup Retention Rule Against Restores

Before enabling automatic deletion, simulate it against a representative backup history. Select restore targets at the oldest promised point, across a differential base change, and just before the newest log backup. Verify that every required file survives. Then perform real restores in an isolated environment and run integrity checks on the restored database. Repeat after schedule changes or a move to new storage.

Ask a simple question of every cleanup rule: which promised restore point becomes impossible if this file disappears? If the answer is unknown, do not delete by age alone. Keep a written exception process for delayed backups, failed copies, and legal holds. Retention is successful when the team can restore within the promised window and can explain exactly when older material is removed.

A retention rule also needs a deletion audit. Record which backup files were removed, when, and under which policy version. I sample the oldest surviving restore path after each policy change. Which retained backup would you choose for a restore request near the window boundary? If the team cannot answer quickly, the rule needs another test.

Related reading on this blog: Full, Differential and Log Backups: A Practical Guide and Check Backup Reliability.

Backup retention is not file housekeeping, it is preservation of promised restore paths.

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