The COPY_ONLY option for the backup command never ceases to cause confusion. What does it really do? And in what way does it affect your restore sequence? Or not?
There are two sides to this. Restoreability and how the GUI behaves:
Restoreability
If you specify COPY_ONLY for a full backup, it will not affect the following differential backups. I.e., the following differential backups will be based on the last full backup which was not performed with COPY_ONLY. Another way of looking at this is that a full backup using COPY_ONLY will not reset the DIFF (also known as DCM) page, which is page 6 and repeated approximately every 4 GB in each database file.
If you specify COPY_ONLY for a log backup, it will not affect the following log backups. I.e., the log backup chain will be without the one(s) produced using COPY_ONLY. Another way of looking at this is that a log backup using COPY_ONLY does not empty (truncate) the log.
That’s it! COPY_ONLY for a full backup does not, in any way, affect the restoreability for log backups. Say you perform
FULL_A
LOG_A
LOG_B
FULL_B
LOG_C
You can restore FULL_A, LOG_A, LOG_B and LOG_C, regardless of whether you used COPY_ONLY for FULL_B or not! I see misunderstanding about this all the time.
The restore GUI
And then we have the restore GUI. I really want to like the GUI, but it… keep disappointing me. For various reasons. To the extent that it frequently becomes useless. I’ve blogged about it before. I know that many of you readers don’t use the GUI when you perform restore. But there are so many “accident” DBAs and less experienced DBA’s which expect to be able to use the restore GUI – and they should! But they can’t. Enough ranting, time for some details.
Prior to SQL Server 2012, the restore GUI didn’t include COPY_ONLY backups. The design reasoning was probably that you did an out-of-band backup and that backup file is no longer there (you deleted it after restoring to the test server, for instance). Sound reasoning, in my opinion.
But, as of 2012, the restore GUI includes COPY_ONLY backups. So, say you do:
FULL_A
LOG_A
LOG_B
FULL_B using COPY_ONLY
LOG_C
Also, say that the backup file for FULL_B was deleted. You use the GUI and specify that you want to the restore up until LOG_C. The restore GUI will now select FULL_B and LOG_C, which is a disaster since FULL_B was deleted (it was produced using COPY_ONLY). And you can’t make the restore GUI to base the restores on FULL_A. If you don’t have the practiced to type your restore commands, your organization (boss?) will not be happy with you now.
So, why did Microsoft do this change in 2012? My guess is it because of availability groups. If you perform a backup on a read-only replica, you need to use COPY_ONLY. Btw, we have the same situation if you did a VSS snapshot of the machine (which I blogged about before).
Bottom line? You know this already. Learn how things work, and practice your restore scenarios. Nothing new here.
(I typed everything above off the top of my head. If there is anything you don’t agree with, then please be very specific. I don’t mind discussions, I appreciate it. But to be fruitful, it need to be concrete. So, be specific, ideally including TSQL commands, and specifics on how you drive the GUI (if relevant). And, remember that things can change over time; service packs and new versions of SSMS – so please include those details as well. 🙂 )