How to back up a SQL Server transaction log

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.

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

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

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:

• SQL Server 2005 Migration Checklist
• Sysadmins: Who Needs a DBA? With Brent Ozar
• SQL Performance Tuning
• Are AlwaysOn Availability Groups Right for You?

Previous post  

  Next post