You have some replies already, I'd just like to summarize, perhaps clarify a bit and also expand a bit on what has been posted already:
COPY_ONLY (I'll call it CO from now on) can affect restorability in two scenarios:
One is when you take a full backup. A subsequent doff backup is based on the most recent (non-CO) full backup. So if you want to take a full backup without affect restorability of diff backup, then do the full backup as a CO backup.
The other is a log backup. When you restore log backups you need an unbroken chain of log backups. You can take an out-of-bands log backup which won't be needed for a subsequent restore process by doing that log backup as a CO log backup. Basically, it won't empty the log.
A full backup does not affect restorability of log backup. CO is not needed here.
Another aspect, though, is if you want to rely on SSMS and its backup history to do restore. In some cases, CO backups are not in the proposed restore sequenct. But in other cases, a restore sequence is based on a CO backup and if that backup doesn't exists on disk, then you won't be able to do the restore from SSMS based on the backup history. It is a bit of a mess and I've reported this to MS several time, but I have no hopes for this being fixed.
Full backup wont break the backup chain.
Full backup does not breaks log backup sequence, but it will break Diff backup sequence. So if you take full backup and you also have diff backup and you do not keep track of manual full backup you have taken, the restore will not work. That is why I pointed to use copy_only
Just in case it was not clear from the other replies: if you (or your DBA) relies on differential backups, you certainly reason to worry.
And the short summary: every time you take an out-of-band backup of any sort, always use COPY_ONLY, just out of principle.
Sign in to comment