March 9, 2013 at 7:14 am
Hi All,
Can any one suggest me one good backup plan for Always on secondaries server... ?Currently i am planning to do
One Full copy only backup--- Every 24 hour :
Every 30 min Log backup.
I am looking for the solution for recovering the database using this backup strategy.
March 9, 2013 at 7:32 am
Why a copy only full backup? Why not a normal full backup?
March 9, 2013 at 7:35 am
I am implementing the backup plan on my secondary server.....We cannot the take the normal backup from this.....
Please suggest me..
March 9, 2013 at 7:41 am
Okay, I was just curious as I haven't looked at Always On as yet. Thanks for the tip.
March 9, 2013 at 9:30 am
Is there any suggestion from anyone...?
March 9, 2013 at 9:56 am
Suggestions for what?
How you recover with the backup strategy you listed is pretty easy, restore latest full backup, restore all log backups.
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
March 9, 2013 at 10:15 am
here the problem is i can schedule copy only full backup and log backup on secondary server...
Using this is it possible....?
March 9, 2013 at 10:23 am
Hello,
I have configured the SQL server always on with 4 server, and out of these 3 server is synchronizing real time (Database and data is secured).
But when i am implementing the Backup plan in Always on secondary server, there we cannot take the normal full backup & Differential backup..and possible only Copy only full backup and Log backup. Using these copy only full backup and log backup i am not able to recover the database.
So how to manage this scenario ?
🙂
March 9, 2013 at 10:24 am
ratheesh4sql (3/9/2013)
here the problem is i can schedule copy only full backup and log backup on secondary server...Using this is it possible....?
Have you tried? If so, does it not work? If not, why exactly and with what errors?
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
March 9, 2013 at 12:13 pm
You mean to say that we can recover the data using copy only full backup and log backup.....but unfortunately i couldnt restore it successfully. 🙁
I tried below scenario
2 pm - Copy only Backup
2.30 pm - Log backup
3.00pm - Log backup'
i couldn't restore 2pm copy only full backup+ log backup which i have taken.
There is a LSN related error's was coming.
July 22, 2013 at 8:33 am
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.
July 22, 2013 at 10:20 am
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.
July 23, 2013 at 9:08 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 29, 2013 at 7:55 am
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.
July 29, 2013 at 8:29 am
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..
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply