Full, Differential, Log, Copy-Only, File/Filegroup, and Tail-Log Backups

Learning outcomes

ServiceHub has reached the point where a database backup cannot be treated as a ceremonial nightly file. A usable recovery system is a chain of evidence: which data pages were captured, which log ranges are available, which full backup is the differential base, which files or filegroups are protected, whether a special-purpose backup disturbed routine restore assumptions, and whether the final unbacked-up tail of the log can still be captured after an incident.

This lesson builds that model from the backup set outward. The goal is not to memorize six BACKUP syntaxes. It is to understand what each backup contributes to a future restore sequence and what it does not prove.

1. Think in restore dependencies, not file extensions

A SQL Server backup file can contain one or many backup sets, so the extension .bak tells you almost nothing about restore semantics. A full database backup contains enough data to represent the whole database at recovery consistency, plus enough transaction log to bring the included pages to a consistent point. A differential contains extents changed since its differential base. A log backup archives a contiguous log range and, under FULL/BULK_LOGGED recovery, advances the log archive point. File and filegroup backups protect selected data containers. A copy-only backup is deliberately independent of the normal backup sequence.

SELECT SERVERPROPERTY('ProductVersion') AS product_version,       SERVERPROPERTY('ProductUpdateLevel') AS update_level,       SERVERPROPERTY('Edition') AS edition,       SERVERPROPERTY('EngineEdition') AS engine_edition,       SERVERPROPERTY('InstanceDefaultBackupPath') AS default_backup_path;GOSELECT name,recovery_model_desc,state_desc,log_reuse_wait_desc,       is_auto_close_on,is_auto_shrink_onFROM sys.databasesWHERE name IN (N'master',N'model',N'tempdb',N'ServiceHubLab');GOSELECT name,value_in_useFROM sys.configurationsWHERE name IN (N'backup compression default',N'backup compression algorithm');GO

USE master;GOIF DB_ID(N'ServiceHubRecoveryLab') IS NOT NULLBEGIN  ALTER DATABASE ServiceHubRecoveryLab SET SINGLE_USER WITH ROLLBACK IMMEDIATE;  DROP DATABASE ServiceHubRecoveryLab;END;GOCREATE DATABASE ServiceHubRecoveryLab;ALTER DATABASE ServiceHubRecoveryLab SET COMPATIBILITY_LEVEL = 170;ALTER DATABASE ServiceHubRecoveryLab SET RECOVERY FULL;GOUSE ServiceHubRecoveryLab;GOCREATE SCHEMA lab15 AUTHORIZATION dbo;GOCREATE TABLE lab15.RecoveryEvent(  event_id bigint IDENTITY(1,1) PRIMARY KEY,  event_time datetime2(3) NOT NULL CONSTRAINT DF_lab15_RecoveryEvent_time DEFAULT SYSUTCDATETIME(),  event_type varchar(32) NOT NULL,  work_order_id bigint NULL,  details nvarchar(300) NOT NULL);CREATE TABLE lab15.WorkOrderRecovery(  work_order_id bigint NOT NULL PRIMARY KEY,  status varchar(16) NOT NULL,  priority tinyint NOT NULL,  amount decimal(12,2) NOT NULL,  last_change datetime2(3) NOT NULL);INSERT lab15.WorkOrderRecovery(work_order_id,status,priority,amount,last_change)VALUES (15001,'OPEN',2,240.00,SYSUTCDATETIME()), (15002,'ASSIGNED',3,480.00,SYSUTCDATETIME()), (15003,'ESCALATED',5,860.00,SYSUTCDATETIME());INSERT lab15.RecoveryEvent(event_type,work_order_id,details)VALUES ('BASELINE',NULL,N'Chapter 15 recovery baseline created');GO

2. Build a deterministic full → log → differential chain

The lab uses the instance default backup directory so it works on Windows, Linux or a container when the SQL Server service account has access to its configured backup path. Backup device permissions are evaluated in the Database Engine service account’s OS context, not your SSMS desktop identity.

DECLARE @root nvarchar(4000)=CONVERT(nvarchar(4000),SERVERPROPERTY('InstanceDefaultBackupPath'));IF @root IS NULL THROW 51000,'InstanceDefaultBackupPath is unavailable; choose a writable backup directory explicitly.',1;DECLARE @sep nchar(1)=CASE WHEN CHARINDEX(N'/',@root)>0 THEN N'/' ELSE N'\' END;IF RIGHT(@root,1) IN (N'/',N'\') SET @sep=N'';SELECT @root AS backup_root;DECLARE @full nvarchar(4000)=@root+@sep+N'ServiceHubRecoveryLab_full.bak';DECLARE @log1 nvarchar(4000)=@root+@sep+N'ServiceHubRecoveryLab_log1.trn';DECLARE @diff nvarchar(4000)=@root+@sep+N'ServiceHubRecoveryLab_diff.bak';BACKUP DATABASE ServiceHubRecoveryLab TO DISK=@fullWITH INIT,CHECKSUM,NAME=N'Chapter15 baseline full';INSERT ServiceHubRecoveryLab.lab15.RecoveryEvent(event_type,work_order_id,details)VALUES ('UPDATE',15001,N'Priority raised before first log backup');UPDATE ServiceHubRecoveryLab.lab15.WorkOrderRecoverySET priority=4,last_change=SYSUTCDATETIME() WHERE work_order_id=15001;BACKUP LOG ServiceHubRecoveryLab TO DISK=@log1WITH INIT,CHECKSUM,NAME=N'Chapter15 first log';INSERT ServiceHubRecoveryLab.lab15.RecoveryEvent(event_type,work_order_id,details)VALUES ('UPDATE',15002,N'Amount changed before differential');UPDATE ServiceHubRecoveryLab.lab15.WorkOrderRecoverySET amount=525,last_change=SYSUTCDATETIME() WHERE work_order_id=15002;BACKUP DATABASE ServiceHubRecoveryLab TO DISK=@diffWITH DIFFERENTIAL,INIT,CHECKSUM,NAME=N'Chapter15 differential';

Expected state: the database remains online; the three backup files exist in the SQL Server backup directory; and msdb.dbo.backupset records one full (D), one log (L) and one differential (I) set. Those rows are useful inventory, but they are not evidence that another instance can read the media or that your restore sequence is complete.

SELECT TOP (20)       database_name,type,is_copy_only,backup_start_date,backup_finish_date,       first_lsn,last_lsn,checkpoint_lsn,database_backup_lsn,       differential_base_lsn,has_backup_checksums,compressed_backup_sizeFROM msdb.dbo.backupsetWHERE database_name=N'ServiceHubRecoveryLab'ORDER BY backup_finish_date DESC;GO

3. Copy-only means “do not disturb routine sequence,” not “less complete”

An ordinary full backup becomes the differential base for later differentials. That is desirable for scheduled full backups but disruptive if an engineer takes a one-off full backup for a migration rehearsal and the operations team expects tomorrow’s differential to depend on Sunday’s scheduled full. COPY_ONLY solves that coordination problem.

DECLARE @root nvarchar(4000)=CONVERT(nvarchar(4000),SERVERPROPERTY('InstanceDefaultBackupPath'));IF @root IS NULL THROW 51000,'InstanceDefaultBackupPath is unavailable; choose a writable backup directory explicitly.',1;DECLARE @sep nchar(1)=CASE WHEN CHARINDEX(N'/',@root)>0 THEN N'/' ELSE N'\' END;IF RIGHT(@root,1) IN (N'/',N'\') SET @sep=N'';SELECT @root AS backup_root;DECLARE @copy nvarchar(4000)=@root+@sep+N'ServiceHubRecoveryLab_copy_only.bak';BACKUP DATABASE ServiceHubRecoveryLab TO DISK=@copyWITH COPY_ONLY,INIT,CHECKSUM,NAME=N'Chapter15 ad-hoc copy-only full';SELECT TOP (10) type,is_copy_only,backup_finish_date,       database_backup_lsn,differential_base_lsnFROM msdb.dbo.backupsetWHERE database_name=N'ServiceHubRecoveryLab'ORDER BY backup_finish_date DESC;

4. File/filegroup backup is a recovery design tool, not a smaller full backup

Large databases can protect and restore files or filegroups independently. Under FULL/BULK_LOGGED, file restores require log recovery to make the restored data consistent with the rest of the database. Under SIMPLE, file restore is limited to read-only secondary files/filegroups. These techniques matter for very large databases and tiered data, but they create more media and sequencing dependencies.

USE ServiceHubRecoveryLab;GOSELECT fg.name AS filegroup_name,fg.type_desc,fg.is_read_only,       df.name AS logical_file,df.physical_name,df.size*8.0/1024 AS size_mbFROM sys.filegroups AS fgJOIN sys.database_files AS df ON df.data_space_id=fg.data_space_idORDER BY fg.data_space_id,df.file_id;GO-- Optional learning backup of PRIMARY on the disposable lab:-- DECLARE @p nvarchar(4000)=N'<writable path>/ServiceHubRecoveryLab_primary.bak';-- BACKUP DATABASE ServiceHubRecoveryLab FILEGROUP=N'PRIMARY'-- TO DISK=@p WITH INIT,CHECKSUM;

Do not infer that file-level backup is automatically faster or easier. You trade one monolithic restore sequence for more granular media inventory, filegroup design and log-chain coordination.

5. Tail-log backup closes the recoverable timeline—when the scenario needs it

The tail of the log is the portion not yet archived in a log backup. If the database is using FULL/BULK_LOGGED and you want to recover through the latest committed work before overwriting/restoring an online database, capturing the tail normally prevents avoidable data loss. For an online database that is about to be restored, Microsoft recommends BACKUP LOG ... WITH NORECOVERY, which also prevents further changes. For an offline/damaged database the options differ, and a damaged log can make tail capture impossible.

-- Run only when you are actually beginning a restore sequence.-- BACKUP LOG ServiceHubRecoveryLab-- TO DISK = N'<writable path>/ServiceHubRecoveryLab_tail.trn'-- WITH NORECOVERY,CHECKSUM;-- The database enters RESTORING and stays unavailable for normal writes.

A tail-log backup is not required when the desired recovery point is already inside an earlier log backup or when you are intentionally replacing a database and do not need later work. “Always take a tail log” is therefore as misleading as “never take one.” The recovery objective determines the requirement.

6. Production judgment

Production judgment. Backups require enough privilege to read the database and OS/device permission for the SQL Server service. Record where media lives, whether it is encrypted/compressed, the recovery model, LSN chain, retention and restore owner. Before destructive restore, explicitly decide whether the tail must be captured and what data-loss window is accepted.

Hands-on verification checklist

• Confirm the full/log/differential files are written to the instance backup directory.
• Use msdb.dbo.backupset to identify backup types, LSNs and checksum metadata.
• Create a copy-only full and verify is_copy_only=1.
• Do not execute the tail-log NORECOVERY template until beginning a real restore sequence.
• Keep the database and media for Lessons 2–4; later cleanup removes the disposable databases.

Authoritative references

• Backup overview — backup types and strategy
• Copy-only backups — copy-only semantics and differential base
• Tail-log backups — when tail capture is required and supported
• Backup history and header information — msdb and backup metadata
• BACKUP — current BACKUP syntax and permissions