How to Build a SQL Server Backup Plan: RPO, RTO, Schedules and Testing

Most SQL Server environments have backups. Far fewer have a backup plan. The difference shows up on the worst day: the backup job ran every night, but nobody knows how long a restore takes, the log backups were never tested, or the only copy sat on the same server that just failed.

A real backup plan is a promise to the business about two things: how much data you can lose and how long you’ll be down. This article shows how to build that plan step by step: setting the targets, choosing backup types and schedules, storing backups safely, sizing retention, and proving that it all works.

Start With RPO and RTO, Not With the Schedule

Before choosing any schedule, agree on two numbers for each database with the business owner:

• RPO (Recovery Point Objective): the maximum amount of data, measured in time, the business can accept losing. An RPO of 15 minutes means that after a disaster, at most the last 15 minutes of work may be lost.
• RTO (Recovery Time Objective): the maximum acceptable downtime, from the moment of failure until the database is back in service.

RPO determines how often you take log backups. RTO determines how fast the restore must be, which affects backup type mix, storage speed, compression, and whether backups alone are enough or you also need high availability.

Not every database needs the same targets. A practical approach is to group databases into tiers:

The Recovery Model Decides What’s Possible

Point-in-time recovery and an RPO measured in minutes are only possible with the full (or bulk-logged) recovery model and regular log backups. Under the simple recovery model there are no log backups: you can only go back to the last full or differential backup, so the RPO equals the gap between those backups.

Check every database’s recovery model against its tier. A critical database in simple recovery can’t meet a 15-minute RPO, however often you take full backups. A database in full recovery without log backups is a different problem: its log keeps growing until the disk fills. We explain both in SQL Server Simple Recovery Model: When to Use It and When Not To and SQL Server Transaction Log Too Big?

Backup Types and How They Work Together

• Full backup: a complete copy of the database, plus enough log to make it consistent. Every restore sequence starts with one.
• Differential backup: every extent that has changed since the last full backup. Differentials are cumulative, so you only ever need the latest one, but they grow every day until the next full backup.
• Transaction log backup: all log records since the previous log backup. Log backups form a chain, and every link is needed to restore to a point in time. They also allow the log to be truncated and reused.
• Copy-only backup: a full or log backup that doesn’t affect the normal backup sequence. Use it for ad hoc copies, for example to refresh a test environment, so you don’t break the differential base or log chain.

Building the Schedule

For most of the business databases we manage, our starting point is:

• Full backup: once a week, in the quietest window (for example, Sunday night).
• Differential backup: every day, on the other nights.
• Log backup: every 15 minutes, around the clock.

With this schedule, a failure on Thursday at 14:20 is restored with Sunday’s full backup, Wednesday night’s differential, and the log backups taken since then, plus a tail-log backup if the log file is still accessible. In the worst case you lose up to 15 minutes; with a successful tail-log backup, often nothing.

Adjust it to the tier and the workload:

• Tighter RPO: shorten the log backup interval to 5 minutes or even 1 minute. Log backups of a quiet database are small, so frequent backups cost little.
• Tighter RTO: the longer the chain of log backups to apply, the longer the restore. Daily full backups, or extra differentials during the day, shorten the chain.
• Write-heavy databases: differentials can grow close to the size of a full backup by the end of the week. If that happens, a daily full backup is simpler.
• Small databases: a daily full backup plus log backups is often the simplest plan.

Don’t forget the system databases. Back up master and msdb regularly (logins, jobs, backup history and SSIS packages live there) and model if you’ve customized it.

Backup commands with the right options

-- Weekly full
BACKUP DATABASE [SalesDB]
TO DISK = N'\\backupsrv\sql\SalesDB\SalesDB_FULL_20261004.bak'
WITH COMPRESSION, CHECKSUM, STATS = 10;

-- Daily differential
BACKUP DATABASE [SalesDB]
TO DISK = N'\\backupsrv\sql\SalesDB\SalesDB_DIFF_20261007.bak'
WITH DIFFERENTIAL, COMPRESSION, CHECKSUM;

-- Log backup every 15 minutes
BACKUP LOG [SalesDB]
TO DISK = N'\\backupsrv\sql\SalesDB\SalesDB_LOG_20261007_1415.trn'
WITH COMPRESSION, CHECKSUM;

CHECKSUM verifies page checksums while the backup is read and stops the backup if it finds a damaged page. COMPRESSION usually makes backups both smaller and faster, at the cost of some CPU; see SQL Server Backup Compression: Benefits, CPU Cost and Defaults. Use one file per backup with a date-time stamp in the name, rather than appending many backups to the same file.

Always On: Where Should Backups Run?

In an availability group you can move backups to a secondary replica to take the load off the primary. Configure which replicas are preferred with the availability group’s backup preference and priority settings, create the backup jobs on every replica, and in the job check sys.fn_hadr_backup_is_preferred_replica() so only the preferred replica actually takes the backup.

Know the limits. Up to SQL Server 2022, a secondary replica supports only copy-only full backups and regular log backups; differential backups aren’t supported there. SQL Server 2025 adds full and differential backups on secondary replicas. The log chain stays consistent whichever replica takes the log backup, but the backup history is recorded in the msdb of the replica that took it, so monitoring has to look at every replica. For the architecture, see What Is SQL Server Always On? High availability doesn’t replace backups: an accidental DELETE is replicated to every secondary in seconds.

Where Backups Live: Off the Server, and Out of Reach

A backup on the same disk, or even the same server, as the database protects against very little. A widely used rule of thumb is 3-2-1: three copies of your data, on two different types of storage, with one copy off-site. In practice:

• Write backups to a separate backup server or network share, not to the database server’s own disks.
• Copy them on to a second location: another data center, cloud storage (SQL Server can back up directly to Azure Blob Storage with BACKUP ... TO URL), or tape.
• Protect at least one copy against ransomware and accidental deletion: immutable storage, or storage that the SQL Server service account and domain administrators can’t delete.

Encrypting backups

Retention: How Long to Keep Backups

Retention isn’t just a disk space decision. Three things set the minimum:

• How far back you might need to go: a data error noticed after a week needs a backup older than a week.
• Integrity checks: the gap between two successful DBCC CHECKDB runs must be shorter than your retention, or corruption may be in every backup you still have. See DBCC CHECKDB in SQL Server.
• Legal and contractual requirements: regulations, audits and contracts may require longer retention for some data.

Test Restores: The Only Proof That a Backup Works

A successful backup job only proves that a file was written. RESTORE VERIFYONLY checks that the backup is complete and readable, but it doesn’t prove the data inside is consistent. The only real proof is an actual restore:

• Restore regularly (at least monthly for critical databases) to a separate server, including a point-in-time restore using log backups.
• Run DBCC CHECKDB on the restored copy to prove the backup contains a consistent database.
• Time it. If the restore takes four hours and the RTO is one hour, the plan doesn’t meet its target, however well the backups run.
• Write the runbook: who restores, from where, in which order, and how applications are reconnected. The middle of an outage is the wrong time to work it out.

For the restore steps themselves, see our SQL Server Restore guide.

Monitoring: Is Every Database Actually Backed Up?

Jobs get disabled, fail silently, or skip a database that was added last month. This query shows the latest full, differential and log backup for every database on the instance:

SELECT d.name,
       d.recovery_model_desc,
       MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS last_full,
       MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS last_diff,
       MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS last_log
FROM sys.databases AS d
LEFT JOIN msdb.dbo.backupset AS b
       ON b.database_name = d.name
WHERE d.database_id <> 2   -- tempdb
GROUP BY d.name, d.recovery_model_desc
ORDER BY last_full;

Look for databases with no full backup, a full backup older than your schedule allows, and databases in full recovery whose last_log is older than the RPO. Turn this into a daily check, or an alert. In availability groups, run it on every replica, since each replica’s msdb only records the backups it took. Backup job failures should also notify the DBA team through SQL Server Agent.

Backup Plan Checklist

1. RPO and RTO agreed and written down for every database.
2. Recovery model matches the tier.
3. Full, differential and log backup schedule meets the RPO and RTO.
4. System databases (master, msdb, model) included.
5. Backups taken with CHECKSUM and, where it helps, COMPRESSION.
6. Backups written off the database server, with an off-site copy and at least one copy protected from deletion.
7. Encryption certificates backed up and stored separately.
8. Retention covers the CHECKDB interval and legal requirements; cleanup removes whole sets.
9. Regular test restores, timed against the RTO, with CHECKDB on the restored copy.
10. Daily monitoring of last backup times and alerts on job failures.

Frequently Asked Questions

This schedule has already been tested in a real incident. At a manufacturing customer, an obsolete backup was accidentally restored over the live production database. Because there was a verified chain of weekly full, daily differential and 15-minute log backups, we limited the data loss to one minute. Read the full story: Recovering from an Accidental SQL Server Restore with a 1-Minute RPO.