SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Always on Secondry database backup plan & Recovering.


Always on Secondry database backup plan & Recovering.

Author
Message
bartedgerton
bartedgerton
SSC-Enthusiastic
SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)

Group: General Forum Members
Points: 131 Visits: 257
A copy-only backup is not part of a restore set that involves transaction log backups. The purpose of the copy-only backup is to have a backup set outside the regular backup chain without breaking the chain.

A regular full-backup MUST be done on the primary database, and it can't be a copy-only backup. Same goes for differential backups. Only transaction log backups can be done on the secondary, and the caveat is that it will not update the "Last Database Log Backup" time on the primary, however, it will keep a consistent log chain, regardless of where the log backups are taken in the availability group.

I've configured a daily full-backup on both servers that does a check to ensure that the current node is primary before executing the full backup. I've also configured hourly log-backups on both, but have them set to check sys.fn_hadr_backup_is_preferred_replica before executing, so the log-backups will use the Availability group's backup preferences for the log backup only.

This way, I don't have to flip switches on the backup jobs every time we have a failover event, and I can always rely on getting my regular backups.
balasaukri
balasaukri
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 301
bartedgerton (7/22/2013)
A copy-only backup is not part of a restore set that involves transaction log backups. .


What do you mean?
Database can be restored using copy-only full and copy-only log backups.
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39299 Visits: 32621
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).

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
michael.mcloughlin
michael.mcloughlin
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 141
Did you take the database out of the AG before you tried to restore it? You then need to restore the database on all the replicas from the same backups and log files. Im currently trying to get my head around restoring a AG enabled database myself and so far this is what i've read. You can point your maintenance plans to back up to a UNC path but this doesn't seem to work.
bartedgerton
bartedgerton
SSC-Enthusiastic
SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)

Group: General Forum Members
Points: 131 Visits: 257
My current setup is to take nightly full backups on the primary, and hourly log backups on the secondary. I'm using PowerShell scripts to perform the backup so that I can have the same script running my backups on all nodes. I schedule the job to execute the PowerShell script on each node and it checks the following before running the backup job:

If it's primary and the last full backup is more than 24 hours old, and the database is online
If it's secondary and the last transaction log backup is more than 1 hour old, and the database is online.

My thought is that I won't have to do the exercise of enabling or disabling jobs every time we encounter a failover situation, so if the same job is running on all nodes, I'm always assured a good backup without having to take any actions during a failover.

The only issue I'm having now is that the backup times on the primary and secondary are not synchronized. I.e. - the transaction log backup on the secondary only updates data in the secondary's MSDB database, but the primary is not aware of the log backup at all. However, I have confirmed a few times that I can use the combination of primary full backup and secondary log backups to do a point-in-time restore. Also, I verified that the secondary log backup is indeed truncating the primary's log, however, the date doesn't get updated on the primary. I think that's a bug that should be addressed. In the meantime, I guess I'm going to have to create linked servers on each node pointing to the other nodes and query all the MSDB databases in the cluster to determine the max last backup time.. ugh..
HowardW
HowardW
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2231 Visits: 9892
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.
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39299 Visits: 32621
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Faisal Malik
Faisal Malik
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 238
You can't use copy_only backup in point in time recovery.
lkennedy76
lkennedy76
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1170 Visits: 906
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

MCSA SQL Server 2012
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86554 Visits: 45244
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search