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.
