Will a COPY_ONLY backup initialise the log chain?

While setting up some databases into an Availability Group, a colleague asked me why, after switching to the FULL recovery model, he was getting the message “A full backup is required”, given that he was planning on using the “Full” data synchronization, which states:

Starts data synchronization by performing full database and log backups for each selected database. These databases are restored to each secondary and joined to the availability group.

The obvious question there is then “Why do we need to perform a full database backup if the Availability Groups wizard will be taking one for me?”

Looking at the script generated by the Availability Groups wizard, we can see the following steps:

:Connect SQL01

BACKUP DATABASE [AW2012] TO  DISK = N'\\SQL01\DataSynch\AW2012.bak' WITH  COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5
GO

:Connect SQL02

RESTORE DATABASE [AW2012] FROM  DISK = N'\\SQL01\DataSynch\AW2012.bak' WITH  NORECOVERY,  NOUNLOAD,  STATS = 5
GO

:Connect SQL01

BACKUP LOG [AW2012] TO  DISK = N'\\SQL01\DataSynch\AW2012_20130213234500.trn' WITH NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5
GO

:Connect SQL02

RESTORE LOG [AW2012] FROM  DISK = N'\\SQL01\DataSynch\AW2012_20130213234500.trn' WITH  NORECOVERY,  NOUNLOAD,  STATS = 5
GO

Clearly, the full backup is a COPY_ONLY backup. From Books Online:

A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. Usually, taking a backup changes the database and affects how later backups are restored. However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. Copy-only backups serve this purpose.

…

Copy-only full backups (all recovery models)

A copy-only backup cannot serve as a differential base or differential backup and does not affect the differential base.

Restoring a copy-only full backup is the same as restoring any other full backup.

What Books Online doesn’t mention is whether a COPY_ONLY backup can be used as a base for the first full backup taken after switching the recovery model from SIMPLE to FULL. Whenever I find ambiguous information, I like to run a test:

-- Switch the database into the SIMPLE recovery model
ALTER DATABASE TestDB SET RECOVERY SIMPLE

-- Switch the database into the FULL recovery model
ALTER DATABASE TestDB SET RECOVERY FULL

BACKUP LOG TestDB TO DISK = 'D:\SQLBackup\TestDB_LOG_1.trn' WITH COPY_ONLY
-- Failed! Msg 4214, BACKUP LOG cannot be performed because there is no current database backup.

BACKUP DATABASE TestDB TO DISK = 'D:\SQLBackup\TestDB_COPY_ONLY.bak' WITH COPY_ONLY
-- Successfully took a COPY_ONLY backup

BACKUP LOG TestDB TO DISK = 'D:\SQLBackup\TestDB_LOG_2.trn' 
-- Failed! Msg 4214, BACKUP LOG cannot be performed because there is no current database backup.

BACKUP DATABASE TestDB TO DISK = 'D:\SQLBackup\TestDB_FULL.bak'
-- Success – full backup complete

BACKUP LOG TestDB TO DISK = 'D:\SQLBackup\TestDB_LOG_3.trn'
-- Success - the log chain is initialised

We have the proof – COPY_ONLY backups will not initialise the log chain.

The bottom line is that if you skip using the wizard and use a T-SQL script to initialise the databases, you can change these COPY_ONLY backups to normal FULL backups. However, you probably should be ensuring your database is already in the FULL recovery model before you start your HA adventures. From an engineering perspective, it’s a lot easier to write a wizard that assumes you’ve already got the database in the correct state than to try and fix the state as part of the wizard.