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

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




Tuesday, May 07, 2019 - 4:12:52 PM - T Back To Top
Tip Comments Pending Approval

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

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

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

 

 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