Question: What is Copy Only Backup in SQL Server?
Answer: This is another interesting question I received the other day. The question was in the image form and I am re-producing the image again here.
The answer of this question can be very long. However, to simplify the answer here is what I would have replied if I ever get asked – “Copy Only backup is independent backup and it does not break the chain of database backup LSN (Log Sequence Number). In another word, it does not disturb the original backup workflow, but just create a copy of the original database independently.”
Well, that was a short answer here is a bit more detailed answer. However, if you are not sure what is differential backup, I strongly suggest that you read this blog post first before you continue reading this blog post. Must Read:Â Backup Timeline and Understanding of Database Restore Process in Full Recovery Model.
When you want to restore your latest differential backup, you must first restore the last full backup before restoring this full backup. Let us assume that everyday you are taking full database backup at midnight and differential backup every hour. Â Now when you want to restore your database to 2 PM in the afternoon, you will first have to restore your full database backup of midnight and restore the latest differential backup.
However, due to any reason, if you take another full backup at 10 AM in the morning, the differential backup at 2 PM, can be only restored after you have restored the 10 AM full database backup and not the one which you have taken at midnight. This can be very complicated if you have a backup workflow in your organization and certain sets of the policy how your backup should be set up.
To illustrate this scenario I have created following image. Where you can see how the regular backup in middle of your backup workflow works. If you have placed automated workflow of the backup sequence, taking an additional backup in the middle can just create disruption in the automatic process if it is dependent on sequence.
Now let us think of the scenario that you just want to take a backup but does not want to disturb the backup workflow. In that case you can use the copy_only backup option and that will not disturb the backup workflow. The optional copy_only will be a stand alone full backup.
Here is the T-SQL script for copy_only backup.
BACKUP DATABASE [DatabaseName] TO DISK = N':c\backup.bak' WITH COPY_ONLY
Let me know what you think of this blog post. I will be happy to know your opinion and if you believe I should add something more in the blog post, please leave a comment.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
7 Comments. Leave new
Hi Pinal,
Does your explanation also hold if my backup starategy only has full backups at night and hourly log backups instead of differential backups?
1. If your backup policy doesn’t have differential backups in the schedule, you are not required to specify “copy-only” while taking an adhoc full backup. A full backup (with or without copy-only) never breaks the LSN chain of the log backups. It only resets the differential base.
2. Obviously, if you want an adhoc log backup, you will have to specify copy-only in the log backup command. Otherwise, you’ll break the log chain.
Hope this helps.
You have cleared me on log backup.lthank you
Hi Pinal,
This is a very good illustration! Thank you!!
To tell the truth, I never even looked at that before. Has that been in SQL Server for a long time?
Very good explanation.. Thank you so much….
This type of backup is useful when you want to natively backup and restore a primary DB on a dev environment or as a secondary replica without interfering with the backup chain managed by a 3rd party solution such as NetApp SnapCenter.