Backup and DR
Microsoft SQL Server Transaction Log Backup
SQL Server stores records for all database changes in order to minimize data loss in the event that a disaster or a database corruption occurs. This type of record is called a transaction log, or T-log, and can be used for backup and recovery. In this article, we’ll discuss how to create and manage your transaction log backups in Microsoft SQL Server.
Overview of SQL Server Transaction Log Backup
What Is a Transaction Log?
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.
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.
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.
(See this Microsoft article for SQL Server recovery models overview.)
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.
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 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.
Further reading 3-2-1 Backup: What It Means and How to Achieve It
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.
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
SQL Server Transaction Log Backup Automation with MSP360
MSP360 Backup allows you to back up Microsoft SQL Server to the cloud storage location of your choice and automate SQL Server full, differential, and T-log backups.
Backing up your SQL Server 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 SQL Server 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 transaction log 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.
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 SQL 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 SQL Server backup. Start your free trial and see how MSP360 can greatly improve your SQL Server backup experience.
MSP360 offers an easy-to-use and reliable solution for SQL Server backup to the cloud or local storage.
Compression and Encryption
Compression allows you to reduce storage space (and thus save money) while improving backup time. With AES-256 encryption, you can be sure that all your backup files are protected.
Cloud and Local
MSP360 Backup allows you to store your backups on local storage and any of more than 20 cloud storage providers, including Amazon S3 and Amazon Glacier, BackBlaze B2, Wasabi Hot Storage and Microsoft Azure.
Flexible Retention and Recovery
Store as many versions as you need for as long as you need with flexible retention settings. Recover the latest version or restore to the point in time of your choosing.
Emergency Recovery for Windows Server