How to back up a SQL Server transaction log

What is transaction log backup for?

SQL databases are the heart and soul of your ERP and CRM systems, as well as many other critical business applications. Their downtime and lost data mean financial losses in hours of employee work, lost deals and profits. The more data you want to save in case of downtime, the more reasons you have to create SQL-transaction log backups. They maximize SQL Server availability, enabling database restores to any point-in-time.

Native SQL transaction log backup

You can back up SQL transaction logs by either native Microsoft tools or 3rd party solutions. Native tools include SQL Server Management Studio, Transact-SQL (or T-SQL), SQL Server Agent jobs, SQL Server Maintenance Plans and PowerShell scripting. For more information on using these options, I suggest you check out Microsoft documentation.

How to use Veeam to backup SQL transaction logs

Let’s focus on Veeam this time. Veeam Backup & Replication (a part of Veeam Availability Suite) can take care of your SQL transaction logs, as well as your entire SQL Server. It provides application-aware image processing for consistent SQL Server backups and supports transaction log backups and truncation. For virtualized SQL Server 2012 and 2014, Veeam also supports AlwaysOn Availability Groups.

Preparing your SQL database for backup

Your database properties should support transaction logging. SQL Server controls transactions logging through recovery models. A Simple recovery model goes without any transaction log and doesn’t allow for point-in-time recovery. Veeam Backup & Replication by default excludes simple-mode databases from log processing jobs. Only databases in Full or Bulk-logged recovery models have their changes recorded to a SQL transaction log file, making recovery to a specific point-in-time possible. Make sure your database is in a Full or Bulk-logged recovery mode.

Database recovery model

Setting up a backup job for SQL Server VM

In Veeam Backup & Replication, transaction log backup jobs are a subtask of a SQL Server VM backup job. So, first create and configure a backup job for your SQL Server VM. To create a transaction-consistent SQL Server backup, enable application-aware image processing.

Application-aware image processing

Click Applications to configure SQL-specific processing options.

AAIP options

Under the Edit settings, choose Process transaction logs with this job.

Server processing options

Log files are growing in size along with the database load, making regular SQL transaction log backups necessary to control the log (.LDF) file size. Depending on the amount and frequency of changes, the .LDF file can expand more than twice as big as the database itself. If allocated storage space runs out, new transactions won’t start. When you’re backing up an SQL transaction log, it’s truncated and reuses storage space. Log backup and truncation prevents the storage space overflow.

Backup jobs

On the SQL tab, choose how you’d like to manage SQL transaction logs. The set of available options addresses nearly any SQL requirement. For highly-loaded SQL Server databases, DBAs back up transaction logs every 15 minutes or less. Low RPOs are easy to achieve because log files contain only changes to a database. Its backups are much faster even than incremental SQL Server backups, and they don’t affect production environment.

SQL Server processing options

In Veeam Backup & Replication, SQL transaction log backup jobs are an interval background process. It’ll automatically start every time according to your defined schedule.

Background job processing

Where are SQL transaction log backups stored?

Log backups are stored as a .VLB files in your backup repository along with corresponding SQL Server VM backups.

Backup repository

Summary

Veeam Backup & Replication provides you a full image-based SQL Server VM backup with a chain of increments and a chain of SQL log backups. For SQL database recovery, you can use Veeam Explorer for Microsoft SQL. It supports database restore scenarios from the latest backup restore point, from a log replay to a specific point-in-time, and from a log replay to a specific transaction. In our Veeam Blog, there’s a great post on SQL database recovery with Veeam Explorer by Rick Vanover, which I highly recommend to you. Also, see our User Guide for all the little details about SQL transaction logs backup.

Thank you for your time and, as usual, your comments are more than welcome!


See also:

V11

Eliminate Data Loss
Eliminate Ransomware

#1 Backup and Recovery

Start free trial

25 Comments

  • Sam says:

    One other required setting i noticed is to ensure the database setting “Auto Close” is set to False. I ran into this on an inherited environment, and Veeam auto-excluded the database

  • Polina Vasileva says:

    Hi Sam,
    Thanks for noticing this. AutoClose is False by default; however, it’s a good practice to double check this setting.

  • Bharath Ramanathan says:

    just trying to understand “Only databases in Full or Bulk-logged recovery models have their changes recorded to a transaction log file, making recovery to a specific point-in-time possible” – you mean to say Veeam can be used to recover DBs to point-in-time even though MSFT doesnt support recovery to point in time with bulk logging?
    Is this a veeam exclusive feature ?

  • Andrew Zhelezko says:

    Thanks for the question. No, we’re aligned with MSFT here. What we say is that if your DB is working in bulk-logged recovery model, we’re able to catch and backup its transaction log and then we can try to restore it. Of course, it won’t be possible to restore to point in time if there are bulk-logged operations, performed with DB and stored in a log. Otherwise, it’s possible and we’re going to do that.

  • Joey Blaydes says:

    Question, With the setting in your post above, is it an option to do a Backup Copy job “hourly” of this listed job in your article (that has a destination of “Off Site” and capture the Transaction Logs in the Backup Copy job for a RPO of 1 Hour if (worse Case scenario) we become dependent on the Backup Copy job? I hope this question makes sense, …….still working on my first cup of coffee :-)

  • Andrew Zhelezko says:

    Hi Joey,
    I hope the coffee worked fine and you can clarify the question for me:D Would you like to set Backup Copy job to protect SQL transaction log along with VM backups?

  • Joey Blaydes says:

    I think my answer is Yes.
    I have an SQL server in production and I make daily Backups (local) of this server with “Hourly” Transaction logging backup. I also have a daily replication job of the SQL server that has a destination of our offsite recovery center. In addition to what I have in place, I would also like to setup a Backup Copy to our Offsite Datacenter and know that I’m capturing the hourly transaction log backup every hour. Having a Replica offsite gives me an RPO of 24 hours, I would like to Utilize BackupCopy for a RPO of 1 hour if possible. Can that be done with Veeam?

  • Server1DK says:

    Should there be a .vlb file for each log backup?

  • Jan Hollemans says:

    Jan Hollemans
    Question:
    Normaly we backup in SAP the transactionlog every 15 min. including a truncate so the space needed for the logfile is in proportion.
    When we will replace this by VEAAM and schedule every 15 min. a logbackup as far as i can see there is NO truncate to select.
    This means that the logfiles will grow the whole day.
    Do you have a solution for this?
    Hope to hear from you soon.

  • Polina Vasileva says:

    Hi Jan,
    Veeam truncates logs with the “Backup logs periodically” option selected.

  • Jan Hollemans says:

    Hi Polina,
    Thanks for your reply.
    We have tested in a backup job with application awearness to backup periodically the logfiles and it does but only truncate it at the end of the day when the job finish.
    Is there a solution that after every logbackup, so every 15 min., it also gets a truncate?

  • Polina Vasileva says:

    Hi! My apologies for the late response. Unfortunately, I somehow missed your comment.
    Correct, each log backup is stored as a .vlb file.

  • Polina Vasileva says:

    Hi Jan,
    In fact, log truncation should happen (and happens) after every log backup. I’d suggest you contact Veeam Support to resolve this issue.

  • Polina Vasileva says:

    Hi Joey,
    Better late than never :)
    The thing is, Backup Copy jobs don’t process transaction logs files. Instead, you can use a file copy option.

  • Rich Leick says:

    We are just starting to look at this. In our existing model, our transaction log backups are configured to go to a different partition on the server (T). When we attempt to use VEEAM, they go to the C drive (ugh). How can we specify that the transaction logs get created on a different partition?

  • Polina Vasileva says:

    Hi Rich,

    Glad to know you’re starting to use Veeam!

    How is your Backup Job configured? Do you use the C drive as your primary Veeam backup repository? Veeam stores transaction logs in the same location as SQL Server backups. The target repository can be changed in the backup job settings.

    You may also want to check Veeam Help Center for more information on transaction logs settings: https://helpcenter.veeam.com/backup/vsphere/backup_job_vss_sql_vm.html

  • Rich Leick says:

    Polina,
    Thanks for responding. From a SQL Server setup, the default locations for data, log, and backup are all configured to different partitions (e.g. D, T, L). However, it does not appear Veeam uses these settings because when we initially configured and ran it, the transaction log backups were created on C instead of the default T and caused our C drive to run out of space. We have reviewed the help documentation but we did not find a way to change/verify. We did contact support and they mentioned we need to change a registry setting. That does not seem to make sense since we have configured our SQL server defaults to use different partitions. Do we really have to change a registry setting for this?

  • Polina Vasileva says:

    Rich,

    I’d definitely follow the support team guidance as they had a chance to review your situation a bit deeper and suggested the most applicable workaround.

  • Polina Vasileva says:

    Hi Jose,

    Unfortunately, I can’t suggest you any better way to solve the issue, other than updating the registry. For now, the information on the forum is the most relevant.

  • JohnHMET says:

    Does Veeam have the facility to do a one-off transaction log backup and it go to the Veeam repository like the other scheduled Veeam log backups (maintaining the log chain in Veeam)? I am a Senior SQL DBA and have different reasons for needing to do immediate out-of-schedule log backups for log file maintenance. With Native SQL Server backups to disk, this is easy to do and maintain the log chain. But with Veeam controlling the log chain, I need an easy way to do ad-hoc log backups that maintain the chain in Veeam. Let me know. Thanks.

  • Polina Vasileva says:

    Hi JohnHMET,

    Unfortunately this scenario isn’t currently supported. You can request this feature at Veeam forums https://forums.veeam.com/veeam-backup-replication-f2/ and our R&D team will take it into account for future releases.

  • Andrew says:

    How do I get these logs off site? The “Backup Copy” job doesn’t seem to be doing it.

  • Andrew says:

    This won’t work that well because the other files the “Backup Copy” does look at are also in the same directory.

  • Goos says:

    We have 2 user databases on a SQL Server 2012 SP3 server. Both databases are in Full Recovery Mode and still they appear as “Excluded” in the transaction log backup and as a result the transaction log grows without being truncated.
    The only databases that appear as “Protected” are the model and ReportServer databases, that are also in Full Recovery Mode.
    Why are both our user databases excluded from the transaction log backup ?

  • Polina Vasileva says:

    Hi Goos,
    In order to investigate this issue properly, I’d advise you to open a case with Veeam support team: https://cp.veeam.com

Leave a Reply

Your email address will not be published.