SQL Server 2016 and Transaction Log Backup

transaction log backup SQL server

Backing up SQL Server transaction logs may not end up on the cover of Newsweek. But protecting transaction logs has a big impact on database recoverability, and on your ability to recover within Recovery Time and Point Objectives (RTO and RPO respectively). There is a whole universe of threats out there — failing storage media, user error or malice (more common than you might think), server failures, and natural disasters. Backing up the transaction logs as well as the databases lets you beat those risks. You can also use database and transaction log backups to efficiently archive or mirror databases.

Let’s set the stage for this post: we’re looking at SQL Server 2012 databases that are using full or bulk-logged recovery models. Both full and bulk-logged recovery models use the transaction log for internal backup and restores; the simple recovery model does not, and is dependent on restoring from external backup.

You should be using a full recovery model for normal operations, since it logs all transactions for optimal protection and recovery. The bulk-logged recovery model is a high performance mode for large bulk operations, such as bulk-importing big data stores. However, it sacrifices full transactional logging in order to preserve fast performance. Since you should only be using the bulk-logged model intermittently, this temporary exposure is usually an acceptable level of risk.

Protecting Transaction Log Backup

It seems odd to discuss backing up the backup, but your transaction log is pure data protection gold. If you do not back it up and something happens to the database, you have no recourse other than restoring from your last database backup. And unless you are doing continuous backup to external storage, you are going to lose hours to days of transactional data. Backup your transaction log backup.

Backing Up the Transaction Log

  • Backup the whole database first. Create at least one full database backup before trying to create transaction log backups. Once you’ve done that, you can back up the transaction log at will. Apart from this initial full backup, transaction log backup is independent from database backup.
  • Schedule transaction log backups according to your RPOs. Frequent backup both protects the data contained in the transaction log, and also minimizes the log size. A typical schedule is a full database weekly backup, daily or hourly incremental backups, and more frequent transaction log backup. 10 minutes is not too often when you are talking about meeting RPO. Not every database needs every 10 minutes; some will take more or less.
  • Prioritize backup. You may have multiple SQL Server databases and don’t want frequent transaction log backups to impact your server. Make individuals scheduling decisions based on database priority (mission-critical, business-critical, general), how big the database is, and how workloads impact your server.
  • Protect your backup. Store your transaction log backup on highly available and fault-tolerant storage, and make sure that the backup storage is separate from database storage. If you find that your transaction log backups are growing too fast, the culprit might be the default success messages that SQL Server adds to completed transaction log operations. Backup operations copy those messages too. You can suppress those entries using trace flags.

Recovering from Transaction Log Backup

  • Verify that you have enough log backups to create a restore sequence. A restore sequence combines multiple RESTORE statements into a single operation that sequences copied data, rolls it forward, and brings it online when complete. Catalog views, SQL’s internal management database (MSDB), and transaction log backups can all verify that you have a sufficient restore sequence.
  • Maintain the correct restore order. Although transaction log backup is independent of database backup, you can only restore them after you restore immediately previous full or incremental database backups. Once these backups are restored then you can recover the transaction logs. When the database and transaction logs have been restored, the database will come back online.
  • Test often. Finally, periodically test your transaction log backup and restore. Until you do, you have no way of knowing if your data is really available, or if you can restore it on time to meet RPO and RTO. The last thing you want to find out is that you just lost 24 hours’ worth of transactions because you didn’t test transaction log backup and recovery.
  • Make sure that your backup service provider backups up transaction logs. For a lot of businesses, hiring a backup service provider can tame complexity and free up IT staff time for strategic projects. However, be sure that the SP you choose automates SQL transaction backups on frequent schedules, as well as backing up databases.