SQL Server Interview Question #189

What is a transaction log backup?

Security, Backup, Recovery, Administration & Real-World Scenarios Senior Advanced

Detailed Explanation

A transaction log backup captures transaction log records not previously backed up in the log backup chain. Under the FULL or BULK_LOGGED recovery model, log backups are central to point-in-time recovery and log management.

Taking a full backup alone does not substitute for regular log backups when point-in-time recovery is required. A proper log backup chain must be maintained.

Code Example

BACKUP LOG SalesDb
TO DISK = 'D:\SqlBackups\SalesDb_Log.trn'
WITH CHECKSUM, COMPRESSION;