SQL Server Backup Types Explained: Full, Differential and Transaction Log

SQL Server offers several backup types, but three appear constantly in day-to-day administration: full, differential and transaction-log backups. The important thing is not simply knowing what each one is called. You need to understand what each backup contains, what it depends on, and how those pieces come together during a restore.

Start with the recovery requirement, not the backup button

A backup strategy should begin with two business questions: how much data can we afford to lose, and how long can recovery take? A system that can lose an entire day of work has different needs from one that can lose only a few minutes. Explore DBA responsibilities for more detail.

Backup frequency, storage, retention and restore complexity should follow those requirements.

Full backup

A full database backup contains the data needed to represent the whole database and enough transaction log information to make that backup transactionally consistent when restored. It becomes the base that other backup types may depend on.

For a small database, regular full backups may be simple and practical. As databases become larger, taking only full backups can consume more time, I/O and storage than necessary.

BACKUP DATABASE [Sales]
TO DISK = 'D:\SQLBackups\Sales_full.bak'
WITH CHECKSUM, COMPRESSION;

Differential backup

A differential backup contains data extents changed since the differential base, which is normally the most recent conventional full backup. Each new differential is cumulative from that same base; it does not contain only the changes since the previous differential.

That detail matters during restore. To restore with full plus differential, you usually need the full backup that is the differential base and the most recent differential you want to use. You do not restore every differential in between.

BACKUP DATABASE [Sales]
TO DISK = 'D:\SQLBackups\Sales_diff.bak'
WITH DIFFERENTIAL, CHECKSUM, COMPRESSION;

Transaction-log backup

Transaction-log backups capture log records that have not already been backed up in the preceding log-backup sequence. They are relevant to databases using the full or bulk-logged recovery model, not the simple recovery model.

Frequent log backups reduce the amount of recent work exposed to loss and, under the appropriate recovery model, support recovery to a specific point in time. They also play an important role in log truncation. For a focused explanation, see recovery models.

BACKUP LOG [Sales]
TO DISK = 'D:\SQLBackups\Sales_log.trn'
WITH CHECKSUM, COMPRESSION;

How the three types work together

Consider a database with a full backup Sunday night, differential backups each night, and transaction-log backups every 15 minutes. If the database fails Thursday at 14:07, a restore strategy could begin with Sunday's full backup, then Wednesday night's or Thursday's latest differential depending on the schedule, followed by the necessary log backups after that differential.

The differential reduces the number of log backups that need to be replayed, while the log backups provide much finer recovery granularity.

A simple restore sequence

Suppose you want to restore the latest full, the latest differential and then log backups. The full and differential are restored with NORECOVERY so SQL Server keeps the database ready for more restore operations. The final log is recovered when the sequence is complete.

RESTORE DATABASE [Sales]
FROM DISK = 'D:\SQLBackups\Sales_full.bak'
WITH NORECOVERY;

RESTORE DATABASE [Sales]
FROM DISK = 'D:\SQLBackups\Sales_diff.bak'
WITH NORECOVERY;

RESTORE LOG [Sales]
FROM DISK = 'D:\SQLBackups\Sales_log.trn'
WITH RECOVERY;

What about copy-only backups?

A copy-only full backup is useful when you need an ad-hoc full backup without changing the normal differential base. That makes it valuable before certain changes or when another team needs a standalone backup without disturbing the regular backup chain.

Why successful backup jobs are not enough

A green job history means the backup operation completed. It does not prove that the entire recovery process meets the business requirement. Backup files can be missing, retention can be wrong, restore permissions can be unavailable, or the documented recovery sequence can be incorrect.

Common beginner mistakes

• Taking backups but never performing restores.
• Using full recovery model without scheduling log backups.
• Assuming a differential depends on the previous differential instead of the differential base.
• Deleting older full backups while keeping dependent differential or log backups.
• Keeping all backup copies on the same storage as the database server.
• Confusing backup retention with actual business recovery requirements.

How often should each backup run?

There is no universal schedule. A common pattern might use periodic full backups, more frequent differentials and very frequent log backups, but the correct interval depends on RPO, RTO, database size, workload, storage and operational constraints. The schedule should be designed from recovery needs rather than copied from another organisation.

The real DBA skill is restore planning

Knowing how to create a backup is only the beginning. A DBA should know which backup files are required to recover from a given failure, in what order they must be restored, where the files are stored, how long the restore takes and how the recovered database will be validated before users return. The companion article on DBA learning roadmap develops this point.

Related training

The SQL Server DBA course includes backup design, automated backup jobs, full/differential/log chains, point-in-time recovery and hands-on restore drills.

View the SQL Server DBA course

Need help choosing the right course?

If you are not sure where to begin, contact Goalbi Academy and tell us your current experience level and what you want to learn.

Contact Goalbi Academy

References

Backup overview (SQL Server) - SQL Server

Differential Backups (SQL Server) - SQL Server

Transaction log backups - SQL Server

Back up and Restore of SQL Server Databases - SQL Server