In SQL Server, what do full, differential and transaction log backups each contain, and in what order do you restore them?
answer
- three backup types, one chain
- differential counts from the last full
- log backups need FULL or BULK_LOGGED
- NORECOVERY on every step but the last
basics
~20 sA full backup copies the whole database plus enough log to recover it; a differential, extents changed since the last full; a log backup, log since the previous one. Restore the full, the newest differential, then later log backups in order.
solid answer
~50 s`BACKUP DATABASE` makes a **full** backup: every data extent plus the part of the transaction log needed to bring that copy to a consistent state as of the moment it finished. `BACKUP DATABASE ... WITH DIFFERENTIAL` copies only the extents marked in the differential bitmap since the last conventional full backup, its *differential base*, so each differential grows until the next full. `BACKUP LOG` copies the log records written since the previous log backup and is possible only under the FULL or BULK_LOGGED recovery model. To restore, apply the full backup `WITH NORECOVERY`, then only the most recent differential `WITH NORECOVERY`, then every later log backup in sequence, and finish `WITH RECOVERY`, which rolls back uncommitted work and opens the database. A `COPY_ONLY` full backup leaves the differential base alone, so it is safe for an ad hoc copy.
code
sql · 8 lines-- Sunday: full backup, the differential base
BACKUP DATABASE Sales TO DISK = N'D:\bak\Sales_full.bak' WITH CHECKSUM;
-- Each night: differential since that full backup
BACKUP DATABASE Sales TO DISK = N'D:\bak\Sales_diff.bak' WITH DIFFERENTIAL, CHECKSUM;
-- Every 15 minutes: log backup (FULL recovery model)
BACKUP LOG Sales TO DISK = N'D:\bak\Sales_log_1015.trn' WITH CHECKSUM;go deeper
Recall the three statements, BACKUP DATABASE, BACKUP DATABASE WITH DIFFERENTIAL and BACKUP LOG, and that a restore goes full, newest differential, then log backups in order.
Explain the differential base and bitmap, why only the newest differential is restored, and why the log chain survives a new full backup.
Show that you read msdb backup history to prove the chain is complete, use CHECKSUM and COPY_ONLY on purpose, and rehearse restores rather than trusting job success.
Treat the full, differential and log cadence as a restore-time and storage budget agreed with the business, and make someone accountable for testing restores.
## The three backup types SQL Server has three backup types that matter for an ordinary user database, and each is a different statement or option: | Backup | Statement | Contains | Recovery models | |---|---|---|---| | **Full** | `BACKUP DATABASE` | Every data extent plus enough transaction log to make the copy consistent | All three | | **Differential** | `BACKUP DATABASE ... WITH DIFFERENTIAL` | Extents changed since the differential base | All three | | **Transaction log** | `BACKUP LOG` | Log records since the previous log backup | FULL and BULK_LOGGED only | A **full backup** is taken while the database stays online: inserts, updates and deletes keep running. Because pages are read over a period of time, the backup also captures the log written while it ran, and a restore uses that log to bring the copy to the state of the database at the moment the backup *finished*, minus any uncommitted transactions. ## How a differential knows what changed An **extent** is a group of eight contiguous 8 KB pages. SQL Server keeps a **differential bitmap** with one bit per extent; any change to an extent sets its bit. A differential backup reads only the extents whose bit is set, which is why it is usually much smaller and faster than a full backup. - The full backup the bitmap counts from is the **differential base**. Only the next conventional full backup clears the bitmap. - Every differential therefore contains everything the previous differential did, plus newer changes. Differentials are **cumulative**, not incremental. - A full backup taken `WITH COPY_ONLY` does not touch the bitmap and cannot serve as a base, so an ad hoc copy for a developer does not silently change what Tuesday's differential means. - Because differentials grow over time, a regular full backup (weekly is common) keeps them, and the restore time, under control. ## The log chain Under the FULL and BULK_LOGGED recovery models, each log backup starts where the previous one ended. That unbroken sequence is the **log chain**, and it is independent of data backups: a log backup taken after a new full backup still continues the chain that the older full backup started. Two consequences follow: - A full or differential backup does not truncate the log; only a log backup frees log space in FULL recovery. - If the newest full backup turns out to be unreadable, you can restore the previous one and replay every log backup since, provided none is missing. The SIMPLE recovery model has no log backups at all, so a database in SIMPLE can only be restored to the end of a full or differential backup. ## The restore sequence A restore that uses more than one backup must keep the database in the RESTORING state until the last step. `RESTORE` defaults to `WITH RECOVERY`, which rolls back uncommitted transactions and opens the database, after which no further backups can be applied. So the sequence is: 1. Restore the most recent full backup `WITH NORECOVERY`. 2. Restore only the most recent differential based on that full backup, also `WITH NORECOVERY`. Older differentials are skipped because the newest already contains their changes. 3. Restore every log backup taken after that differential, in order, `WITH NORECOVERY`. 4. Recover the database with `RESTORE DATABASE ... WITH RECOVERY`, or put `WITH RECOVERY` on the final log restore. Restoring over a live database adds one step before all of this, a backup of the tail of the log, so that the commits since the last log backup are not lost. ## Reading backup history SQL Server records every backup in `msdb`. The `msdb.dbo.backupset` table holds a `type` column (`D` for a database backup, `I` for a differential, `L` for a log backup), `is_copy_only`, and `first_lsn` and `last_lsn`, which let you confirm that each log backup begins where the previous one ended. That history is the first thing to read before planning a restore. ## Common mistakes - Treating differentials as incremental and restoring all of them in turn. - Forgetting `NORECOVERY` on the first restore, which recovers the database early and forces you to start over. - Expecting a full backup to keep the log small under FULL recovery. - Taking an ad hoc full backup without `COPY_ONLY` in the middle of the week, so later differentials are based on a file that may not be kept with the rest.
- In SQL Server, does taking a new full backup break the log chain for restores that start from an older full backup?No. The log chain is independent of data backups: log backups taken after a newer full backup still continue the chain the older one began. You can restore an older full backup and then every later log backup, which is how you recover when the newest full backup is unreadable. A missing log backup or a switch to the SIMPLE recovery model, by contrast, does break it.
- Why does each SQL Server differential backup get bigger through the week?A differential copies every extent whose bit is set in the differential bitmap, and only the next conventional full backup clears that bitmap. Changed extents keep accumulating, so Friday's differential holds everything Monday's did plus later changes. A regular full backup resets the base and keeps differentials, and the restore time, small.
A full backup is a photograph of the whole house; a differential is a fresh photo of every room that has changed since that photograph; the log backups are a diary of each change in order. To rebuild, you hang the original photograph, swap in the latest set of room photos, then replay the diary from there.
- A differential holds only the changes since the previous differential.
- A full backup truncates the transaction log under the FULL recovery model.
- You can take log backups of a database in the SIMPLE recovery model.
- Every differential since the full must be restored in order, like incrementals.
- Restore the full backup WITH RECOVERY first, then apply log backups afterwards.