Have you ever wondered what a copy only backup is in SQL Server? In this blog post, we will explore this concept and understand how it can be useful in certain scenarios.

A copy only backup is an independent backup that does not break the chain of database backup Log Sequence Numbers (LSN). In simpler terms, it does not disrupt the original backup workflow, but rather creates a copy of the original database independently.

Let’s consider a scenario where you have a regular backup workflow in your organization. You take a full database backup at midnight and a differential backup every hour. Now, if you want to restore your database to a specific point in time, let’s say 2 PM in the afternoon, you would first need to restore the last full backup taken at midnight and then restore the latest differential backup.

However, if for some reason you take another full backup at 10 AM in the morning, the differential backup at 2 PM can only be restored after you have restored the 10 AM full database backup, not the one taken at midnight. This can become complicated if you have a backup workflow in place with specific policies on how backups should be set up.

To illustrate this scenario, let’s take a look at the following image:

As you can see, taking an additional backup in the middle of your backup workflow can disrupt the automatic process if it is dependent on sequence.

Now, what if you just want to take a backup without disturbing the backup workflow? This is where the copy only backup option comes in handy. By using the copy_only backup option, you can take a stand-alone full backup that does not disrupt the backup workflow.

Here is an example of the T-SQL script for a copy only backup:

BACKUP DATABASE [DatabaseName] TO DISK = N'C:\backup.bak' WITH COPY_ONLY

By including the COPY_ONLY option, you ensure that the backup is independent and does not affect the regular backup sequence.

Copy only backups can be useful in various scenarios, such as when you need to take a one-time backup for a specific purpose without interfering with the regular backup process. It provides flexibility and allows you to create backups without disrupting the established backup workflow.

What are your thoughts on copy only backups? Have you encountered any situations where they were particularly useful? Let us know in the comments below!