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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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