Log Shipping Configuration - Things to watch out

Recently I came across a situation where a server was configured to log ship. Everything was setup properly and the primary server had security rights to copy transaction log files to the secondary server.

Once configured, log shipping would work for a day and restore the logs to the secondary database as you would expect, but then it will start failing. Based on the error message, it was clear that log shipping chain was being broken somehow.

The person in charge of the database management had disabled differential backups erroneously thinking they were somehow breaking the log chain.

Remember that full backups and differential backups do not break transaction log chain. The only way you can break the log chain is either by switching the database to simple recovery model (which will truncate the log and break the chain) or if you take a backup outside of the log shipping backup process.

Log shipping consists of the following main operations:

• Backup Transaction Log
• Copy it to the secondary destination server (You may configure so that the backup file is created on the secondary server and this step won't be necessary).
• Restore the Transaction Log to the secondary database (on the secondary database).

Log shipping simply creates a transaction log backup from the last log based on LSN and restore must happen in that order, i.e. previous log must be restored first and so on. Naturally, if an outside process takes a transaction log backup, it will break the log chain and any log restores without this backup will fail. The only way to recover would be to find the missing log backup or start all over.

Indeed that was the case here. Even though there wasn't another transaction log backup process, there was a shrink log file job configured to run nightly and as part of this process, the job was first creating a backup and then shrinking the log file.

Transaction log backups will automatically truncate the log file (although it may not happen right away) but file generally won't shrink, so if you are low on disk space it is good practice to shrink the log file periodically, but you shouldn't be taking the backup, if you already have transaction log backup or log shipping configured elsewhere.

To know more about transaction log shipping and how to configure it, check out this post.

Thank you.

Full vs Differential vs Transaction Log Backup

SQL Server provides three backup options - Full backup, Differential backup and Transaction Logs backup. Today, we will review the three backup strategies and key differences between them.

Full Backup
Full backup is just that, a full backup of your database at a point in time. You can restore the full backup on the same or a different SQL Server.

Differential Backup
Differential backup backs only the changes since the last full backup. The benefit of differential backup is that it is very fast and takes less space since you are only backing up the changes.

Differential backup has a backup chain which starts from the last full backup. All differential backups are from the previous full backup. It is possible to take another full backup without breaking the differential backup chain, i.e. to have it continue from the previous full backup. If you take a full backup with COPY_ONLY option, you will not break the differential backup chain, but without the COPY_ONLY option, the previous differential backup chain will be broken and a new chain will start from the most recent backup.

You can perform a restore at point in time by restoring a full backup and then applying the most recent differential backup.

Transaction Logs
Transaction Logs are the changes since the last transaction log backup. I have seen some confusion about whether transaction log backups are from the last full backup or from the last transaction log backup. If you are taking full database backup for the very first time, you transaction log back up chain will start after the full backup. Any subsequent full or differential backups will not break the log chain and the next transaction log backup will be from the last transaction log backup and not the last full backup.

The transaction log backup only works in Full and Bulk Logged recovery model and the only way to break the log chain is by either switching the recovery model to Simple or if you choose to override existing backup set when creating a full backup media set.

If your database is set to full or bulk logged recovery model, you must take frequent log backups otherwise your log file won't truncate, filling up your hard drive.

It is good to use all three backup schemes in your database environment to ensure you keep the restore media set or files as current as possible, so you can restore to a point in time and minimize data loss.

Thank you.