Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Always on Secondry database backup plan & Recovering. Expand / Collapse
Author
Message
Posted Monday, July 22, 2013 8:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 11:32 AM
Points: 19, Visits: 101
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.
Post #1476037
Posted Monday, July 22, 2013 10:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 03, 2014 9:30 PM
Points: 40, Visits: 230
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.
Post #1476120
Posted Tuesday, July 23, 2013 9:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:42 PM
Points: 14,840, Visits: 27,315
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1476636
Posted Monday, July 29, 2013 7:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 7:31 AM
Points: 20, Visits: 100
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.
Post #1478599
Posted Monday, July 29, 2013 8:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 11:32 AM
Points: 19, Visits: 101
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..
Post #1478613
Posted Monday, July 29, 2013 9:11 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 1,207, Visits: 9,351
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.
Post #1478638
Posted Monday, July 29, 2013 9:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:42 PM
Points: 14,840, Visits: 27,315
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1478640
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse