SQL Server Transaction Log Backups
By: Greg Robidoux
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.
A transaction log backup allows you to backup the active part of the transaction log. So after you issue a "Full" or "Differential" backup the transaction log backup will have any transactions that were created after those other backups completed. 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, 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
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.
Last Update: 2/22/2019