I’m asked this question quite often while setting up availability groups and setting a preferred secondary for transaction log backups – can you recover from the full backup taken on the secondary with COPY_ONLY and also rely on a full backup taken on the primary or does the log chain become broken and prevent it?

To answer the question directly, yes, you can recovery form the COPY_ONLY full as well as the full taken on the primary and still apply logs to both. Instead of simply answering the question, let’s show it on our lab setup with availability groups running the following configuration.

As shown above, the NODE1 and NODE2 are acting in an automatic failover, synchronous state. NODE3 is acting as a read-intent routing replica and NODE4 is acting as a disaster and recovery replica.

Below, we can see the backup preferences are set to exclude all replicas except NODE2.

In this setup, COPY_ONLY full backups and transaction log backups are preferable on NODE2. We would run these jobs by calling the sys.fn_hadr_backup_is_preferred_replica and checking if the job is executed on a preferred replica at that time. Although this is a great way to offload transaction log backups, we’ll still look to the primary for a full (without COPY_ONLY) and then differentials, if suited for the backup and recovery strategy.

Testing Recovery

To test the situation of recovering from utilizing both the full backup from a primary and then the COPY_ONLY full from a secondary, while retaining the ability to utilize the transaction log backups taken from the secondary replica, we can run the following test.

On NODE2, as a secondary preferred backup replica, we have a full COPY_ONLY job

IF sys.fn_hadr_backup_is_preferred_replica('AdventureWorks2008') = 1
BEGIN
  DECLARE @CMD NVARCHAR(1500) 
  SET @CMD = 'BACKUP DATABASE [AdventureWorks2008] TO DISK=''C:BackupsFull' + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 112), '/', '') + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 108) , ':', '')  + '.bak'' WITH COPY_ONLY'
  Exec(@CMD)
END

On NODE2, we also have a log backup, which is the primary offloading reasoning for the preferred backup replicas.

IF sys.fn_hadr_backup_is_preferred_replica('AdventureWorks2008') = 1
BEGIN
  DECLARE @CMD NVARCHAR(1500) 
  SET @CMD = 'BACKUP LOG [AdventureWorks2008] TO DISK=''C:BackupsLOG' + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 112), '/', '') + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 108) , ':', '')  + '.trn'''
  Exec(@CMD)
END

Prior to running the COPY_ONLY, we want to execute, on the primary replica, a full backup.

BACKUP DATABASE [AdventureWorks2008] TO DISK = N'C:BackupsAW2008_FULL_PRIMARY.bak' WITH NOFORMAT, INIT,  NAME = N'AdventureWorks2008-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Now, execute the COPY_ONLY full code and two logs backups on the secondary replica that is the preferred backup replica. (code shown earlier)

At this point, we can recover from the full taken from the primary or the COPY_ONLY full taken on the preferable secondary replica and apply the logs backups for to each for recovering to a point in time of the last log backup taken.

COPY_ONLY restore test to a database, AdventureWorks2008Recovery

USE [master]
RESTORE DATABASE [AdventureWorks2008Recovery] FROM  DISK = N'C:backupsFull20131210181523.bak' WITH  FILE = 1,  MOVE N'AdventureWorks2008_Data' TO N'C:Program FilesMicrosoft SQL ServerMSSQL11.SHAREPOINT2013MSSQLDATAAdventureWorks2008Recovery_Data.mdf',  MOVE N'AdventureWorks2008_Log' TO N'C:Program FilesMicrosoft SQL ServerMSSQL11.SHAREPOINT2013MSSQLDATAAdventureWorks2008Recovery_Log.ldf',  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5
GO
RESTORE LOG [AdventureWorks2008Recovery] FROM  DISK = N'C:backupsLOG20131210181530.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO
RESTORE LOG [AdventureWorks2008Recovery] FROM  DISK = N'C:backupsLOG20131210181537.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO

Now let’s try the full taken from the primary and the log backups from the secondary to a database, AdventureWorks2008RecoveryFull

USE [master]
RESTORE DATABASE [AdventureWorks2008RecoveryFull] FROM  DISK = N'\node1c$BackupsAW2008_FULL_PRIMARY.bak' WITH  FILE = 1,  MOVE N'AdventureWorks2008_Data' TO N'C:Program FilesMicrosoft SQL ServerMSSQL11.SHAREPOINT2013MSSQLDATAAdventureWorks2008RecoveryFull_Data.mdf',  MOVE N'AdventureWorks2008_Log' TO N'C:Program FilesMicrosoft SQL ServerMSSQL11.SHAREPOINT2013MSSQLDATAAdventureWorks2008RecoveryFull_Log.ldf',  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5
GO
RESTORE LOG [AdventureWorks2008RecoveryFull] FROM  DISK = N'C:backupsLOG20131210181530.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO
RESTORE LOG [AdventureWorks2008RecoveryFull] FROM  DISK = N'C:backupsLOG20131210181537.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO

As shown above in the screen shot, after both restorations are complete, we’ve successfully restored from both the full and the COPY_ONLY full while applying the log backups to both that were taken from the secondary. Essentially, we’ve utilized the COPY_ONLY to recovery to a point in time while still retaining the ability to use the full taken earlier.

Although there is much more to the backup chain with COPY_ONLY and adding differentials, scenarios of recovery points and so on, this has shown the basic answer to questioning a COPY_ONLY on a replica allowing for recovery as well as retaining the log chain for a recovery from the full.