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

View posts related to category:

    Veeam Availability Suite — Download free 30-day trial

    • 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

      GD Star Rating
      loading...
      • Polina Vasileva

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

        GD Star Rating
        loading...
    • 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 ?

      GD Star Rating
      loading...
      • 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.

        GD Star Rating
        loading...
    • 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 🙂

      GD Star Rating
      loading...
      • 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?

        GD Star Rating
        loading...
        • 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?

          GD Star Rating
          loading...
          • 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.

            GD Star Rating
            loading...
    • Server1DK

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

      GD Star Rating
      loading...
      • 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.

        GD Star Rating
        loading...
    • 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.

      GD Star Rating
      loading...
      • Polina Vasileva

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

        GD Star Rating
        loading...
        • 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?

          GD Star Rating
          loading...
          • 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.

            GD Star Rating
            loading...
    • 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?

      GD Star Rating
      loading...
      • 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

        GD Star Rating
        loading...
        • 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?

          GD Star Rating
          loading...
          • 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.

            GD Star Rating
            loading...
    • 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.

      GD Star Rating
      loading...
    Polina Vasileva
    Author: 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... 

    Published: December 21, 2015