SQL Server Backup Types Explained: Full, Differential, Log, Tail-Log and Copy-Only

SQL Server offers more backup types than most people use, and the differences between them decide what you can restore, how far back you can go, and how long a restore takes. Several common mistakes come from misunderstanding them: a manual full backup that silently breaks the nightly differentials, a log that never stops growing despite nightly full backups, or a “test copy” that turns out to be the only backup the restore depends on.

This guide explains each SQL Server backup type, how they depend on each other, and which one to use when.

SQL Server Backup Types at a Glance

Full Backup

A full backup contains all the data in the database, plus enough of the transaction log to bring that data to a consistent state when it’s restored. The backup represents the database as it was when the backup finished, and users can keep working while it runs.

BACKUP DATABASE [SalesDB]
TO DISK = N'\\backupsrv\sql\SalesDB\SalesDB_FULL_20261004.bak'
WITH COMPRESSION, CHECKSUM, STATS = 10;

Two things a full backup does not do are often misunderstood:

• It doesn’t truncate the transaction log. In the full recovery model, only a log backup frees log space for reuse. A database with nightly full backups but no log backups will keep growing its log until the disk is full. See SQL Server Transaction Log Too Big?
• It doesn’t break the log chain. Log backups continue in sequence across full backups, so a restore can start from an older full backup and still roll forward through all later log backups.

Differential Backup

A differential backup contains only the data extents that have changed since the differential base, which is the most recent (non-copy-only) full backup. SQL Server tracks changed extents in a bitmap, so a differential doesn’t have to read the whole database to know what changed.

BACKUP DATABASE [SalesDB]
TO DISK = N'\\backupsrv\sql\SalesDB\SalesDB_DIFF_20261007.bak'
WITH DIFFERENTIAL, COMPRESSION, CHECKSUM;

Differentials are cumulative: Thursday’s differential contains everything Monday’s, Tuesday’s and Wednesday’s did. To restore, you need only the full backup and the latest differential, not every one in between. The trade-off is that each differential is larger than the last until the next full backup resets the base.

The trap: a manual full backup moves the differential base

Transaction Log Backup

A log backup contains all log records that weren’t included in the previous log backup. It’s only available in the full and bulk-logged recovery models, and the first log backup requires an existing full backup. Log backups do three things:

• Point-in-time recovery: with an unbroken chain of log backups, you can restore to any moment, for example one second before a wrong DELETE.
• A low RPO: the log backup interval sets the maximum data loss. Every 15 minutes means at most 15 minutes of lost work.
• Log truncation: after a log backup, the inactive part of the log can be reused, which keeps the log file at a stable size.

BACKUP LOG [SalesDB]
TO DISK = N'\\backupsrv\sql\SalesDB\SalesDB_LOG_20261007_1415.trn'
WITH COMPRESSION, CHECKSUM;

Every log backup is a link in a chain. If one file is lost or damaged, you can’t roll forward past it. That’s why log backups need the same care as full backups: off-server storage, monitoring and retention. In the simple recovery model there are no log backups; we explain the consequences in SQL Server Simple Recovery Model: When to Use It and When Not To.

Tail-Log Backup

A tail-log backup captures the log records that haven’t been backed up yet, the “tail” of the log, just before a restore. If a data file fails but the log file is still readable, a tail-log backup lets you restore with no data loss at all:

-- Database online: back up the tail and leave the database in RESTORING state
BACKUP LOG [SalesDB]
TO DISK = N'\\backupsrv\sql\SalesDB\SalesDB_TAIL.trn'
WITH NORECOVERY;

-- Database damaged: try to back up the tail anyway
BACKUP LOG [SalesDB]
TO DISK = N'\\backupsrv\sql\SalesDB\SalesDB_TAIL.trn'
WITH NO_TRUNCATE, CONTINUE_AFTER_ERROR;

Copy-Only Backup

A copy-only backup is independent of the regular backup sequence:

• A copy-only full backup doesn’t change the differential base, so it doesn’t affect your scheduled differentials. It can’t be used as a differential base itself.
• A copy-only log backup doesn’t affect the sequence of regular log backups, and the log isn’t truncated after it.

Use copy-only for anything outside the schedule: refreshing a test or development environment, sending a copy to a vendor, or taking a safety copy before a deployment.

BACKUP DATABASE [SalesDB]
TO DISK = N'\\backupsrv\adhoc\SalesDB_COPY_before_release.bak'
WITH COPY_ONLY, COMPRESSION, CHECKSUM;

COPY_ONLY has no effect when combined with DIFFERENTIAL. In Always On availability groups, full backups on secondary replicas must be copy-only up to SQL Server 2022.

File, Filegroup and Partial Backups

For very large databases split into several filegroups, backing up everything every time may not fit the maintenance window. SQL Server can back up individual files or filegroups, and a partial backup (BACKUP DATABASE ... READ_WRITE_FILEGROUPS) includes the primary filegroup and every read/write filegroup but skips read-only ones. A common pattern is to move historical data to a read-only filegroup, back it up once, and then back up only the active part regularly. These strategies make the restore more complex, so they’re best kept for databases that really need them, and tested carefully.

Backup Options Worth Using on Every Backup

• CHECKSUM: verifies page checksums while the backup reads the data and stops the backup if it finds a damaged page. It also adds a checksum to the backup itself.
• COMPRESSION: usually makes backups smaller and faster for some extra CPU. Supported in Enterprise edition and, from SQL Server 2016, Standard edition. See SQL Server Backup Compression: Benefits, CPU Cost and Defaults.
• ENCRYPTION: protects backup files from SQL Server 2014 onward. Back up the certificate, or the backups can’t be restored.

Which Backup Types Do You Need?

• Critical or important databases (full recovery model): full + differential + frequent log backups. A typical schedule is a weekly full, daily differentials and log backups every 15 minutes.
• Small databases: a daily full backup plus log backups is often simpler than adding differentials.
• Reloadable databases (simple recovery model): daily full backups, optionally with differentials.
• Anything ad hoc: copy-only.

How to turn this into a schedule that matches your RPO and RTO, where to store backups and how to test them is covered in How to Build a SQL Server Backup Plan.

Check Which Backups You Actually Have

This query lists the backups of the last seven days, including the type, size and whether each backup is copy-only:

SELECT b.database_name,
       CASE b.type
            WHEN 'D' THEN 'Full'
            WHEN 'I' THEN 'Differential'
            WHEN 'L' THEN 'Log'
            WHEN 'F' THEN 'File / filegroup'
            WHEN 'P' THEN 'Partial'
       END AS backup_type,
       b.is_copy_only,
       b.backup_start_date,
       b.backup_finish_date,
       CAST(b.compressed_backup_size / 1048576.0 AS decimal(18, 1)) AS size_mb,
       m.physical_device_name
FROM msdb.dbo.backupset AS b
JOIN msdb.dbo.backupmediafamily AS m ON m.media_set_id = b.media_set_id
WHERE b.backup_start_date > DATEADD(DAY, -7, GETDATE())
ORDER BY b.database_name, b.backup_start_date DESC;

Watch for regular (non-copy-only) full backups that don’t come from your schedule. Each one moves the differential base, and the physical_device_name column shows where it went.

Frequently Asked Questions