InfoQi

The basic syntax for a transaction log backup to a disk file is:

BACKUP LOG [database_name]
TO DISK = ‘file_path\file_name.trn’
WITH NOINIT, COMPRESSION;
GO

• [database_name]: Replace with the actual name of your database (e.g., AdventureWorks).
• 'file_path\file_name.trn': Replace with the full path and desired filename for your backup file (e.g., 'C:\SQLBackups\MyDB_logs.trn'). The .trn extension is a common convention but not mandatory.
• WITH NOINIT: Appends the new log backup to the end of the existing backup file, which is useful for collecting all log backups in one file.
• WITH COMPRESSION: (Optional) Compresses the backup file, saving storage space.
• GO: A SQL Server Management Studio (SSMS) command to signal the end of a batch. 

Key Considerations

• Prerequisites: A full backup must exist before you can take the first transaction log backup.
• Recovery Model: Ensure the database’s recovery model is set to FULL or BULK_LOGGED. You can check this in SSMS by right-clicking the database, selecting Properties, and going to the Options page.
• Log Truncation: A successful transaction log backup marks the inactive log records as available for reuse, which helps prevent the log file from growing indefinitely.
• Point-in-Time Recovery: Transaction log backups, when used in sequence with a full backup, enable you to restore a database to a specific point in time, minimizing data loss.
• Tail-Log Backups: If a database is damaged but the log file is intact, a special tail-log backup can be taken (using WITH NO_TRUNCATE or WITH NORECOVERY options) to capture the final active log records and prevent work loss before recovery begins. 

For more information and advanced options, refer to the Microsoft Learn documentation on the BACKUP statement. 

Example:

Before Log backup:

SELECT
name AS FileName,
size1.0/128 AS AllocatedSpaceMB, size1.0/128 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS INT)*1.0/128 AS UnusedSpaceMB,
physical_name AS PhysicalLocation
FROM
sys.database_files;

FileName AllocatedSpaceMB UnusedSpaceMB PhysicalLocation
EpicorERPPilot 588800.000000 69513.437500 M:\SQL\Data\EpicorERP.mdf
EpicorERPPilot_log 79872.000000 26.656250 L:\SQL\Logs\EpicorERP_Log.ldf

BACKUP LOG EpicorERP
TO DISK = ‘S:\SQL\Backups\EpicorERP_LogBackup_021326.trn’
WITH NOINIT, COMPRESSION;
GO

Processed 10211251 pages for database ‘EpicorERP’, file ‘EpicorERPPilot_log’ on file 1.
BACKUP LOG successfully processed 10211251 pages in 1718.846 seconds (46.412 MB/sec).

Completion time: 2026-02-13T08:43:42.8928785-06:00

Check Log size after the Log backup – UnusedSpaceMB significantly increased (79379 MB)