April 12, 2017 at 8:12 am
Hi All,
I have recently migrated some SQL 2008 R2 databases to SQL 2014, one of the features I use quite regularly is the copy-only full backup option so that I don't break the database back chains.
However on my 2014 server the copy only backups (full) are part of my backup chain!!
As an example:
If I take a full backup at 08:00 then hourly transaction backups up to 13:00, then a copy only backup at 13:30 and I then perform a restore to the latest time possible the copy-only backup is the only backup listed. So it doesn't appear to be using the initial full and subsequent transaction log backups.
Has anybody else experienced this issue?
Adam
April 12, 2017 at 8:27 am
Are you saying you restored a copy-only backup and then restored subsequent t-log backups to it? That's normal.
April 12, 2017 at 8:45 am
Hi Beatrix,
No, the what I am saying is:
1, Full backup taken at 8am
2, Hourly transaction backups taken every hour there after up to 1pm.
3, An ad-hoc full copy only backup taken at 13:07 (anytime really)
4, Another hourly transaction log backup taken at 2pm
Then if I wish to perform a restore to the latest possible time I would expect to see a list of backups in the restore window starting with the 8am full and then all the subsequent transaction log backups up to the 2pm backup.
But what I actually see is:
A single full backup (backup type is "Full(Copy-Only)") in the restore window and the last transaction log backup taken at 2pm.
April 12, 2017 at 9:07 am
Such unpredictability is one of the reasons why I avoid the GUI where possible. But maybe there is logic to it. A copy-only full backup doesn't have any effect on the log chain - it's only there to preserve the integrity of the differential chain. Therefore the GUI treats it as it would any other full backup. Could that be what's going on?
John
April 12, 2017 at 9:09 am
That's expected behaviour though. I would only expect the GUI to show me one full backup (copy-only or otherwise) before the point in time to which I wished to restore, and any subsequent transaction logs.
April 12, 2017 at 9:14 am
This is what I mean. Assume I have the following backups:
12.00 FULL
12.30 TLOG
13.00 TLOG
13.20 FULL(COPYONLY)
13.30 TLOG
14.00 TLOG
If I wanted to restore to 13.35, I'd restore the backups as follows:
13.20 FULL(COPYONLY)
13.30 TLOG
14.00 TLOG (with STOPAT)
April 12, 2017 at 9:33 am
Hi Beatrix,
I understand what you are saying, but this is not what is supposed to happen as a copy only backup should not interfere with the backup chain as they are designed for ad-hoc backups.
Say for example we are required to hold a weeks worth of backups for DR purposes (as an example more likely to be longer I admit). Each week a full backup is taken on a Monday, Daily differentials daily and hourly transactions throughout the day,
We are then requested to supply a copy of the database some time during the week to a customer, usually this would be a copy only backup and the backup file sent to the customer (and for arguments sake the file is now off the system and gone). Later in the week we notice an issue or are requested to perform a restore and need to restore to a point in time past the time the copy only backup was taken....., We cant as the copy only backup file has now gone as it has been incorporated into the backup chain.
This worked fine in 2008 R2 as copy only backups did not form any part of the backup chain so the weekly full, daily diffs and hourly transaction backups were the only backups that formed the chain.
I am not saying that the design of copy only backups have changed, but is there a potential bug in 2014?
April 12, 2017 at 1:03 pm
acrutchley - Wednesday, April 12, 2017 9:33 AMHi Beatrix,
I understand what you are saying, but this is not what is supposed to happen as a copy only backup should not interfere with the backup chain as they are designed for ad-hoc backups.Say for example we are required to hold a weeks worth of backups for DR purposes (as an example more likely to be longer I admit). Each week a full backup is taken on a Monday, Daily differentials daily and hourly transactions throughout the day,
We are then requested to supply a copy of the database some time during the week to a customer, usually this would be a copy only backup and the backup file sent to the customer (and for arguments sake the file is now off the system and gone). Later in the week we notice an issue or are requested to perform a restore and need to restore to a point in time past the time the copy only backup was taken....., We cant as the copy only backup file has now gone as it has been incorporated into the backup chain.
This worked fine in 2008 R2 as copy only backups did not form any part of the backup chain so the weekly full, daily diffs and hourly transaction backups were the only backups that formed the chain.I am not saying that the design of copy only backups have changed, but is there a potential bug in 2014?
I just ran through the same sequence Beatrix posted but I just skipped the copy_only backups, ran the previous full backup, restored the logs and all was fine. No issues.
The copy_only backup was not needed.
Did you read John's post? It looks like that is exactly the issue. Beatrix mentions the same.
The change is that the GUI displays the backup sets and 2008 did not. It's just doing a query of the last full backup - whatever it was - and then the subsequent logs.It's not a bug but a change in the GUI and needing to understand what is going to show up in the list.
You can change what is restored by selecting the devices yourself or use t-sql for the restores instead of the GUI.
Sue
April 12, 2017 at 1:32 pm
Hi Sue,
Yes, I understand that's how it works now, apparently since 2012.
I also found that the copy only option doesn't actually interfere with the backup chain post 2008 R2 as I had feared earlier, the LSN chain seems to remain in tact.... which is a massive relief.
Although not a great way of doing it at all I have been able to work around the GUI issues if I connect to the server using SSMS 2008 R2 client. The 2008 R2 client only queries for full backups not all full including the copy only ones so you get a complete list of restore since the first full backup in the chain minus the copy only backups.
I still don't fully understand why the copy only backups appear in the restore chain as according to BOL they have no impact on back chains.... so why list them in the first place over listing the actual chain. I understand that they can be used and would potentially add a performance increase to the speed of a restoration. But it would be nice if it was an option in the GUI to include them or not and just show the full chain instead in the restore process, but that's just me after the world 🙂
The reason why I am pretty insistent on using the GUI over T-SQL is that other members in my team are of mixed skills which are not related to SQL as mine are, and we are expected to cover each others workload when required, so the simpler I can keep it for everyone the better.
Thanks guys for all of you help.
Adam
April 12, 2017 at 2:01 pm
acrutchley - Wednesday, April 12, 2017 9:33 AMI understand what you are saying, but this is not what is supposed to happen as a copy only backup should not interfere with the backup chain as they are designed for ad-hoc backups.
Nope.
A copy-only backup does not reset the differential base. That is all, that's the only difference from a normal full backup. It has no effect on LSNs for log backups, because *no* full backup affects the LSNs for log backup.
Hence, you can restore a copy-only full backup and all logs since then, just as you would a normal full backup.
The only thing you can't do is restore a copy-only full backup, then a differential, then log backup, as the copy-only cannot be the base for a differential backup, the previous full backup would be the base for it.
I am not saying that the design of copy only backups have changed, but is there a potential bug in 2014?
No, and no. There's been no design change, and there's no bug. SSMS is behaving a bit unintuitively, that's all
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 12, 2017 at 2:29 pm
And an old blog post discussing the subject (also read the posts that it links to): http://sqlinthewild.co.za/index.php/2011/03/08/full-backups-the-log-chain-and-the-copy_only-option/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply