Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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.

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

Last Update: 3/9/2009




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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


Learn more about SQL Server tools