SQL Server transaction log backups: why and how

During audits I regularly find production databases in the FULL recovery model with no scheduled log backup. Or the opposite: production databases in SIMPLE, with a single full backup once a day — or worse, once a week, or worse still, never, because backup software takes snapshots of the VM.

In the worst case, the infrastructure manager states that “the VMs are backed up by Veeam” (or an equivalent), which sounds sufficient. It is not, and this article explains why.

Recovery model and transaction log behaviour

The behaviour of the transaction log, and the recovery models, are covered in:

• this article: Identify and fix transaction log problems;
• this YouTube video: Comprendre les problèmes du journal de transaction dans SQL Server (in French).

In summary:

FULL is the default model for any new database. It is the only model that guarantees zero data loss between two full backups — provided log backups are taken regularly.

In FULL, the log never empties itself. It accumulates transaction records until a BACKUP LOG command archives them and frees the space. Without that command, the .ldf file keeps growing, however often you take full backups.

What a transaction log backup does

The BACKUP LOG command performs two operations at once:

1. It copies the active transaction records to a .trn file, the conventional extension.
2. It truncates the inactive part of the log, freeing space inside the .ldf file.

Those .trn files form a continuous backup chain. Each file covers a given period of the database’s transactional activity. Applied after a full restore, the chain replays the history of the transactions and recovers the exact state of the database at any moment the backups cover.

That is the principle of Point-In-Time Recovery (PITR).

VM backups do not replace SQL Server backups

A backup strategy based solely on VM backups, or solely on full backups once a day or at any other interval, is not enough.

The log is never truncated

If you stay in the FULL recovery model, a VM backup — even one using the Volume Shadow Copy Service (VSS) to guarantee file system consistency — simply copies disk blocks. It does not natively tell the SQL Server engine to truncate its log. The result: you can back up your VM every hour and your .ldf file will keep growing until the disk is full, because SQL Server waits for an official log backup before freeing the space.

A Recovery Point Objective that is far too high

The RPO is the amount of data you accept losing in a disaster.

• With VM backups, the frequency is generally 24 hours, sometimes every 4 or 12 hours for critical servers. If your VM is backed up at midnight and a disk crash happens at 4 pm, you lose 16 hours of transactions.
• With log backups, the usual interval comes down to 5 or 15 minutes. Potential data loss is then reduced to the strict minimum, which is the standard requirement for any serious line-of-business application.

Point-in-time recovery becomes impossible

Picture this: at 10:42 a user runs a massive UPDATE with no WHERE clause by mistake.

• With a VM backup: your only option is to restore the whole VM, or the .mdf/.ldf files, as they were at midnight. You lose the entire morning’s work to correct a one-second mistake.
• With log backups: you restore your full backup, then your logs, into a copy of the database, telling SQL Server to stop at exactly 10:41:59. You get all your data back, minus the human error.

With an intact log chain, a database can be restored to any moment that chain covers.

A VM backup is a photograph taken at a point in time; the log chain is a film you can rewind frame by frame.

Without log backups, the only option is to go back to the last full backup, losing every transaction since.

Restore granularity

Restoring a complete multi-terabyte VM to recover a single 50 GB database is grotesquely inefficient. The process is heavy, often needs substantial extra disk space to mount the image, and ties up the network and storage resources of the virtualisation infrastructure. A native SQL restore, by contrast, is a direct process, optimized by the database engine, and depends on no external infrastructure layer.

Data integrity, and checksums

When BACKUP DATABASE or BACKUP LOG runs with the CHECKSUM option — strongly recommended — SQL Server verifies the integrity of the data pages as it reads them. If a page is corrupt on disk, the backup fails and tells you immediately.

A VM backup copies disk blocks blindly, sound or corrupt. You could back up a corrupt database for months without knowing, and discover the disaster only when you actually need to restore.

The hybrid strategy

Does this mean you should stop taking VM backups? No. VM backups are excellent for overall disaster recovery: they bring a complete server — OS, configuration, instances — back up very quickly on a new host.

For data protection, however, the standard production strategy has to combine:

1. Full backup: daily or weekly, to reset the restore baseline.
2. Differential backup (optional): with a weekly full backup, daily or every 6 hours, to speed up restore time. This step is unnecessary if you take a daily full backup. It depends on your RTO.
3. Log backup: every 5 to 15 minutes, for PITR and for the health of the disk.
4. VM backup: to protect the container, the server, rather than the content, the data.

By delegating log management to SQL Server, you guarantee the stability of your storage and the survival of your most recent business data.

The frequency of the log backups directly determines the RPO: the maximum data loss in an incident.

For critical transactional databases — invoicing, orders, financial data — an RPO of 15 minutes or less is the standard target.

The myth of frequent backups overloading the server

One of the most common objections I hear: “If I take a backup every 5 minutes I am going to bring my server to its knees with the constant stream of writes.”

It is an understandable intuition, but on SQL Server it is technically wrong.

The total volume of data your transactional activity generates in an hour is a constant. Whether you extract it in one go or in twelve, the amount of data written to the backup target is the same, to within a few 8 KB pages.

Backing up every 5 minutes simply cuts one heavy task into small, surgical, painless operations. The I/O pressure is smoothed out: instead of a massive disk saturation spike every hour, reading 1 GB at once, you get micro-reads of a few megabytes that pass entirely unnoticed by your users.

The DBA’s peace of mind

Beyond the technical side, going from a 60-minute RPO to a 5-minute one radically changes how an incident plays out.

Losing 5 minutes of accounting entries or customer orders is generally treated as a minor incident: “we will re-key the last few emails”. Losing a full hour or more is often a disaster that calls for a crisis meeting and a formal communication to management.

In short: do not be afraid of log backups

There is essentially no valid technical argument against going down to a frequency of 5 or 15 minutes on a production database. On ultra-critical systems, log backups every minute are not unusual.

The only real cost is the proliferation of small .trn files on your backup storage, and that is easily managed with any maintenance script or automated cleanup plan.

Setting up log backups

The T-SQL command to back up the transaction log:

-- Transaction log backup, with compression
BACKUP LOG [MyDatabase]
TO DISK = N'D:\Backups\MyDatabase\log\MyDatabase_20260302_143000.trn'
WITH COMPRESSION, STATS = 10;

In practice, two approaches are commonly used to automate these backups:

1. SQL Server Agent maintenance plans: graphical configuration in SSMS, scheduled through the interface. See my YouTube playlist (in French).
2. Ola Hallengren’s scripts: the reference community solution, free and actively maintained. These scripts handle file naming, compression, retention and operation logging. See my YouTube video (in French).

To check that the log backups are actually running, query the history in msdb:

-- Latest log backups per database, over the last 24 hours
SELECT
    bs.database_name,
    bs.backup_start_date,
    bs.backup_finish_date,
    DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) AS duration_seconds,
    bs.backup_size / 1024 / 1024                                   AS size_mb,
    bs.compressed_backup_size / 1024 / 1024                        AS compressed_size_mb
FROM msdb.dbo.backupset bs
WHERE bs.type = 'L' -- L = Log
  AND bs.backup_start_date >= DATEADD(DAY, -1, GETDATE())
ORDER BY bs.database_name, bs.backup_start_date DESC;

If a database in FULL does not appear in that result over the last 24 hours, no log backup has been taken.

Restoring with transaction log backups

Another reason teams avoid log backup strategies: the fear of not knowing how to restore them.

It is not that complicated, especially since the SSMS graphical interface makes life considerably easier.

Restoring a backup chain — full plus logs — follows a precise sequence. I made this video to help when you need to restore. It describes the whole procedure with a demonstration in SSMS: SQL Server : réussir vos restaurations de bases de données pas à pas (in French).

The restore sequence

The logic goes like this (see the video from 02:40):

1. Restore the full backup WITH NORECOVERY: the database stays in a restoring state, inaccessible, ready to receive the following logs.
2. Restore the last differential backup, if there is one, WITH NORECOVERY.
3. Restore the log backups in chronological order, each one WITH NORECOVERY.
4. On the last log file, apply WITH RECOVERY to bring the database online.

-- 1. Restore the full backup
--    (WITH NORECOVERY: the database stays in a restoring state)
RESTORE DATABASE [MyDatabase]
FROM DISK = N'D:\Backups\MyDatabase\full\MyDatabase_20260302_060000.bak'
WITH NORECOVERY, STATS = 10;

-- 2. Restore the transaction logs, in order
RESTORE LOG [MyDatabase]
FROM DISK = N'D:\Backups\MyDatabase\log\MyDatabase_20260302_060000.trn'
WITH NORECOVERY;

RESTORE LOG [MyDatabase]
FROM DISK = N'D:\Backups\MyDatabase\log\MyDatabase_20260302_061500.trn'
WITH NORECOVERY;

-- ... the remaining log files, in order ...

-- 3. The last log: bring the database online
--    (WITH RECOVERY is the default and can be omitted)
RESTORE LOG [MyDatabase]
FROM DISK = N'D:\Backups\MyDatabase\log\MyDatabase_20260302_143000.trn'
WITH RECOVERY;

If the database stays stuck in a restoring state after the whole chain, use:

-- Bring online a database stuck in a restoring state
RESTORE DATABASE [MyDatabase] WITH RECOVERY;

Point-in-time recovery

To restore the database to a precise moment, add the STOPAT clause on the last RESTORE LOG (see the video from 20:48):

-- Restore to a precise moment
RESTORE LOG [MyDatabase]
FROM DISK = N'D:\Backups\MyDatabase\log\MyDatabase_20260302_091500.trn'
WITH RECOVERY, STOPAT = '2026-03-02T09:02:00';

SQL Server replays every transaction up to 09:02:00 and stops. The transactional state of the database is consistent at that precise moment.

Using the SSMS graphical interface

In SSMS, if the database exists on the server and the backups were taken from that same server, the history is available in msdb. The interface reads it automatically and proposes the complete sequence without any manual file selection (see the video from 10:37).

The timeline bar in the SSMS restore interface lets you pick the restore point visually. SSMS then generates the corresponding T-SQL script, with the STOPAT clause correctly calculated.

To restore to a different server, or from files with no msdb history, simply select the .bak and .trn files manually in the interface. SSMS analyses the sequence and builds the restore chain automatically.

Mind the integrity of the backup chain

A transaction log chain is continuous: the ending LSN (Log Sequence Number) of each .trn file must match the starting LSN of the next. If a file is missing from the sequence, the restore stops at that point. The following files cannot be applied.

So do not lose any intermediate backup file.

Conclusion

1. Set the FULL recovery model on every production database, and schedule log backups.
2. Use a minimum frequency of 15 minutes for transactional databases.
3. Check regularly that the SQL Agent jobs run and that the history in msdb is consistent.
4. Test a full restore with its log chain in a development or staging environment, at least once a year.
5. Keep the .trn files at least as long as the full backup they attach to.
6. Back up the encryption certificates if the backups are encrypted — without the certificate, restoring is impossible.

Use Ola Hallengren’s scripts.