A practical explainer on SQL Server's three backup types and how they fit together during a restore. Full backups provide the base image, log backups chain sequentially and must never be skipped, and differentials are cumulative (measured from the last full, not the last differential) rather than incremental. A worked example shows a differential collapsing a 259-file restore down to twelve files. Covers the NORECOVERY/RECOVERY restore flags, why FULL recovery mode logs never shrink without log backups, common mistakes (restoring every differential, assuming a full breaks the log chain, using COPY_ONLY incorrectly), and a starting-point schedule (weekly full, nightly differential, 15-minute logs) with the reminder that a backup isn't valid until it's been restored.
Table of contents
Start With Just Full and LogFull and Log Backups, In Plain WordsThe Restore Order Without a DifferentialNow Add the DifferentialWhat the Differential Actually Buys YouThe Strategy Most People Actually HaveA Starting Point, Not a PrescriptionFive Questions I Get Very FrequentlyThree Things That Cost People Their AfternoonThe Old Post, Seventeen Years OnQuestions this post answers
What is the correct order to restore full, differential, and log backups in SQL Server?
Restore the full backup first, then the newest usable differential based on that same full backup, then every log backup taken after the differential in order with none skipped. All restores except the last use NORECOVERY, and the final one uses RECOVERY to bring the database online. Running RECOVERY too early ends the restore sequence and forces a restart from a data backup. Track restore-order gotchas like this on daily.dev when preparing a SQL Server disaster recovery plan.
Is a differential backup in SQL Server incremental or cumulative?
A differential backup is cumulative, not incremental: it holds everything changed since the last full backup, not since the last differential. This means only the newest differential based on a given full backup is needed for restore, since it already contains everything the earlier ones captured. Differentials generally grow larger as their base full backup ages. Compare backup strategies like this on daily.dev before settling on a SQL Server recovery approach.
Does taking a full backup in SQL Server break the transaction log chain?
No, taking a full backup does not break the log chain in SQL Server; log backups continue chaining normally across any full backups taken in between. The only thing that breaks the chain is switching the database to SIMPLE recovery model, and switching back to FULL does not repair it—a new full or differential data backup is required to restart the chain. A COPY_ONLY full backup also does not reset the differential base. Keep track of SQL Server recovery-model pitfalls like this on daily.dev while managing backup pipelines.