A transaction log backup (often shortened to log backup) is a backup that copies the transaction log records generated since the previous log backup, so that every committed change can be replayed later. In Backup & recovery for databases, it is the mechanism that makes point-in-time recovery possible and that keeps the recovery point objective (RPO) down to minutes instead of the hours or days between full backups.
The term is most closely associated with Microsoft SQL Server, where BACKUP LOG is a distinct backup type, but the same principle exists elsewhere: WAL archiving in PostgreSQL, binary log backups in MySQL and MariaDB, and archived redo logs in Oracle all preserve the stream of changes between base backups.
How a transaction log backup works
Every change to a relational database is first written to the transaction log as a sequence of records, each identified by an increasing log sequence number (LSN). A full backup captures the data files at one moment; a transaction log backup captures the log records from the last backed-up LSN up to the current one, forming the next link in the log chain.
After a successful log backup, SQL Server can truncate the inactive part of the log, marking those virtual log files for reuse. Without regular log backups, a database in the full recovery model keeps every log record, and the log file grows until the disk is full, one of the most common operational incidents in SQL Server environments.
The table below shows how the main backup types relate to each other.
| Backup type | What it contains | Role in recovery |
|---|---|---|
| Full backup | All data pages plus enough log for consistency | Starting point of every restore |
| Differential backup | Pages changed since the last full backup | Shortens the restore sequence |
| Transaction log backup | Log records since the previous log backup | Replays changes to a chosen moment |
| Tail-log backup | Log not yet backed up at failure time | Captures the final changes |
Recovery models and log backup prerequisites
In SQL Server, transaction log backups are available only in the full and bulk-logged recovery models. The recovery model decides how much is logged and whether the log is kept until it is backed up.
| Recovery model | Log backups | Point-in-time restore | Typical use |
|---|---|---|---|
| Simple | Not possible | No, only to a full or differential backup | Development, easily rebuilt data |
| Full | Required | Yes, to any moment in the chain | Production systems |
| Bulk-logged | Required | Limited for intervals with bulk operations | Temporary use during large loads |
The log chain begins with a full backup taken after switching to the full model. Breaking it, for example by switching to simple recovery or losing one log backup file, means that later log backups cannot be restored until a new full or differential backup establishes a fresh base.
Restoring from transaction log backups
A restore replays backups in strict LSN order. The database stays in a restoring state until the final step, so each intermediate restore uses WITH NORECOVERY, and only the last uses WITH RECOVERY to roll back uncommitted transactions and open the database.
- Take a tail-log backup if the damaged database still allows it, to capture the final changes.
- Restore the most recent full backup with
NORECOVERY. - Restore the latest differential backup, if one exists.
- Restore every log backup in sequence, using
STOPATon the last one to stop at a chosen time. - Run the final step
WITH RECOVERYand verify the data.
The ability to stop at a precise moment or a marked transaction is what distinguishes log-based recovery from restoring a snapshot. It allows recovery to the second before an accidental DELETE or a faulty deployment, rather than to the last nightly backup.
Equivalents in other database systems
Other database engines implement the same idea under different names, which matters when comparing backup designs across platforms.
| Database system | Mechanism | Typical tooling |
|---|---|---|
| SQL Server | Transaction log backups | BACKUP LOG, maintenance plans |
| PostgreSQL | Continuous WAL archiving | archive_command, pgBackRest, Barman |
| MySQL and MariaDB | Binary log copies | mysqlbinlog, physical backup tools |
| Oracle Database | Archived redo logs | RMAN |
Logical backups such as pg_dump or mysqldump cannot be combined with log backups, because log replay requires a physical base backup with matching internal positions. A strategy that relies on logical dumps alone therefore offers recovery only to the moment of each dump.
Choosing a log backup frequency
The log backup interval directly sets the worst-case data loss if the log file itself is lost together with the server. Shorter intervals reduce that exposure but create more files to manage and restore.
| Workload | Common interval | Reasoning |
|---|---|---|
| High-value transactional system | Every 5 to 15 minutes | Minimal tolerable data loss |
| Standard business application | Every 15 to 60 minutes | Balance of RPO and overhead |
| Reporting database with nightly loads | Hourly or after loads | Few changes during the day |
| Development database | None, simple recovery | Data can be recreated |
Log backups should be copied off the database server promptly, ideally to separate storage or another site. A log backup stored only on the same disk as the database protects against logical errors but not against hardware loss.
Common mistakes with transaction log backups
Most failures with log backups come from broken chains and untested restores, not from the backup command itself.
- Running the full recovery model without log backups, which lets the log grow without limit.
- Taking ad hoc log backups outside the schedule, which splits the chain; use
COPY_ONLYfor one-off copies. - Deleting log backups under a retention policy shorter than the full backup cycle.
- Never rehearsing a restore, so a missing file is discovered only during an outage.
- Assuming that database snapshots or replicas replace log backups; they do not protect against replicated mistakes.
In everyday language
Think of a diary that records every change made to a ledger. A full backup is a photocopy of the ledger once a night. A transaction log backup copies the new diary pages every few minutes.
If something goes wrong at 3:42 in the afternoon, you take last night’s photocopy. Then you replay the diary pages up to 3:41. The ledger is restored almost exactly as it was, and very little work is lost.
Seen in the real world
A regional retailer runs its order database on SQL Server in the full recovery model, with a full backup every Sunday, differential backups every night and log backups every 15 minutes copied to a separate storage server.
On a Thursday at 10:47, an administrator runs an update script without a WHERE clause and overwrites the delivery addresses of all open orders. The team restores Sunday’s full backup and Wednesday night’s differential to a separate database, then applies the morning’s log backups with STOPAT set to 10:46. The correct addresses are copied back to production within an hour, and no orders are lost.