Production SQL Server databases generally require that data loss can be minimized in the event of the disaster. The amount of data loss is something that every administrator will need to understand from the business users who rely on the database. Whatever the answer, the solution is to manage your recovery point objectives (RPOs) through properly scheduled transaction log backups.
In this article, we’ll discuss how to create and manage your transaction log (T-Log) backups on Microsoft SQL Server 2017.
Transaction Log Backups in SQL Server
The Transaction Log is just like it sounds: A log of all transactions that have run against your database. As changes are made to a database, these records of the changes are added to the transaction log. A transaction log backup, again, is just like it sounds: A backup of all the transactions that ran against the database since the last transaction log backup.
These incremental backups allow administrators to track all the changes to the database and can be run as frequently as needed. The frequency of backups dictates the possible RPO. If you cannot withstand losing more than 30 minutes worth of work, then you’ll set your transaction log backup frequency no longer than every 30 minutes. It’s that simple.
Transaction Log backups cannot be used by themselves. Instead, they comprise a larger set of backups that include Full and possibly Differential backups. When restoring, the full database backup is restored, then a differential (if you’re using them), and then you can apply the necessary transaction log backups in sequence up to the point in time needed.
Even if a transaction log backup comprises 30 minutes of activity (say 3:00 - 3:30 PM), you can tell SQL Server you only want to restore transactions up until 3:22 PM. This is an important feature for point-in-time restores, as it allows the administrator to get a database restored to a point just before a problem occurred.
If you are running a database in a recovery model that keeps transactions (Full or Bulk Logged), then you need to run transaction backups. Without them, the old transactions are never cleared out, the amount of data in the transaction log continues to grow, and eventually, you’ll run out of the disk and crash the database.
Microsoft SQL Server provides a set of options that allow you to run backups:
- T-SQL is the SQL Server scripting language that provides BACKUP commands that can be scripted and executed from your query tool
- SQL Server Agent - is the SQL Server scheduling service that allows you to run scripts on schedule
- SQL Management Studio - is a graphical management console for SQL Server that provides an easy-to-use interface for managing and generating scripting actions, like backups, for your databases
Note: The SQL Server Agent is available for all SQL Server editions, except Express.
One more thing you should remember about T-Logs is a database recovery model. This mode allows choosing whether SQL Server will save all transaction logs or delete the older ones.
Creating Transaction Log Backups
It’s a simple matter to use Management Studio to create the scripts and scheduled jobs for your transaction log backups. Right-click on the desired database and select the Tasks - Back Up option. Change the Backup Type to Transaction Log and you're ready to go.
Then you can simply convert these backup settings to the scheduled job by clicking Script at the top:
Most of the settings are self-explaining, so let’s check Scheduling tab:
In the depicted example, the T-Logs backup job created will run every hour . If configuring full or differential backup runs, you may consider daily or weekly plans.
You can achieve the same using SQL server transaction log backup script. For example, paste the script below as a new step in New Job window of SQL Server Agent:
BACKUP LOG [DB_NAME] TO DISK = N'path_to_backup_file' WITH NOFORMAT, NOINIT, NAME = N'JOB_NAME', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO
Do not forget to replace DB_NAME with your database name, path_to_backup_file - with your backup path, and type the proper name for JOB_NAME. The script will append new backups to the end of specified backup file (NOINIT option). If you prefer to store one backup per script - use the INIT option instead.
Resulting Step’s settings:
The syntax for full or differential data copies will be quite similar - just replace LOG with DATABASE (full backup); if you need to create a differential copy, just add WITH DIFFERENTIAL to the script:
BACKUP DATABASE TestDB TO diff_backups WITH DIFFERENTIAL; GO
SQL Backup Log Truncation
T-Logs are quite small files (a few megabytes per file, depending on the SQL Server version), but SQL produces them very fast, so you can even run out of disk space in some poorly-planned environments with a high-load database.
In case of the T-Logs drive partition overfills, SQL Server will stop working and post this message to Windows Event Log:
“Microsoft OLE Provider for SQL Server: The transaction log for database “YourDBName” is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.database”
To overcome this issue, SQL Server allows either to automatically remove older logs (circular logging used in SIMPLE database recovery mode) or regularly create full database copies. The last option is used when DB is set to FULL recovery model. This mode instructs SQL Server to only delete transaction logs made right before the moment regular, full, or differential database backups started.
SQL Backup Transaction Log Automation with CloudBerry
CloudBerry Backup allows you to backup Microsoft SQL Server to the cloud storage of your choice and includes options for both file and image-based backup. The CloudBerry Backup SQL Server edition allows you to automate SQL Server Full, Differential, and T-Log backups.
Backing up your SQL Servers databases with CloudBerry is easy:
- Start the backup wizard by clicking MS SQL Server on the Home tab:
- Next, select whether you want to perform Local, Cloud, or Hybrid backups. For this example, we’ll perform a cloud backup to the Amazon S3 account. If you have not yet defined your storage, you can do so using this guide.
- Next, connect to the preferred SQL Server instance. CloudBerry Backup will automatically check whether the SQL Server account has the necessary permissions to perform database backups.
- Select databases you want to protect (all databases, all user databases, or a custom selection of databases).
- The last step is to define the type of backup to run or use one of our pre-defined templates which can easily automate all the backup types needed for your databases.
In most cases, we suggest using a Recurring (predefined templates) schedule that already includes sets of full, differential and logs backups, which you can slightly modify depending on your needs.
When protecting your databases from data loss and looking to manage your recovery time objectives, strongly consider running your database in Full r Bulk Logged Recovery and running transaction logs backups along with your scheduled full and differential backups. If running a database in FULL recovery mode, please assure that transaction logs are truncated regularly to avoid storage space overfill with subsequent SQL service outage.
Microsoft SQL Server allows you to automate backup tasks using the SQL Server Agent, but if you are looking to avoid having to create and manage T-SQL scripts and have a need to back up your databases to the cloud storage of your choice, consider using CloudBerry Backup for MS SQL Server. Start your free trial and see how CloudBerry can greatly improve your SQL Server backup experience.