OverviewIf 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.

ExplanationA 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 a transaction log backup of the AdventureWorks database to one disk file
T-SQL

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