Backup strategy: Copy-only FULL backups vs NON-copy-only FULL backups

Question

Hi all, as per title,

My maintenance plan consists of FULL backups every 12h and Transaction Log backups every 1h - No differential backups are used

What are the benefits of using/not using the "Copy-only backup" option in my maintenance plan?

Thanks for considering my question
John

Answer 1

Good day Doody

The documentation explain it well starting from the sentence:
https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/copy-only-backups-sql-server?view=sql-server-ver15

A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups.

In other words, when you use a simple backup then your backup is register under the backup chain. This is mostly important when you use transaction Log backups and differential backups since these are part of recovery chain.

In your case for example, you have FULL backups every 12h, but what if you want to have a full backup between these point in time? For example for development propose you might want to have a simple full backup for simple and fast restore. In this case, you do not want to break the chain of the backup in your maintenance plan, so what you do is a Copy-Only Backup which does not break the chain and stand by itself

Answer 2

COPY_ONLY main matters when you are using differential backups.

Assume this:

1. You take a full backup at without COPY_ONLY
2. You take a differential backup.
3. You take a full backup with COPY_ONLY.
4. You take a new differential backup.

If you want to restore the database at the point of the second differential backup, you need to restore backups 1 & 4. Because the second full backup was COPY_ONLY it did not clear the diff map, and there is no relation between it and the differential backup.

Now, you don't have differential backups, but I would say that it's wrong to have COPY_ONLY in the maintenance plan. COPY_ONLY is when you take an ad-hoc backup, for instance to restore to a different environment.

You can only use COPY_ONLY with transaction log backups. Again, this is for ad hoc reasons. COPY_ONLY prevents the log from being truncated, so the scheduled log backups will not have a gap.

Answer 3

Hi,

I do have FULL backups every 12h but these backups are marked as copy-only

This seems like a mistake in first glance. Those copy-only backup that you do are not part of the backup chain.

I guess a better question would be if its more beneficial to have the full backup as copy-only backup or not

I cannot know what is "more beneficial" for you, since I am not familiar with your specific system and requirements.

If you are only using copy-only full backup when restoring and if you do not use actions like: Log shipping, Always On or Database mirroring, Media recovery without data loss, Point-in-time restores, then you probably should use Simple Recovery Model.

In Simple Recovery Model there is no log backup (which is why it does not support the actions I mentioned above) and the server automatically reclaims log space. This usually fit small databases which are ok with losing changes between the backups. In this case there is no meaning for the backup chain.

Using Full Recovery Model you should manage the backup chain and you must manage the log backup to keep it small. In this case you can recover to arbitrary point in time.

have the full backup as copy-only backup or not

Usually this is not common (even so it can be used with transaction log backup). You can use full backup without copy-only backup as part of the chain.

In any case always test recovery of your database from time to time and make sure that you know how to use the backup files you have and that these were created well (DO NOT COUNT ON THE VERIFY BACKUP INTEGRITY ONLY, BUT TEST FULL RECOVERY FROM TIME TO TIME).

Answer 4

Hi DoodyHeady-8339,

Welcome to Microsoft Q&A.

I guess a better question would be if its more beneficial to have the full backup as copy-only backup or not.

In addition, using copy-only the backup is generally to prevent the backup that will be done from destroying the existing backup strategy. For example, for a database (such as Log shipping) that has established backup rules, you now need to make a log backup to another folder. Ordinary log backup will destroy the log chain maintained by the existing backup file system. Copy-only backup will not damage it. So this method is generally only used purposefully in occasional situations.

Best Regards,
Amelia