(The Curious Case of… used to be part of our bi-weekly newsletter but we decided to make it a regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.)
First blog post of the year!
This is an issue that’s come up several times over the last few weeks: restore of a differential backup failing while initializing a log shipping secondary.
In one case, the database is over a terabyte and has a lot of changes being made. The idea was to copy and restore the full backup, perform a differential backup and restore it too, and then have a small amount to catch up on with log backups before starting log shipping for real. The problem was that the differential restore was consistently failing with this error:
Msg 3136, Level 16, State 1, Line 75 This differential backup cannot be restored because the database has not been restored to the correct earlier state. Msg 3013, Level 16, State 1, Line 75 RESTORE DATABASE is terminating abnormally.
After going back and forth a few times in email, it turned out that they’d used the COPY_ONLY option on the full backup they performed to initialize the secondary. They mistakenly thought they had to do that to avoid messing up the log backup chain on their production system, which is incorrect as full backups have no effect on the log backup chain at all.
When you specify COPY_ONLY with a full backup, the full backup process does not reset the differential bitmaps in the data files, and this means it cannot be used as the basis for any restore sequence involving a differential backup. The COPY_ONLY backup gets a different ‘differential base GUID’ which will not match any subsequent differential backups – i.e. it cannot be used as the base for restoring a differential.
If you perform the following sequence of operations:
- Normal full backup
- Copy-only full backup
- Differential backup
And then do a RESTORE HEADERONLY on all three backups, you’ll notice that the BackupSetGUID values for the two full backups are different, and that the DifferentialBaseGUID for the differential backup matches the BackupSetGUID for the first full backup.
Bottom line: if you’re going to use COPY_ONLY for any backup operation, make sure you understand the ramifications of doing so.
PS One of the commenters taught me something – if you restore the first log backup performed after the copy-only full backup, you can then restore the differential and it doesn’t complain!
8 thoughts on “The Curious Case of… the failing differential restore”
Hi,
What about backup Availability group , backup must be in copy_only ?
If you want to use differential backups in an AG environment, you have to take the full backups on the primary replica.
Hi Paul,
first of all: best of luck for 2021.
I once had a problem restoring a differential backup. We use IBM TDP for SQL to backup our databases on physical and virtual machines. When I checked the system views I couldn’t believe what I saw: the VM Snapshots were taking full backups of our databases. Our VMWare Team then had to disable the snapshots for all our virtualized SQL Server disks.
Kind regards
Gerald
Thanks – same to you! Aha – that’s priceless.
Hi Paul – I’ve ran into this many times. I believe if you restore the first transaction log backup taken after the COPY_ONLY full backup, it will then allow you to restore that differential.
Well waddya know, you’re right. You learn something every day and I’ll update the post. Thanks!
Great
Please how does it allow restore . is there any setting copy_only backup maintains
It’s based on the LSNs, which are moved forward by restoring the first log backup taken after the full.