Log Shipping and Database Backups

Will log shipping break if you still implement your daily, weekly, monthly, or other full or differential backup scheme? No. Log shipping can coexist peacefully with any full or differential backup on a database. However, if you are performing a full or differential backup, you cannot do a scheduled transaction log backup during the backup process; it will occur at the next possible scheduled time after the full or differential backup is complete. This means that the transaction log might grow while the full backup takes place (depending on your database usage). On a small database, this should be fairly insignificant, but on a very large database this is not trivial. It means that the log will take longer to copy and apply. It also means that the secondary server will be further out of sync than it is while log shipping is not happening. You need to take this into account in your planning.

The only way a full or differential backup will affect log shipping is if, in some way, you truncate the transaction log prior to doing an actual log backup. A good example is if your transaction log is growing quickly and you want to reclaim disk space by using a BACKUP LOG databasename NO_LOG or switch to Simple recovery.

On the other hand, if you have an existing transaction log backup plan for a database, log shipping will be affected by that plan. Although Microsoft cannot stop you from setting up multiple transaction log backup jobs for one database (you can only do one per database with the Database Maintenance Plan Wizard; when you code your own using Transact -SQL syntax, however, you can technically do an infinite amount), you need to take into account each transaction log and how it will be applied to a secondary server. For example, your senior DBA already has a custom SQL Server Agent Job that backs up the transaction log for the database named MyDatabase every 15 minutes. Another DBA then decides to configure log shipping using the Database Maintenance Plan Wizard with a transaction log backup frequency of 10 minutes.

Problem 1 Log shipping knows nothing about the existing transaction log backup plan.
Problem 2 You now have two conflicting transaction log backups happening. Assuming log shipping is configured correctly, you will more than likely see the aforementioned 4,305 errors almost immediately.
Problem 3 Not only does log shipping not know about the existing job, but you have two different schedules. What is the actual required frequency for the database in question? You need to decide on one and then modify the Database Maintenance Plan with the right interval, and you need to delete or disable the custom job.

As you can see by this example, which assumes using the built-in functionality of SQL Server 2000, you can easily create a problem without realizing it right away. The same would be true for any custom solution. Before you configure log shipping, make sure there will be no conflicts in terms of existing backup plans.

If you have some intrusive maintenance that prevents the creation of a transaction log backup, you will not get a transaction log backup until these tasks are completed. These tasks more often than not require exclusive access to the database or something similar. A good example of maintenance that will prevent a transaction log backup is the aforementioned full backup as well as a database restore. These maintenance tasks and how to deal with more intrusive maintenance are discussed in more detail in Chapter 14, Administrative Tasks to Increase Availability.

Note

In terms of the transaction log backup itself, all committed and uncommitted work is sent as part of the transaction log. If you have a long-running transaction that fails midway through its execution and you need to bring the secondary server online, it needs to go through the rollback process of the transaction in the role change process. This affects your recovery time.

More Info

For more information on backups, see Chapters 9, Database Environment for Discovery, and 10. To mitigate the creation of false errors by intrusive maintenance, see the section Intrusive Maintenance in Chapter 14.