What Is a Database Backup? Full, Differential, Log

A database backup is a copy you can restore from, and nothing else counts. Not a copy of the data files, not a script of the tables, not a spreadsheet somebody exported. SQL Server gives you three kinds, they work together, and the differences decide how much you lose on a bad day.

The Three Kinds

Full copies everything. It is the base, and you cannot restore anything else without one.

Differential copies what changed since the last full backup. Not since the last differential, which catches people out. Each one is bigger than the last until you take a new full.

Log copies the transactions since the last log backup. This is the one that lets you restore to 2:47 this afternoon instead of to last midnight.

What the Sizes Look Like

I took all three on SQL Server 2025 against the same database, changing 50,000 rows between the full and the differential.

backup_type   size_mb  compressed_mb
Full          106.1    12.5
Differential  4.1      1.1
Log           360.7    48.2

Two things are worth pointing out.

The differential is 4 MB against a 106 MB full, because it holds only what moved. That is the whole appeal. A full backup every night with differentials through the day gives you frequent restore points without copying everything each time.

The log backup is larger than the database. That is not a mistake. The log holds every change since the last log backup, and I had been hammering this database all morning. Log size follows how much changed, not how big the database is. A small busy database can produce far more log than a large quiet one.

Compression is doing real work here too, 106 MB down to 12.5 MB. It costs some processor time and it is almost always worth turning on.

The Recovery Model Decides Everything

Before any of this matters, check one setting.

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases WHERE database_id > 4;

In SIMPLE recovery you cannot take log backups at all. Your best case is restoring the last full or differential, so everything after it is gone. That is a fine choice for a reporting copy you can rebuild.

In FULL recovery you can take log backups and restore to a point in time. It also means the log keeps growing until you back it up. Databases where somebody set FULL and never scheduled log backups are the most common reason a log fills a drive.

Choose FULL only if you are actually going to take log backups. Otherwise choose SIMPLE deliberately and accept what it means.

The Commands

BACKUP DATABASE Sales TO DISK = 'D:\Backups\Sales_full.bak'
    WITH INIT, COMPRESSION, CHECKSUM;

BACKUP DATABASE Sales TO DISK = 'D:\Backups\Sales_diff.bak'
    WITH INIT, DIFFERENTIAL, COMPRESSION, CHECKSUM;

BACKUP LOG Sales TO DISK = 'D:\Backups\Sales_log.trn'
    WITH INIT, COMPRESSION, CHECKSUM;

CHECKSUM is worth the keystrokes. It verifies pages as they are read, so a corrupt page is caught while you are making the backup rather than while you are restoring it.

One warning about INIT. It overwrites the file. On a real schedule you want a new filename each time, usually with a timestamp in it, or you will have exactly one backup no matter how often the job runs.

Restoring Is Where the Order Matters

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

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

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

NORECOVERY on every step except the last. It means more is coming, so leave the database closed. RECOVERY on the final step opens it for use. Get this wrong and you start again from the full backup.

You need the full, then at most one differential, then every log backup after that differential in order. Miss one log file and the chain stops there.

A Backup You Have Not Restored Is a Rumour

This is the part everyone agrees with and few people schedule.

Restore your backups somewhere else, regularly, and automatically. I have watched a backup job report success for months while writing files nobody could read. The job was green. The backups were useless. Nobody knew until the day it mattered.

RESTORE VERIFYONLY FROM DISK = 'D:\Backups\Sales_full.bak' WITH CHECKSUM;

This is better than nothing and it is not a restore. It checks that the file is readable and complete. Only an actual restore proves you can get your database back.

Two Questions to Answer Out Loud

How much data can we afford to lose? That number decides how often you take log backups. If the answer is fifteen minutes, take them every fifteen minutes.

How long can we be down? That decides your restore strategy, because restoring a 4 TB database takes as long as it takes no matter how good your backups are.

Answer those two and the schedule writes itself. Skip them and you find out what your schedule was worth at the worst possible moment.

A backup is not a file you have, it is a restore you have already practised.

This post was rewritten from scratch in September 2026. The original, published on 2009-04-02, was a short announcement about something that no longer exists. The address is the same, the subject is now a basic idea worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.