Creating a copy only backup to avoid breaking a backup chain

Category : Blogs Published : June 7, 2012 User Rating : 4 Stars      Views : 3 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.
I was recently asked to do an extra "adhoc" backup of a live database. This was so that the backup file could be periodically restored into another database which was used for testing. The intention was to keep the backup file so that the testing database could be restored to be a “clean” copy of the live data after each testing session.

This might seem like a simple enough task, but there is a problem with doing a one off backup like this. This is because the extra backup potentially breaks the back up chain, and could cause problems if a database restore becomes necessary before the next full backup.

In our situation the live database has a full backup at a weekend with nightly differential backups taken during the week. Restoring the backup then involves restoring the last full backup (the “base” backup), followed by the latest differential backup (differential backups are cumulative). The trouble with my situation above is that if a database restore is required after my adhoc backup, but before the next full backup, then it is my adhoc backup that has become the base backup and needs to be restored before any differential backup is restored.

This might not seem to be a big deal for smaller setups, but in larger organisations where the person doing the restore is not aware of my adhoc backup it can cause a restore failure.

Fortunately there is an easy way around this, as I can specify my backup as being ‘copy only’. This means that the backup is treated as a one off and will not interrupt any backup strategy already in place.

This facility was introduced in SQL Server 2005 but could only be specified by command line switches in TSQL. With SQL Server 2008 this can now be specified in the GUI.

Important : You might have noticed that I've not mentioned transaction log backups or log chains at all. This is because full backups do *not* break the transaction log backup chain whether they are 'copy only' backups or not. It's a common misconception that a full backup will break the transaction log chain, but this is not the case. A full backup does not truncate the transaction log and only contains sufficient transaction log information to restore a consistent database.

To complicate things a little more it is also possible to specify a 'copy only' transaction log backup, which does not truncate the transaction log, or break the log chain.

Now onto the practicalities. To specify a copy only full backup using TSQL the syntax is:

BACKUP DATABASE MyDatabase TO MyBackupDevice WITH COPY_ONLY
To specify a copy only backup within the GUI version in SQL Server 2008, just check the 'Copy Only Backup' checkbox in the Backup window :
Copy Only Backup GUI


Related Articles

The following articles may also be of interest :

Link back to this article : https://www.sqlmatters.com/Articles/Creating a copy only backup to avoid breaking a backup chain.aspx

Keywords

Blog,copy-only,copy only,backup,transaction,log


Comments
Post by Danny on Mon 21 Jan 2013 15:31. Report Inappropriate Post

You mentioned the following:
"It's a common misconception that a full backup will break the transaction log chain, but this is not the case"

Do you have a examples when the transaction log chain will break?
Please advise? Thanks
Post by SQLMatters on Mon 21 Jan 2013 20:22. Report Inappropriate Post

As I mentioned in the article a full backup will *not* break the transaction log chain, which is important if you want to be able to do point in time restores.
You can choose to break the log chain by switching to simple recovery and back to full recovery. This is not advisable for production databases, but for a test database where you're probably not worried about point in time restores it can be a useful technique to free up transaction log space, especially if you are very short of disk space.
Post by Roni on Wed 23 Jan 2013 13:48. Report Inappropriate Post
Website : http://www.ildba.co.il
Thanks, it was a useful post,
I used it in my organization, when we've started to use Third party SQL Backups on top of the regular native SQL backups and I was looking to understand the behavior of the backup chain in case of multiple backups.
Post by rajesh on Tue 12 Feb 2013 11:29. Report Inappropriate Post

Then when you restore full backup will full backup break differential backup chain?
Post by pavan on Wed 11 Sep 2013 05:26. Report Inappropriate Post

superbbbbbbb

Post a comment   No login required !

Name : Email : Website :
Will be displayed alongside your comment
 Not displayed Optional, but displayed if entered