{"id":33614,"date":"2020-11-29T20:02:43","date_gmt":"2020-11-29T16:02:43","guid":{"rendered":"https:\/\/www.msp360.com\/resources\/?p=33614"},"modified":"2025-05-07T13:31:29","modified_gmt":"2025-05-07T09:31:29","slug":"sql-server-transaction-log-backup","status":"publish","type":"post","link":"https:\/\/www.msp360.com\/resources\/blog\/sql-server-transaction-log-backup\/","title":{"rendered":"Microsoft SQL Server Transaction Log Backup"},"content":{"rendered":"

A transaction log<\/strong>, also called a T-log<\/strong>, 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.
\n<\/p>\n

In this article, we\u2019ll discuss how to create and manage your backups.<\/p>\n

What Is a Transaction Log?<\/h2>\n

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.<\/p>\n

\n
\n
FREE WHITEPAPER<\/div>\n
SQL Server Backup and Recovery<\/div>\n
Check out the ultimate guide with best practices for MSPs:<\/div>\n\"CTA\"<\/a><\/span><\/span>\n<\/div>\n
\"Exchange<\/div>\n<\/div>\n

What Is a Transaction Log Backup?<\/h2>\n

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.<\/p>\n

You need to perform a full backup before you can create any T-log backups.<\/p><\/blockquote>\n

In general, transaction log backup can be thought of as the equivalent of incremental backup<\/a>, applied to SQL backup. In other words, it\u2019s 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<\/a>, the differential backup, and up to the T-log backup up to the required point in time<\/strong>.<\/p>\n

<\/a>T-log Backup and Truncation in Different Recovery Models<\/h2>\n

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.<\/p>\n

\u00a0<\/a>\"CTA\"<\/a><\/span><\/span><\/div>\n

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.<\/p>\n

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.<\/p><\/blockquote>\n

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.<\/p>\n

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.
\nA bulk-logged model<\/strong> 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.<\/p>\n

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.<\/p><\/blockquote>\n

\u00a0<\/a>\"CTA\"<\/a><\/span><\/span><\/div>\n

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.<\/p>\n

SQL Server Transaction Log Backup Best Practices<\/h2>\n

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.<\/p>\n

Further reading<\/span> Backup Best Practices for MSPs<\/a><\/p>\n

Keep in mind the following recommendations:<\/p>\n

Start with creating a full database backup.<\/p><\/blockquote>\n

Backups cannot be used by themselves. Instead, they should be used along with full and differential SQL Server backups<\/a> (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.<\/p>\n

The main feature of any backup and disaster recovery<\/a> 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\u2019t have the required storage space.<\/p>\n

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 (RTO and RPO)<\/a>. 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.<\/p><\/blockquote>\n

If you cannot withstand losing more than 15 minutes' worth of work, then you\u2019ll set your transaction log backup frequency in such a way that backups occur at least every 15 minutes. It\u2019s that simple.<\/p>\n

If you cannot withstand losing more than 15 minutes' worth of work, then you\u2019ll set your transaction log backup frequency in such a way that backups occur at least every 15 minutes. It\u2019s that simple.<\/p>\n

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.<\/p>\n

Use transaction log backups for point-in-time recovery and maximum data protection in case of a database disaster.<\/p><\/blockquote>\n

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<\/strong>.<\/p>\n

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.<\/p>\n

Develop a backup schedule<\/a>.<\/p><\/blockquote>\n

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.<\/p>\n

\n
\n
FREE WHITEPAPER<\/div>\n
Guide to Backup Types<\/div>\n
Which type of backup is right for your needs?
\nLearn in our whitepaper:<\/div>\n\"CTA\"<\/a><\/span><\/span>\n<\/div>\n
\"Whitepaper<\/div>\n<\/div>\n

If you are running a database using a recovery model that doesn\u2019t 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.<\/p>\n

Do not store backups on the same site as a production database.<\/p><\/blockquote>\n

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.<\/p>\n

Further reading<\/span> 3-2-1 Backup: What It Means and How to Achieve It<\/a><\/p>\n

Running an SQL Server Transaction Log Backup<\/h2>\n

There is a set of options that allow you to run T-log backups:<\/p>\n