SQL Server Transaction Log Backups

By: Greg Robidoux

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 SSMS 17.