July 29, 2013 at 9:11 am
Grant Fritchey (7/23/2013)
You can do a COPY_ONLY backup on the secondaries. But, you cannot use a COPY_ONLY backup as part of a point in time recovery. The entire concept behind the COPY_ONLY backup is to avoid dealing with the transaction log and differentials.
That's news to me. You can definitely do a restore of a COPY_ONLY backup with NORECOVERY and restore subsequent log backups...
Differential restores are obviously not possible. The resetting of the differential base is the entire concept of COPY_ONLY backups, it doesn't change any behaviour relating to transaction logs.
July 29, 2013 at 9:14 am
HowardW (7/29/2013)
Grant Fritchey (7/23/2013)
You can do a COPY_ONLY backup on the secondaries. But, you cannot use a COPY_ONLY backup as part of a point in time recovery. The entire concept behind the COPY_ONLY backup is to avoid dealing with the transaction log and differentials.That's news to me. You can definitely do a restore of a COPY_ONLY backup with NORECOVERY and restore subsequent log backups...
Differential restores are obviously not possible. The resetting of the differential base is the entire concept of COPY_ONLY backups, it doesn't change any behaviour relating to transaction logs.
My keyboard frequently works faster than my brain. Differentials is the issue, not logs. Sorry, sorry, sorry.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 3, 2014 at 8:25 am
You can't use copy_only backup in point in time recovery.
June 3, 2014 at 9:52 am
Grant Fritchey (7/23/2013)
Backups from the secondary are somewhat redundant, and as you're discovering, not really possible. You can only run a FULL, proper, backup from the current primary. You can run log backups from any or all secondaries, but, understand that you'll need those logs, from all the secondaries, in order to do a point in time recovery. So, be very, very cautious about setting up secondary log backups. They will affect your primary server. I wrote a chapter in the book "Pro SQL Server 2012 Practices" on just doing backups and restores with Availability Groups.You can do a COPY_ONLY backup on the secondaries. But, you cannot use a COPY_ONLY backup as part of a point in time recovery. The entire concept behind the COPY_ONLY backup is to avoid dealing with the transaction log and differentials.
In a nutshell:
You have to enable backups on the secondary
set priority on the secondaries
schedule jobs on all secondaries
but make sure those jobs use T-SQL to do the backups and check for fn_hadr_backup_is_preferred_replica in order to determine if they are the correct backup server currently.
In short, it's pretty complicated.
Recommendation. Do all backups on the primary unless you identify a need for additional backups (high transaction volume or log backups causing blocking would be a good reason to split off the transaction log backups). Secondaries I wouldn't backup. They're just copies of the primary and should be synchronized (at some point if you're doing async).
THANK YOU GRANT! you just saved my buttocks! I have been looking for this type of answer for months. :w00t:
MCSE SQL Server 2012\2014\2016
June 3, 2014 at 10:28 am
Faisal Malik (6/3/2014)
You can't use copy_only backup in point in time recovery.
The only thing you can't do with a copy-only is restore differentials onto it. You can restore log backups onto it for point-in-time recovery.
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 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply