Backup and DR
Microsoft SQL Server Transaction Log Backup
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 log file. Transaction log can be used to minimize data loss if the disaster or a database corruption occurs.
In this article, we’ll discuss how to create and manage your backups.
What Is a Transaction Log?
Transaction log is a list of records for all Microsoft SQL database changes. It is an essential component of the SQL Server database. If there is a system failure, you will be able to use the T-log to bring your database back to a consistent state before the point of failure.
What Is a Transaction Log Backup?
Transaction log backup is a backup technique that requires the copy of a transaction log. With it, you can recover the database to any point in time since the last T-log backup.
You need to perform a full backup before you can create any T-log backups.
In general, transaction log backup can be thought of as the equivalent of incremental backup, applied to SQL backup. In other words, it’s the granular backup that copies incremental changes in the given dataset. In essence, transaction log backup is enough to be able to recover a failed Microsoft SQL database to the working state. However, it is advisable to perform a full chain of MS SQL backup, including the full database backup, the differential backup, and up to the T-log backup up to the required point in time.
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 can bring the whole database to the hault. 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.
T-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. Transaction log truncation happens after each successful backup automatically.
Once backed up, the transaction log is cleared and space is now available for new transactions. Without backups, the log files will continue to grow until the drive runs out of space and you either will have to perform a backup immediately or clear the log files manually, which may end up in either the corruption of, or performance issues with your database.
If you need to restore the database, the data loss in full or bulk-logged recovery models will very likely be the transactions that ran after the last T-log backup.
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 a simple recovery model, you cannot back up transaction logs; instead, the truncation process happens automatically once the logs fill up the T-log file.
However, 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 circumstances, the database in a simple recovery model might experience less data loss.
SQL Server Transaction Log Backup Best Practices
Your 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.
Further reading Backup Best Practices for MSPs
Keep in mind the following recommendations:
Start with creating a full database backup.
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, otherwise they won't be created.
The main feature of any backup and disaster recovery plan is the ability to recover fast. But what exactly is a fast recovery? The business decision makers would want you to be able to recover the database to the nearest possible state in time, but the fact of the matter is, that you are not always able to perform backups at the wanted schedule. Sometimes your server is not ready to perform frequent backups, other times, you simply don’t have the required storage space.
Thus, to define the right schedule for your backups, you should get to the drawing board and define the essential disaster recovery metrics, the recovery time and the recovery point objectives. These will allow you to define, how critical your MS SQL data is, and propose the backup technique and frequency, that will be optimal for your business case.
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 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 log backups in sequence up to the needed point in time.
Even if a 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.
Learn in our whitepaper:
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 T-log backups. Otherwise, the old transactions are never cleared out and the amount of data in the 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 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.
Further reading 3-2-1 Backup: What It Means and How to Achieve It
Running an SQL Server Transaction Log Backup
There is a set of options that allow you to run T-log backups:
- T-SQL - the scripting language that provides BACKUP commands that can be scripted and executed from your query tool.
- SQL Server Agent - the scheduling service that allows you to run scripts on schedule.
- SQL Server Management Studio - a graphical management console 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 editions, except Express.
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 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 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
Backup Automation with MSP360
MSP360 Backup allows you to back up Microsoft SQL Server to the cloud storage location of your choice and automate full, differential, and T-log backups.
Note: Microsoft SQL Server 2008-2017 and SQL Server Express are supported in the MSP360 Backup for MS SQL Server and MSP360 Backup Ultimate editions.
Backing up your databases with MSP360 is easy by following these steps:
1Start the backup wizard by clicking MS SQL Server on the Home tab:
2Next, select whether you want to perform Local, Cloud, or Hybrid backups and select your backup storage location on the following wizard step. For this example, we’ll perform a cloud backup to an Amazon S3 account.
3Connect to the preferred SQL Server instance. MSP360 Backup will automatically check whether the account has the necessary permissions to perform database backups.
4Select databases you want to protect (all databases, all user databases, or a custom selection of databases).
5Specify the compression, encryption and retention policy settings in the next two steps. Then schedule the SQL Server backup process.
In Recurring schedules (where you can use either predefined templates or set an advanced schedule) you will be able to configure how often to perform transaction log backup (as well as full and differential backups).
6Let’s schedule backups to be performed hourly using Simple Recurring Schedule (to do this you will need to have selected Recurring (predefined templates) in the previous step):
7Specify the remaining settings in the next steps of the wizard, review your backup plan configuration and click Finish to create it.
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.
Microsoft SQL Server allows you to automate backup tasks using the Server Agent, but if you don’t want to create and manage T-SQL scripts and would like to back up your databases to the cloud storage location of your choice, consider using MSP360 Backup for MS SQL Server, which provides simpler and more flexible backup. Start your free trial and see how MSP360 can greatly improve your backup experience.
Simple. Reliable.
