Welcome to another recap of the Stedman SQL Podcast. In Season 2, Episode 26, I discussed one of the most important topics in SQL Server management—backup types and retention periods. Whether you’re a DBA, developer, or IT manager, understanding how backups work and how long to keep them is critical to protecting your data, meeting compliance requirements, and ensuring long-term recovery options.
October Promotion: Free Year of Monitoring
Before diving into today’s topic, a quick note—during October, anyone who signs up for a SQL Server Performance Assessment or SQL Server Health Assessment will receive one free year of our monitoring service. This offer is available only through the end of the month, so it’s a great time to take advantage of it.
Why Backups and Retention Matter
Backups are one of the most essential responsibilities of a DBA. Yet too often, they’re either incomplete, poorly configured, or not tested. The result? You might think you’re protected—until the day disaster strikes.
We frequently see SQL Servers with only a few days of full backups being kept because of disk space limitations. For example, one recent client had just five days’ worth of full backups. We redesigned their backup strategy to achieve six weeks of point-in-time recovery using roughly the same amount of disk space—simply by combining full, differential, and transaction log backups.
Understanding SQL Server Backup Types
Full Backups
A full backup captures the entire database. Without compression, it’s roughly the same size as the database itself. With backup compression enabled, the file size can be reduced to 40–80% of the original. This is your baseline for all other backups.
Differential Backups
A differential backup includes only the data pages that have changed since the last full backup. These backups grow in size as more changes occur but are much faster and smaller than full backups. For many systems, taking a full backup weekly and daily differential backups is an efficient strategy.
Transaction Log Backups
A transaction log backup records every change made since the last log backup. These are required for point-in-time recovery in the full or bulk-logged recovery models. Running frequent log backups—every 5 to 10 minutes—is far better than large, infrequent ones. Without them, transaction log files can grow endlessly and consume all available disk space.
File and Filegroup Backups
For extremely large databases, file or filegroup backups allow selective backup of specific portions of the database. While less common, they can be valuable for very large or specialized environments.
Choosing a Recovery Model
- Full Recovery Model: Supports point-in-time recovery. Requires regular log backups.
- Bulk Logged Recovery Model: Similar to full, but can skip logging for certain bulk operations.
- Simple Recovery Model: Automatically reclaims log space after transactions complete. Best for databases where point-in-time recovery is not required.
Determining Retention Periods
The right retention policy depends on both business needs and regulatory requirements. Here are three common categories:
- Short-Term (Days to Weeks): Useful for operational recovery—accidental deletes, dropped tables, or bad updates.
- Medium-Term (Weeks to Months): Supports auditing, compliance, and internal investigations.
- Long-Term (Years): Meets legal and regulatory mandates (HIPAA, GDPR, SOX, PCI).
For example, a hospital may need to retain backups for seven years to satisfy HIPAA requirements. A financial institution may need to preserve monthly or quarterly backups for tax audits.
Cost-Effective Long-Term Storage
Storing backups for years doesn’t have to be expensive. Many organizations now use Azure Blob Storage or AWS Glacier for low-cost cold storage. Historically, we even helped clients rotate external hard drives weekly to safe deposit boxes—thankfully, the cloud makes this much easier today.
Building a Solid Backup Strategy
A proven strategy looks like this:
- Full Backups: Weekly
- Differential Backups: Daily
- Transaction Log Backups: Every 5–10 minutes
This combination allows for fast restores and longer retention while using minimal disk space. For added protection, we recommend:
- Immutable storage (so backups can’t be altered by ransomware)
- Off-server storage (never store backups on the same machine as your database)
- Regular DBCC CHECKDB checks to ensure you’re not backing up corrupt databases
The Importance of Testing Your Backups
If you’ve never tested your backups, you don’t really know if they work. I call this the “Schrödinger’s Cat Backup” scenario—you won’t know if it’s alive or dead until you open the box. Schedule regular restore tests, whether in a test environment or by restoring a copy with a different name on production.
Common Backup Pitfalls
- Full recovery model without log backups – leads to massive log growth.
- No offsite or immutable storage – leaves you vulnerable to ransomware or physical damage.
- Unmonitored or untested backups – you may discover failures only when it’s too late.
Final Thoughts
Your SQL Server backup and retention strategy should align with your business needs, storage capacity, and compliance requirements. Combine full, differential, and transaction log backups to optimize space while maintaining full recoverability.
At Stedman Solutions, we help clients design and monitor effective backup strategies every day through our SQL Server Managed Services. We also offer detailed training in our SQL Server Backup and Recovery Course, available at stedman.us/backup.
To learn more or get expert help with your backup and recovery strategy, contact us.
You can also watch or listen to all podcast episodes at https://stedman.us/podcast.
Watch Episode 26 Now
Want to be a guest on the Podcast, see how at https://stedmansolutions.com/sql-server-podcast-guest/
Check out all Season 3 episodes: https://stedmansolutions.com/home/sql-server-podcast/season-3/
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!