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.