SQL Server Transaction Log Backups



By:
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
backup transaction log with ssms

Here is what this looks like in SSMS 17.

backup transaction log with ssms

Last Update: 2/22/2019






Comments For This Article




Sunday, June 18, 2017 - 8:06:13 AM - Greg Robidoux Back To Top (57614)

If the database is not being updated (no deletes, inserts or updates) and you take a full backup, since no data has changed you would not need to take a transaction log backup.  The full backup will have all transactions.

If data is still being changed, then a transaction log backup will have those new transactions.


Saturday, June 17, 2017 - 9:55:44 AM - CHAITANYA MULLAPUDI Back To Top (57540)

AFTER PERFORMING THE FULL BACK, IF I HAVEN'T DONE ANY CHANGES OR ANY TRANSACTIONS ON DATABASE, SHOULD I NEED TO TAKE A LOG BACKUP ALSO OR NOT, IF YES, WHY SHOULD I...????

 

I WILL BE WAITING FOR THE ANSWER.. PLZ MAKE IT FAST.


Thursday, May 04, 2017 - 2:32:55 AM - MD ALAM Back To Top (55425)

 

 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. but my question is how i ll get all my transaction log becoz my recovery model is bulk logged and my last transaction is bulk transaction so log will not be generate for bulk operation then how i ll get all transaction log to recovery the database? plz explain



download








get free sql tips
agree to terms


Learn more about SQL Server tools