SQL Server COPY_ONLY backup – Don’t be confused!

Let’s talk about an option that often confuses DBAs around the world. At least it confused me for almost a decade… COPY_ONLY backups and what you should know about them.

There are numerous sources online for the various backup types that SQL Server offers, how you can use them to schedule your backups plans, what are the restore options that correspond to the selected plan etc. For what there isn’t enough information available (unless you really dig into the issue) is “what the COPY_ONLY backup isn’t suitable for”.

If you read Microsoft’s Books online you will end up believing that a COPY_ONLY backup is as if it didn’t exist! Meaning that you can take it, do whatever you intended to do with it and forget it. Though this is quite true, it doesn’t mean that a COPY_ONLY backup cannot be used in almost any situation that a full database backup is required. And when I say almost, I mean ALMOST. Because the only occasion that a COPY_ONLY backup cannot be used is when you need to restore a Differential backup too! This is the ONLY occasion when the restore of a COPY_ONLY backup will ruin the party.

On the contrary, transaction log backups are not affected by the existence or even the restore operation of a COPY_ONLY full database backup, as long as there is no gap in the LSN chain. Meaning that the last lsn +1 of the last restored database backup (be it COPY_ONLY, FULL or DIFFERENTIAL) should be any number between the first lsn and the last lsn of the log backup you will try to restore next! This is the only requirement for restoring a transaction log backup after a successful restore of (any kind of) a database backup.

To sum up, and answer the question posed in the first paragraph (“What the COPY_ONLY backup isn’t suitable for”) always remember the following: Whenever you try to restore a DIFFERENTIAL database backup remember that it can only be applied right after the restore of its base full backup. So, if you restore a COPY_ONLY full database backup you invalidate any subsequent DIFFERENTIAL backup you intended to use.

Sources
Understanding SQL Server Log Sequence Numbers for Backups
Different Ways to Restore a SQL Server Database

Leave a Reply

Your email address will not be published. Required fields are marked *