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.
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.
Click Applications to configure SQL-specific processing options.
Under the Edit settings, choose Process transaction logs with this job.
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.
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.
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.
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.
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:
25 Comments
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
Hi Sam,
Thanks for noticing this. AutoClose is False by default; however, it’s a good practice to double check this setting.
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 ?
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.
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 :-)
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?
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?
Should there be a .vlb file for each log backup?
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.
Hi Jan,
Veeam truncates logs with the “Backup logs periodically” option selected.
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?
Hi! My apologies for the late response. Unfortunately, I somehow missed your comment.
Correct, each log backup is stored as a .vlb file.
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.
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.
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?
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
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?
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.
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.
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.
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.
How do I get these logs off site? The “Backup Copy” job doesn’t seem to be doing it.
This won’t work that well because the other files the “Backup Copy” does look at are also in the same directory.
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 ?
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