Knowledge Base

Backing up log shipping standby databases

Category: How do I?
Date: 28 May 2009
Product: SQL Backup
If you are log shipping a database from one server to another, it is still possible to also perform backups on the log shipping destination database, although there are a few restrictions on this activity.

Log Shipping is a replication technology where log backups are restored to a secondary server.

Generally, when log backups occur, the checkpoints are reset, so only full and differential backups can be performed on the log shipping source database, because these backup types do not affect the checkpoint. Normally, all log backups from the source database MUST be restored to the secondary server, so log backups cannot be part of the backup strategy outside of the log shipping.

SQL Server 2005 and newer can perform log backups without changing the database checkpoint using the COPY_ONLY log backup option, which will not truncate the database log and therefore will not affect the log shipping chain. Please see SQL Server 2005 books online for the usage of BACKUP LOG WITH COPY_ONLY.