Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

Copy-Only Backup
Published: Feb 03, 2014
This is a crucial addition to SQL Server which is invaluable at times. It basically takes a snapshot of the database rather than an actual backup. Therefore it can be used to restore a database and to provide a copy, but crucially it does not break the backup chain.

What is a backup chain? Well, when you have a database in Full Recovery mode you need a constant chain of backups in order to have the ability to restore the database correctly.

For example, we have the following scenario:

Nightly full backup
6 hourly differential backup
Hourly transactional backup

Let’s say that it’s 2:30pm and you need a copy of the database placing on a test server in order to verify some pending changes. Therefore you simply take a backup of the database and restore the backup to the test server. Simple.

Now, at 4:05pm there is a corruption and the only option is to restore your database from backups. So you follow the correct procedure:

Full backup
Midday differential
1pm, 2pm, 3pm, 4pm Transactional backups

However, when you attempt to add the 3pm transactional backup it fails with an error (perhaps like the following):

Basically what has happened is that the backup you took at 2:30pm has broken the chain. The 3pm and 4pm transaction log backups are now for THAT backup and not for the midnight copy.

Therefore if you have subsequently deleted the 2:30pm backup you will have lost all data since the 2pm transaction log backup.

However, if you were to select “copy-only backup” then this problem would be avoided and your backup chain would remain intact and complete.

Ensuring people always use this option when making out of sync backups can be a very safe practice to follow.

Breaking the Chain

The other way to break a backup chain is to change the recovery model of a database. For example, when bulk loading or running something that you don’t want logged (or minimally so), some people may change the recovery model from Full to Simple (or Bulk_Logged). Once this action is complete they will revert back to Full. However this will invalidate the transaction log backups from then on.

The way to prevent this breaking of the chain is to run either a Full or Differential backup after reverting back to Full recovery.

It is a common myth to think that only a Full backup can restore / restart the backup chain, but this is not the case. A Differential backup will also suffice.

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.


© Copyright 2020 SQLTraining Ltd.