asked on Database Log Backups failing on SQL 2014 Availability Group
My current setup is two servers running Windows 2012 R2 and SQL 2014. I have the two servers in an availability group and all seems to be functioning normally other than the log backups for a specific db. It is running every 15 minutes and when run it generates this error
ource: Back Up Database Task Execute SQL Task Description: Executing the query "DECLARE @preferredReplica int SET @preferredRep..." failed with the following error: "Log backup for database "Database" on a secondary replica failed because the last backup LSN (0x00000512:000306f0:0001)
These backups were working just fine until one of my servers crashed (SQL01) which was originally the primary. I had a problem with the master database so SQL02 became the primary until I was able to restore the master DB from a backup a few days earlier. Since then when SQL02 is the primary the backups work great. Once I make SQL01 (which previously had the master DB problem) then this is when the log backups fail.
Any assistance would be appreciated.
ource: Back Up Database Task Execute SQL Task Description: Executing the query "DECLARE @preferredReplica int SET @preferredRep..." failed with the following error: "Log backup for database "Database" on a secondary replica failed because the last backup LSN (0x00000512:000306f0:0001)
These backups were working just fine until one of my servers crashed (SQL01) which was originally the primary. I had a problem with the master database so SQL02 became the primary until I was able to restore the master DB from a backup a few days earlier. Since then when SQL02 is the primary the backups work great. Once I make SQL01 (which previously had the master DB problem) then this is when the log backups fail.
Any assistance would be appreciated.
Microsoft SQL Server
Last Comment
Jeff Perry
ASKER
The Automated Backup is set to secondary, This is most likely because on the properties of the AG under backup preferences it is set to Prefer Secondary
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Ok. I made the change so I should know in about 15 minutes or so.
Hope it goes well. Take note that your t-log backup may run longer than you are used to as it hasn't run in some time.
ASKER
That seems to have fixed the problem. Thanks for the assistance.
SELECT * FROM master.sys.availability_gr