Types of Backup in SQL Server: Full, Differential, Log and More

The main types of backup in SQL Server are full, differential and transaction log backups. Copy-only, file and filegroup, partial and tail-log backups cover special jobs. Your recovery model decides which of them you’re allowed to take.

Backups rescue you from a dead disk, corruption or a bad DELETE, so I ran each type, restored the chain to a new database and broke a log backup on purpose.

Before You Start: Backup Test Setup and Orders Table

Test setup: SQL Server 2025 (17.0.1000.7) Developer, sqlcmd in VS Code, database DfBackupTypesDemo in FULL recovery (check your version):

CREATE TABLE dbo.Orders (
    OrderID  int          PRIMARY KEY,
    Customer nvarchar(20) NOT NULL,
    Amount   decimal(8,2) NOT NULL
);
INSERT INTO dbo.Orders VALUES
(1, N'Hannah Brooks', 129.99), (2, N'Tyler Reed', 54.10),
(3, N'Grace Miller', 310.00), (4, N'Marcus Bennett', 22.75);

No database yet? Create one first.

Method 1: Full, Differential and Transaction Log Backup (Recommended)

Same Orders table: full backup, new row, differential, new row, log backup, then a copy-only full:

:setvar dir "C:\DatabaseFAQsDemo\df_bkptypes\bak"
SET NOCOUNT ON;
BACKUP DATABASE DfBackupTypesDemo TO DISK = '$(dir)\full.bak' WITH INIT, FORMAT;

INSERT dbo.Orders VALUES (6, N'Ethan Cole', 45.00);
BACKUP DATABASE DfBackupTypesDemo TO DISK = '$(dir)\diff.bak'
    WITH DIFFERENTIAL, INIT, FORMAT;

INSERT dbo.Orders VALUES (7, N'Ava Turner', 199.00);
BACKUP LOG DfBackupTypesDemo TO DISK = '$(dir)\log.trn' WITH INIT, FORMAT;

BACKUP DATABASE DfBackupTypesDemo TO DISK = '$(dir)\copy.bak'
    WITH COPY_ONLY, INIT, FORMAT;

Summary lines:

BACKUP DATABASE successfully processed 394 pages in 0.153 seconds (20.092 MB/sec).
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 130 pages in 0.018 seconds (56.206 MB/sec).
BACKUP LOG successfully processed 11 pages in 0.019 seconds (4.523 MB/sec).
BACKUP DATABASE successfully processed 418 pages in 0.021 seconds (155.319 MB/sec).

Important: you need BACKUP permission: sysadmin, db_owner or db_backupoperator.

How does this work? The full copied every used page (394). The differential copied only extents changed since that full (130), and the log backup only unsaved log records (11).

How to Read Backup Types in msdb.dbo.backupset

Same Orders table, now its four backups as msdb logged them:

SET NOCOUNT ON;
SELECT b.type,
       CAST(b.backup_size / 1024 AS int) AS SizeKB,
       CAST(b.first_lsn AS varchar(20))  AS FirstLSN,
       CAST(b.differential_base_lsn AS varchar(20)) AS DiffBase,
       b.is_copy_only AS CopyOnly
FROM msdb.dbo.backupset AS b
WHERE b.database_name = N'DfBackupTypesDemo'
  AND b.family_guid = (SELECT family_guid
      FROM sys.database_recovery_status WHERE database_id = DB_ID())
ORDER BY b.backup_set_id;

Result:

type SizeKB      FirstLSN             DiffBase             CopyOnly
---- ----------- -------------------- -------------------- --------
D           3224 45000000043200001    NULL                        0
I           1112 45000000057600001    45000000043200001           0
L            144 45000000043200001    NULL                        0
D           3416 45000000068000001    NULL                        1

What this tells me: full 3,224 KB, differential 1,112 KB, log 144 KB. The differential’s base and the log’s first LSN both point at the full. Track growth with a database size check.

The copy-only row (CopyOnly = 1) isn’t a differential base, so the next differential still uses the regular full.

Restore the Backup Chain WITH NORECOVERY, Then RECOVERY

An untested backup is a guess. I restored all three to DfBackupTypesDemo_Restore:

:setvar dir "C:\DatabaseFAQsDemo\df_bkptypes\bak"
SET NOCOUNT ON;
RESTORE DATABASE DfBackupTypesDemo_Restore FROM DISK = '$(dir)\full.bak'
  WITH NORECOVERY,
  MOVE N'DfBackupTypesDemo' TO '$(dir)\Restore.mdf',
  MOVE N'DfBackupTypesDemo_log' TO '$(dir)\Restore_log.ldf';
RESTORE DATABASE DfBackupTypesDemo_Restore FROM DISK = '$(dir)\diff.bak' WITH NORECOVERY;
RESTORE LOG DfBackupTypesDemo_Restore FROM DISK = '$(dir)\log.trn' WITH RECOVERY;

SELECT OrderID, Customer FROM DfBackupTypesDemo_Restore.dbo.Orders WHERE OrderID > 4;

What came back:

RESTORE DATABASE successfully processed 394 pages in 0.027 seconds (113.859 MB/sec).
RESTORE DATABASE successfully processed 130 pages in 0.014 seconds (72.265 MB/sec).
RESTORE LOG successfully processed 11 pages in 0.007 seconds (12.276 MB/sec).
OrderID     Customer            
----------- --------------------
          6 Ethan Cole          
          7 Ava Turner

How does this work? NORECOVERY keeps the database restoring for the next file; RECOVERY brings it online. MOVE works as when you move database files.

Method 2: Tail-Log Backup Before You Restore

A tail-log backup saves log records written since the last log backup, so a restore loses nothing:

:setvar dir "C:\DatabaseFAQsDemo\df_bkptypes\bak"
SET NOCOUNT ON;
BACKUP LOG DfBackupTypesDemo_Restore TO DISK = '$(dir)\tail.trn' WITH NORECOVERY, INIT, FORMAT;
SELECT CAST(state_desc AS varchar(10)) AS State
FROM sys.databases WHERE name = N'DfBackupTypesDemo_Restore';

Result:

BACKUP LOG successfully processed 18 pages in 0.008 seconds (17.578 MB/sec).
State     
----------
RESTORING

NORECOVERY left it RESTORING, so nothing changes after the tail. Full or bulk-logged only. For a cloud source, see Azure database to local SQL Server.

Method 3: File, Filegroup and Partial Backups

Big databases can go one filegroup at a time:

:setvar dir "C:\DatabaseFAQsDemo\df_bkptypes\bak"
BACKUP DATABASE DfBackupTypesDemo FILEGROUP = N'PRIMARY'
    TO DISK = '$(dir)\fg.bak' WITH INIT, FORMAT;

Result:

BACKUP DATABASE...FILE=<name> successfully processed 402 pages in 0.020 seconds (157.031 MB/sec).

A partial backup (READ_WRITE_FILEGROUPS) takes PRIMARY plus every read/write filegroup.

Which Recovery Model Allows Which Backup Types?

Backup type	Simple	Full	Bulk-logged
Full, differential, copy-only	Yes	Yes	Yes
Log, tail-log	No	Yes	Yes
Point-in-time restore	No	Yes	No

I switched the restored copy to SIMPLE and tried a log backup.

:setvar dir "C:\DatabaseFAQsDemo\df_bkptypes\bak"
ALTER DATABASE DfBackupTypesDemo_Restore SET RECOVERY SIMPLE;
BACKUP LOG DfBackupTypesDemo_Restore TO DISK = '$(dir)\simple.trn';

sqlcmd returned:

Msg 4208, Level 16, State 1, Server YOUR-SERVER, Line 2
The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
Msg 3013, Level 16, State 1, Server YOUR-SERVER, Line 2
BACKUP LOG is terminating abnormally.

Things to Keep in Mind With Backups (My Takeaways)

1. No full backup, no chain.
2. Under FULL, only log backups truncate the log.
3. Use COPY_ONLY for ad hoc copies, like a backup to Azure Blob Storage test.
4. Things that tripped me up: Msg 3013 hides the real error, and $(dir) must be writable by the SQL Server service account.

• PostgreSQL version: back up a PostgreSQL database
• When something fails, read the SQL Server error log

Frequently Asked Questions