SQL Server Transaction Log Backups

Overview

If your database is set to the “Full” or “Bulk-logged” recovery model then you will be able to issue “Transaction Log” backups.  By having transaction log backups along with full backups you have the ability to do a point in time restore, so if someone accidently deletes all data in a database you can recover the database to the point in time right before the delete occurred.  The only caveat to this is if your database is set to the “Bulk-logged” recovery model and a bulk operation was issued, you will need to restore the entire transaction log, so you cannot do a point in time restore using a transaction log backup that contains a bulk-logged operation.

Explanation

A transaction log backup allows you to backup the transaction log.  After the transaction log backup is issued, the space within the transaction log can be reused for other processes.  If a transaction log backup is not taken and the database is not using the Simple recovery model the transaction log will continue to grow.

A transaction log backup can be completed either using T-SQL or by using SSMS.  The following examples show you how to create a transaction log backup.

Create SQL Server Transaction Log Backup to one disk file

T-SQL

This will create a transaction log backup of the AdventureWorks database and write the backup contents to file “C:\AdventureWorks.TRN”.  The .TRN extension is commonly used for identifying that the backup is a transaction log backup.

BACKUP LOG AdventureWorks TO DISK = 'C:\AdventureWorks.TRN'
GO

SQL Server Management Studio

• Right click on the database name
• Select Tasks > Backup
• Select “Transaction Log” as the backup type
• Select “Disk” as the destination
• Click on “Add…” to add a backup file and type “C:\AdventureWorks.TRN” and click “OK”
• Click “OK” again to create the backup

Here is what this looks like in a different version of SSMS.

Greg Robidoux

Greg Robidoux has been working with databases for 35+ years with extensive hands on SQL Server experience from version 6.5 to 2025. He has authored over 250 technical articles and delivered several presentations online and at various conventions. Greg is also the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server.