November 25, 2014 at 9:07 am
On one of our SQL Server 2014 boxes each database has a copy-only full backup made every night, in addition to the maintenance plan schedule of a full backup weekly, daily differential backups and log backups.
When performing a PIT restore in SSMS the restore file list lists the most recent copy-only backup as the full backup to use, not the most recent plan full backup. I noticed that using SSMS 2008 to start a PIT restore on the 2014 box does not have this problem, and lists the correct restore file sequence (ignores the copy-only backups).
A quick google search didn't turn up anything on this apparent bug in SSMS 2014, so I was wondering if anyone else had noticed this and had any suggestions for a workaround, or if there was a fix available for this.
Thanks
Tim
November 27, 2014 at 3:42 am
Why would you think this is a bug?
If anything, the restore sequences shown in SSMS 2008 is wrong, but then again connecting with SSMS 2008 to a SQL 2014 instance you may see more strange things.
A copy_only Full backup can be used as a starting point for a PIT. It cannot be used as base for a differential backup.
So in case you're using differential backups you need to start your restore sequence with the Full Backup (NOT copy_only), then the last diff and any tlog backups necessary.
But if you're only use FULL and LOG backups you can use a copy-only backup as a starting point.
[font="Verdana"]Markus Bohse[/font]
November 27, 2014 at 6:08 am
But that's the thing - we are using differential backups.
Tim
November 27, 2014 at 7:38 am
Sorry missed that in my first reply.
But after some more testing I think it must be related to your SSMS version. I cannot reproduce the issue with SSMS 2014. Also you mention that SSMS 2008 shows the correct sequence as well.
With which version of SSMS and which SP/CU do you get this issue?
[font="Verdana"]Markus Bohse[/font]
November 27, 2014 at 7:44 am
We are using SSMS 2014 to access a 2014 server. That's the scenario where the problem exists. I only included the info on SSMS 2008 to illustrate that the restore sequence displays correctly in SSMS 2008 but not in SSMS 2014 (on the same 2014 box). SSMS 2012 also has the same behavior as SSMS 2014. I doubt if it is a bug, otherwise surely others would have noticed. But there must be some reason this is occurring.
BTW the restore works fine on the 2014 box using SSMS 2008.
Thanks for your time on this, BTW.
Tim Harding
Tim
November 27, 2014 at 1:08 pm
slimchance99 (11/25/2014)
On one of our SQL Server 2014 boxes each database has a copy-only full backup made every night, in addition to the maintenance plan schedule of a full backup weekly, daily differential backups and log backups.
That would be the problem. You're taking the daily full backups anyway. Why not make them useful by making them NOT copy only and stop doing the daily DIFs altogether? It's not like those daily DIFs are actually saving you any disk space or making PIT restores any easier. In fact, they're just making things more complicated than necessary.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 27, 2014 at 1:18 pm
The reason is that the daily copy-only backups are performed at the data centre onto their backup media as part of the enterprise DR plan. If we have to perform a PIT restore using those it is a major hassle, as we have to contact the data centre and have them restore the files from their media for us to access. For local use we prefer to keep our own separate backup plans so we do not have to go through that aggravation, delay and expense.
Tim
November 27, 2014 at 1:37 pm
slimchance99 (11/27/2014)
The reason is that the daily copy-only backups are performed at the data centre onto their backup media as part of the enterprise DR plan. If we have to perform a PIT restore using those it is a major hassle, as we have to contact the data centre and have them restore the files from their media for us to access. For local use we prefer to keep our own separate backup plans so we do not have to go through that aggravation, delay and expense.
OK. NP. What is the data center using to do the backups with? SQL Server or something else?
Also, how big is the weekly FULL backup and how big are the DIF backups throughout the week
--Jeff Moden
Change is inevitable... Change for the better is not.
November 28, 2014 at 9:31 am
I'm not sure what backup product is being used at the data centre - the daily full backups are not SQL server native backups though.
The weekly full backup is ~185 GB, and the daily DIFs are ~2 GB.
It's still not clear to me why the copy-only backups appear in the restore file sequence.
Thanks
Tim Harding
Tim
December 1, 2014 at 6:50 am
Would it not be easier to perform multiplexed backups to multiple Locations? We back our databases up locally on the box and multiplex it to our SAN just in case the Server falls over. Would that help you a Little more?
December 1, 2014 at 2:29 pm
That's similar to what we are doing. The local backups we make are copied out to a NAS device daily. However that doesn't help with the issue of the copy-only backups in the restore sequence. We have no control over what happens at the date centre as far as their DR plan goes. The 2005 and 2008 servers that we have in production do not have this issue, and the copy-only backups do not show up in the PIT restore sequence. This only occurs with the 2014 boxes (we don't have any 2012 boxes in production).
The copy-only backups do not appear in the PIT restore sequence in SSMS versions prior to 2012.
Tim Harding
Tim
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply