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.

You can handle 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 TechNet and MSDN.

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 log backups and truncation. For virtualized SQL Server 2012 and 2014, Veeam also supports AlwaysOn Availability Groups.

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 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

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, transaction log backup jobs are an interval background process. It’ll automatically start every time according to your defined schedule.

Background job processing

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

Backup repository

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 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.

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

See also:

GD Star Rating
loading...
How to back up a SQL Server transaction log, 4.9 out of 5 based on 7 ratings
Polina Vasileva
Polina Vasileva
Polina Vasileva has a deep understanding of a wide range of IT technologies and platforms, combined with comprehensive marketing, sales, project-management and partner-development experience. Polina manages global-marketing campaigns and keeps a close eye on IT market trends in her role at Veeam. Follow Polina on... 

Veeam Availability Suite — Download free 30-day trial

Download now
  • Sam

    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

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

  • Bharath Ramanathan

    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

      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

    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

      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

        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?

        • Polina Vasileva

          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.

          • Andrew

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

  • Server1DK

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

    • Polina Vasileva

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

  • Jan Hollemans

    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

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

      • Jan Hollemans

        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

          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.

  • Rich Leick

    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

      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

        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

          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

    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

    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.

  • Andrew

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

  • Goos

    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

      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