SQL Server Backup & Restore Strategies

Why a Maintenance Plan Is Not a Strategy

A default SQL Server maintenance plan that runs a nightly full backup satisfies exactly one requirement: you can restore to last night. For most production databases, losing up to 24 hours of data is not an acceptable outcome, and "restore to last night" is not a strategy — it is the absence of one. A real backup strategy starts from two numbers the business gives you, not from what the Maintenance Plan Wizard defaults to.

Recovery Point Objective (RPO) is how much data you are allowed to lose, measured in time. Recovery Time Objective (RTO) is how long you are allowed to be down while recovering. Every decision in this article — recovery model, backup types, frequency, retention, storage location — is a mechanism for hitting an RPO/RTO pair, not a best practice to apply uniformly. A reporting database that reloads from a warehouse nightly might genuinely be fine with Simple recovery and a daily full backup. An OLTP order system with an RPO of five minutes cannot use that same configuration no matter how well it's tuned.

Recovery Models and the RPO Ceiling

The recovery model is not a performance setting — it is a hard ceiling on your best possible RPO, decided before you write a single backup job.

Simple recovery truncates the transaction log automatically at each checkpoint, reclaiming log space as soon as SQL Server no longer needs the records for crash recovery. This means log records for committed transactions are gone shortly after they commit. You cannot take a transaction log backup under Simple recovery — there is nothing durable to back up between checkpoints — so your only recovery points are your full and differential backups. If a full backup runs at 2 AM and the server fails at 4 PM, you lose everything since 2 AM. There is no point-in-time restore available under Simple recovery, full stop.

Full recovery keeps every log record since the last log backup, and only truncates the log when a log backup runs (or, more precisely, marks that portion of the log reusable). This is what makes point-in-time restore possible: as long as your log backup chain is unbroken, you can replay the log up to any point in time, including seconds before a failure, using a tail-log backup. Full recovery is the model for any database where RPO is measured in minutes rather than hours.

Bulk-logged recovery is a special case of Full recovery that minimally logs certain bulk operations (SELECT INTO, BULK INSERT, index rebuilds, and similar) to reduce log volume during large loads. You still take log backups exactly as under Full recovery, and the log is still truncated by them. The catch: if a log backup contains a minimally-logged operation, you can only restore that backup in its entirety — you lose the ability to stop at an arbitrary point in time within it. You can only recover to the end of that specific log backup, or to a point in a different (fully-logged) backup in the chain. Most teams run Full recovery all the time and only switch to Bulk-Logged temporarily around a known bulk load window, then switch back.

The practical rule: if the business needs point-in-time restore, the database must be in Full recovery, and you must be taking log backups. Nothing else in this article matters if that foundation is wrong.

Full, Differential, and Log Backups

Each backup type answers a different question about what's changed.

A full backup copies every data page (and enough of the log to make the backup transactionally consistent) as of the moment the backup completes. It is self-contained and is the base every differential and log backup chain is built on.

BACKUP DATABASE [OrdersDB]
TO DISK = 'D:\Backups\OrdersDB_Full.bak'
WITH COMPRESSION, CHECKSUM, INIT;

A differential backup copies only the data extents that have changed since the last full backup, tracked internally via a Differential Changed Map (DCM). It is faster to take and faster to restore than replaying a long log chain, because restoring a differential collapses "everything since the full" into one file instead of dozens of log backups.

BACKUP DATABASE [OrdersDB]
TO DISK = 'D:\Backups\OrdersDB_Diff.bak'
WITH DIFFERENTIAL, COMPRESSION, CHECKSUM;

A transaction log backup copies the active portion of the log since the last log backup (or since the database entered Full/Bulk-Logged recovery, for the first one) and then truncates it, freeing that space for reuse. Log backups are cumulative in sequence, not in content — each one picks up exactly where the previous one left off, and you need all of them in order to replay forward.

BACKUP LOG [OrdersDB]
TO DISK = 'D:\Backups\OrdersDB_Log.trn'
WITH COMPRESSION, CHECKSUM;

A typical schedule combines all three: weekly full, nightly differential, and log backups every 5-15 minutes depending on RPO. This keeps the restore chain short (one full, one differential, a handful of log files) while still allowing granular point-in-time recovery.

The Differential vs. Log Backup Confusion

This is the single most common misunderstanding in SQL Server backup design, and it has real consequences for both restore correctness and storage planning.

A differential backup always captures changes since the last full backup — never since the last differential. The DCM bitmap that tracks changed extents is only reset by a full backup. If you take a full backup on Sunday and a differential every night, Monday's differential contains Monday's changes, but Tuesday's differential contains both Monday's and Tuesday's changes, Wednesday's contains Monday through Wednesday, and so on, growing larger each day until the next full backup resets it. This means you only ever need the most recent differential plus the full to restore — you never chain multiple differentials together. If you find yourself trying to restore Sunday's full, then Monday's differential, then Tuesday's differential in sequence, that is wrong; you'd restore Sunday's full and only Tuesday's differential.

A transaction log backup, by contrast, only captures records since the previous log backup, and every log backup in the chain since the last full or differential is required, in order, with none missing. Skip one log backup file during a restore and the chain breaks — you cannot restore log backup #7 without having successfully restored #6 first, because log records apply sequentially against the LSN (Log Sequence Number) baseline left by the prior restore.

The operational consequence: deleting "old" differential backups is usually safe once a newer one exists (you only need the latest), but deleting an "old" log backup in the middle of an unbroken chain destroys your ability to restore past that point, even if newer log backups exist after it. Retention jobs that indiscriminately purge files older than N days are a common way teams accidentally break their own recovery chain.

Point-in-Time Restore Mechanics

Restoring to a point in time is a sequence, not a single command. Every restore statement except the last is issued WITH NORECOVERY, which leaves the database in a restoring state so more backups can be applied on top of it. The final statement uses WITH RECOVERY, which rolls back any uncommitted transactions and brings the database online — after that point, no further backups can be applied without starting over from the full backup.

The general order is: full backup, then the most recent differential taken after that full (if any), then every log backup taken after that differential, in sequence, up to and including the one that covers your target time.

-- 1. Restore the full backup, database stays offline for more restores
RESTORE DATABASE [OrdersDB]
FROM DISK = 'D:\Backups\OrdersDB_Full.bak'
WITH NORECOVERY, REPLACE;

-- 2. Restore the latest differential taken after that full
RESTORE DATABASE [OrdersDB]
FROM DISK = 'D:\Backups\OrdersDB_Diff.bak'
WITH NORECOVERY;

-- 3. Restore each log backup taken after that differential, in order
RESTORE LOG [OrdersDB]
FROM DISK = 'D:\Backups\OrdersDB_Log1.trn'
WITH NORECOVERY;

RESTORE LOG [OrdersDB]
FROM DISK = 'D:\Backups\OrdersDB_Log2.trn'
WITH NORECOVERY;

-- 4. Restore the final log backup, stopping at the exact point needed,
--    and bring the database online
RESTORE LOG [OrdersDB]
FROM DISK = 'D:\Backups\OrdersDB_Log3.trn'
WITH STOPAT = '2026-07-21T14:32:00', RECOVERY;

If the failure happens mid-day and the log file on the failed server is still readable, take a tail-log backup first, before restoring anything, so you don't lose the gap between the last scheduled log backup and the moment of failure:

BACKUP LOG [OrdersDB]
TO DISK = 'D:\Backups\OrdersDB_TailLog.trn'
WITH NORECOVERY;

This backup itself puts the source database into a restoring state (that's what WITH NORECOVERY on a BACKUP statement does), and its contents become the final file in the restore sequence above, letting you recover to the actual moment of failure rather than to the last scheduled log backup. Skipping the tail-log backup is one of the most common reasons a "5-minute RPO" strategy actually loses more than 5 minutes of data during a real incident — the log backup job that was supposed to run at 14:35 never got the chance to fire before the server went down.

Designing Around RPO and RTO

Once the recovery model is fixed, three variables are tuned to hit the target numbers: backup frequency, retention, and restore chain length.

Frequency is driven by RPO. An RPO of 15 minutes means log backups every 15 minutes or less — not "every 15 minutes on average," but a hard interval, because the gap between two log backups is exactly how much data you can lose if failure lands right before the next one fires. Full and differential frequency matter less for RPO (log backups cover that gap) and more for RTO and storage efficiency.

Restore chain length drives RTO. Restoring one full backup plus one differential plus twelve 15-minute log backups covering a 3-hour gap is fast. Restoring one full backup plus 288 log backups covering two days because there's no differential in between is slow, and each additional file in the chain is another opportunity for a missing or corrupt file to stall the whole recovery. This is the actual argument for differential backups: they don't improve RPO at all, they exist purely to bound RTO by keeping the number of files you must apply small.

Retention is driven by compliance and by how far back you need to be able to restore, not by disk space convenience. A common pattern is 2-4 weeks of full backups retained locally for fast operational restores, with monthly or quarterly fulls retained for 1-7 years offsite for compliance, and log backups retained only as long as needed to bridge between full/differential retention points. Retention policy has to be checked against the differential/log chain rule from the previous section — purging a log backup that's still inside an active chain, even if it's "old," breaks recovery for every point in time after it until the next full backup.

Every strategy should be validated against both numbers explicitly: run the actual restore sequence in a lower environment and time it against the RTO target, and confirm the gap between backups never exceeds the RPO target even during backup job failures (which means alerting on missed or failed backup jobs is itself part of the strategy, not an afterthought).

Storage, Retention, and Real Disaster Recovery

A backup that lives on the same disk, volume, or SAN as the database it protects is not a disaster recovery plan — it's a copy that happens to be convenient to restore from when the problem is a bad deployment or an accidental DELETE. It does nothing when the disk fails, the SAN controller corrupts a LUN, ransomware encrypts every writable volume the service account can reach, or the data center itself goes offline. Any of those scenarios takes the database and its "backup" down together.

A real strategy follows something like the 3-2-1 principle: at least three copies of the data, on two different types of storage, with at least one copy offsite (geographically separate storage account, different region, or physically separate facility). In practice this usually means backing up to local disk for fast operational restores, then shipping or writing directly to blob storage or a separate backup target in another region, with immutability or write-once retention where ransomware is a concern so an attacker with database credentials can't also delete the recovery path.

Encrypt backups in transit and at rest — a stolen backup file is a stolen copy of the database, recovery model and RPO notwithstanding. Use WITH CHECKSUM on every backup so page-level corruption is caught at backup time rather than discovered during a restore under pressure, and periodically run RESTORE VERIFYONLY to confirm files are structurally readable. That said, RESTORE VERIFYONLY only proves the backup file is intact — it does not prove the restore sequence works, that permissions and logins come back correctly, or that the application reconnects cleanly. The only real test of a backup strategy is periodically executing the full restore sequence, including the tail-log step, against a non-production instance and measuring how long it actually takes against the RTO you promised.