TRANSACTION LOG Backups in SQL Server Step by Step Tutorial with Examples
A transaction log, also called a T-log, is a running list of transactions that change either the data in the database or the structure of the database. Each database modification is a transaction; as changes are made to a database, log records are added to the transaction log file.
The transaction log is an essential component of the SQL Server database. If there is a system failure, you will need the T-log to bring your database back to a consistent state before the point of failure.
Transaction log backup is only possible when your database is in FULL or BULK-LOGGED recovery model. With the help of Transaction Log backup one can achieve Point in Time recovery for the database in case of any disaster It is highly recommended to perform transaction log backups at regular intervals to minimise the loss of work and to truncate the transaction log.
What Is a Transaction Log Backup?
A transaction log backup is a backup of all the transactions that have occurred in the database since the last transaction log backup was taken.
**You need to perform a full backup before you can create any T-log backups.
T-log backups can be thought of as the equivalent of incremental backup. They are the most granular type of SQL Server backup that allows you to perform point-in-time restores, thereby providing maximum data protection.
In case of a database disaster you can recover the full database backup, the last differential backup, and the transaction log backup created after the differential backup up to the required point in time.
A transaction log (T-log) backup is the most granular backup type in SQL Server because it backs up the transaction log which only contains the modifications made to the SQL Server database since the last transaction log backup. It’s effectively an incremental backup.
You can perform a transaction log backup as often as every few minutes, which will allow you to perform a point-in-time restore and minimize data loss.
T-log Backup and Truncation in Different Recovery Models
Transaction log records tend to grow over time and may fill up the entire disk, which prevents further modifications to the database. T-log truncation clears the log by marking the space in the log file as reusable, so that new transactions can be written to the log file.
Transaction log truncation does not free up disk space. Instead, it makes the previously used log space available for new transactions. You should avoid shrinking the physical log files, as that process can cause database performance issues.
**If you run a database in full or bulk-logged recovery model, then transaction logs must be backed up; T-log truncation happens after the backup.
Point-in-Time Restore
If you are performing regular transaction log backups, you can recover to a point right before a problematic transaction occurred, such as an incorrect deletion or update of data in a table. To perform a point-in-time restore you’ll have to do the following:
- Restore the last full backup.
- (Optional) Restore the differential backup that is closest to the moment of failure.
- Restore transaction log backups in sequence made from the last full backup (or from the last differential backup if you are using them) and the failure.
Create a TRANSACTIONAL LOG Backups Using TSQL commands
BACKUP LOG MyTechMantra
TO DISK = 'C:\DBBackups\MyTechMantra.TRN'
WITH NOFORMAT, NOINIT,
SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 25
/* Verify the Database Backup File Once the Backup has completed successfully */
RESTORE VERIFYONLY
FROM DISK = 'C:\DBBackups\MyTechMantra.TRN'
GO
Create a TRANSACTIONAL LOG Backup Using SQL Server Management Studio
- Open SQL Server Management Studio, connect to the appropriate SQL Server Database Instance, in Object Explorer, click the SERVER NAME to expand the server tree.
- Expand Databases node and then right click the database which you want to take a transaction log backup.Select Tasks, and then click Back Up…option as shown to open up Back Up Database dialog box.Select Backup Type as Transaction Log and Select Destination as Disk
- Click on Add button to add a backup file and specify the backup file name and click OK to save the changes
- Finally to take Transaction Log backup click OK
Once backed up, the transaction log is cleared and space is now available for new transactions. Without transaction log backups, the log files will continue to grow until the drive runs out of space.
If you need to restore the database, the data loss in these two models will very likely be the transactions that ran after the last transaction log backup.
More frequent T-log backups reduce the possibility of data loss.
A bulk-logged model might not allow a point-in-time recovery if a minimally logged operation took place. This is why in most cases it is not recommended to use a bulk-logged model.
**If you run a database in simple recovery model, you cannot back up transaction logs; instead, the truncation process happens automatically.
However, in an unusual case, you could have a database in full recovery model running T-log backups every 4 hours and a database in simple recovery model running differential backups every hour. Under these circumstance, the database in simple recovery model might experience less data loss.
SQL Server Transaction Log Backup Best Practices
Your SQL Server backup and recovery strategy should minimize the potential for data loss and maximize data availability. At the same time, it should reflect specific business requirements, and be able to adapt according to resource availability.
Keep in mind the following recommendations and SQL Server transaction log backup best practices:
Start with creating a full database backup.
Transaction log backups cannot be used by themselves. Instead, they should be used along with full and differential SQL Server backups (differential backup is optional but strongly recommended). The full backup serves as the starting point for subsequent differential and transaction log backup in SQL Server. Differential and transaction log backups cannot be created if the full database backup has never been created.
The appropriate frequency for performing log backups will depend on the required RTO and RPO. These metrics reflect the amount of data loss that your business can tolerate without suffering serious repercussions. They should also reflect how many log backups you can store, manage, and (when necessary) restore given the resources available to you.
If you cannot withstand losing more than 15 minutes’ worth of work, then you’ll set your transaction log backup frequency in such a way that backups occur at least every 15 minutes. It’s that simple.
If your business requires you to minimize your risk of data loss, perform log backups more frequently. Doing so offers the added advantage of increasing the frequency of log truncation, resulting in smaller log files.
Use transaction log backups for point-in-time recovery and maximum data protection in case of a database disaster.
If a disaster occurs, you will first recover the full database backup, and then recover the last differential backup. After that, you can restore the necessary transaction log backups in sequence up to the needed point in time.
Even if a transaction log backup comprises 30 minutes of activity (say 3:00 – 3:30 PM), you can tell SQL Server you only want to restore transactions up until 3:22 PM. This is an important feature for point-in-time restores, as it allows the administrator to restore a database to a point just before a problem occurred.
**Develop a backup schedule.
To limit the number of log backups that you need to restore, it is necessary to routinely back up your data. For example, schedule a weekly full database backup and daily differential database backups.
If you are running a database using a recovery model that doesn’t automatically truncate the transaction logs (full or bulk-logged models), you need to run transaction log backups. Otherwise, the old transactions are never cleared out and the amount of data in the transaction log will continue to grow. Eventually, you will run out of the disk space and MS SQL Server will not let you make any more modifications to the database.
**Do not store SQL Server transaction log backups on the same site as a production database.
If a transaction log is damaged, work that has been performed since the most recent valid backup is lost. You may face server room physical damage or force-majeure circumstances, which happen at a much higher frequency than you might think. The best solution is to mix both local and off-site backups using, for example, cloud storage systems.
Running an SQL Server Transaction Log Backup
Microsoft SQL Server provides a set of options that allow you to run T-log backups:
- T-SQL – the SQL Server scripting language that provides BACKUP commands that can be scripted and executed from your query tool.
- SQL Server Agent – MS SQL Server scheduling service that allows you to run scripts on schedule.
- SQL Server Management Studio – a graphical management console for MS SQL Server that provides an easy-to-use interface for managing and generating scripting actions, like backups, for your databases.
**The SQL Server Agent is available for all SQL Server editions, except Express.
Read on for examples of using SQL Server Management Studio and SQL Server Agent to create scripts and scheduled jobs for your transaction log backups.
How to Create T-log Backups Using SQL Server Tools
Setting up SQL Server Management Studio to create the scripts and scheduled jobs for your transaction log backup in SQL Server is simple. Follow the steps below.
1. Right-click on the desired database and select the Tasks – Back Up option. Change the Backup Type to Transaction Log and you’re ready to go.
2. Then you can simply convert these backup settings to the scheduled job by clicking Script at the top:
3. Most of the settings are self-explanatory, so let’s check the Scheduling tab:
In the depicted example, the T-log backup job created will run every hour. (If configuring full or differential backup runs, you may consider daily or weekly plans.)
You can achieve the same goal using a SQL server transaction log backup script. For example, paste the script below as a new step in the New Job window of SQL Server Agent:
BACKUP LOG [DB_NAME] TO DISK = N'path_to_backup_file' WITH NOFORMAT, NOINIT, NAME = N'JOB_NAME', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO
Remember to replace DB_NAME with your database name, replace path_to_backup_file with your backup path, and type the proper name for JOB_NAME. The script will append new backups to the end of the specified backup file (NOINIT option). If you prefer to store one backup per script, use the INIT option instead.
4. Resulting Step’s settings:
The syntax for full or differential data copies will be quite similar – just replace LOG with DATABASE (full backup); if you need to create a differential copy, just add WITH DIFFERENTIAL to the script:
BACKUP DATABASE TestDB TO diff_backups WITH DIFFERENTIAL; GO
Summary
When protecting your databases from data loss and looking to manage your recovery time objectives, strongly consider performing regular backups. The schedule and the database recovery model will depend on your business requirements, such as how much data loss your business can tolerate.
If your database runs in full or bulk-logged recovery model, performing T-log backups more frequently reduces possible data loss and prevents storage space overfill by the growing log file. However, the bulk-logged recovery mode cannot provide point-in-time restore if bulk-logged operations have occurred since the most recent log backup.