When backing up your SQL Server database, you have the option of taking transaction log backups. These are quick, small backups containing a sequence of all your database’s changes. Transaction log backups are mightily useful for point-in-time recovery.

A transaction log backup is literally what it says on the tin – it is a backup of your database’s transaction log file. Normal database backups only allow you to recover data up to the point of the backup. As you use your database, all the data in between the backup periods at risk and are not backed up until the next backup. For some databases, this is simply not good enough, for example, commercial bank databases. Read more about transaction log backups here.

So what to do? For many database setups, it is impractical to take several backups during the day – even if these are differential backups. It is far more useful and much quicker to take transaction log backups. Since your transaction logs always keep a copy of all committed transactions on your database, you are guaranteed that all changes to your database are included when you back up your transaction log. The downside (there’s always one, right) is that SQL Server database recovery using transaction logs will usually take longer than using full or even differential backups. But for most, this is an acceptable price to pay.

To take a transaction log backup, you first require two things:

• You need a full database backup. This makes sense; remember that the transaction log backup only records the actual transaction, not the entire database. Thus it follows that you must first have a full backup to restore the transactions.
• Your database’s recovery mode must be FULL or BULK-LOGGED. To set this option, right-click your database while in SQL Server Management Studio and select Options. On the window that opens, again select the Options menu item, and on the right-hand side, change the database recovery mode as appropriate.

And to take a transaction log backup, you simply follow the same steps as a regular backup but select the backup type as “Transactional”.

Transaction Log Backups in SqlBak

SqlBak makes it very easy to take transaction log backups and to include them as part of your backup schedule. You simply select the checkbox to indicate that you want to make transaction log backups and also select their frequency.

Since transaction log backups are relatively ‘cheap’ compared to full or even differential backups, it is ok to schedule them several times a day or even several times an hour. It also helps that, except for very large or very busy databases, transaction log backups are usually small files in the order of hundreds of KB or a few MB.