SQL Server 2012 Logshipping Backup Job Completes Successfully but No Backup File Created
I encountered a strange issue while working on SQL Server 2012 AlwayaON Availability Group databases which also has Logshipping configured. After failover of AlwaysON Availability Group databases from primary to secondary, and also after moving the logshipping from new primary server to DR server, logshipping backup job was having issues where the job was completing successfully, but was not creating the log backup files. Below is the environment setup.
Three servers Server01, Server02, Server03 with Windows Server 2008 R2 as Operating System. Each server has one standalone default instance of SQL Server 2012. AlwaysON Availability Group (AG) and Listener “Prod_LSTNR” has been setup between Server01 and Server02. Logshipping has been setup from Server01 to Server03(This is just same way how we setup logshipping between any two server databases). AlwaysON and Logshipping has been working without any problems after the initial setup.
Now, when there was a planned maintenance to replace Server01 and Server02 with new hardware, we can failover AlwaysON AG to secondary server and also configure or move logshipping from new primary server to DR server. We decided to do this with minimum downtime to production users by moving all databases to one of the server and to perform the maintenance on the AlwaysoN AG secondary server.
As part of planned maintenance, we failed over everything to Server01 and performed maintenance on Server02 and added it back to the AlwaysON AG. Verified and AlwaysON AG databases got synchronized in some time and logshipping also was working fine without any problems.
Now we have to perform maintenance on Server01, so we moved the AlwaysON AG “Prod_LSTNR” to Server02, which was successful and databases are synchronized as well. As expected, there is no logshipping configuration on databases now as logshipping was only setup when databases were primary on Server01 to Server03. We decided to configure logshipping from Server02 to Server03 now, so that logshipping to Server03 happens when the AlwaysON AG databases are either on Server01 or Server02.
Disabled Logshipping Backup job on Server01, disabled logshipping copy and restore jobs on Server03, which disabled logshipping from Server01 to Server03. Took server01 down for maintenance and is no more available now. Configured logshipping from Server02 to Server03 which created new backup job on Server02 and new copy and restore jobs on Server03. All logshipping jobs started running without any errors.
Later, maintenance of Server01 finished and was online and got connected to AlwaysON AG “Prod_LSTNR” and successfully synchronized as secondary.
After some time, we started receiving alerts for logshipping that the copy and restore latency was above the thresholds. We checked and found backup, copy and restore jobs from Server02 to Server03 are all running successfully. Logshipping report shows that there was no backup, copy or restore performed since Server01 was back online. Checked the history of backup job and noticed that backup was not performing any backups, it was just skipping performing the log backup on Server02.
Started verifying the logshipping and AlwaysON settings and noticed that AlwaysON AG backup preferences was set to run backups on any replica with priority of 60 for Server01 and priority of 40 for Server02. As now, we have both Server01 and Server02 online and Server02 acting as AlwaysON Primary, log backups cannot be performed on Server02 due to low priority and that Server01 is online. Changed the AlwaysON AG backup preferences to run backups on Primary Server, after which backup job started creating the backup files successfully. Later copy and restore jobs also starting working without any issues or latency.
You can find AlwaysON backup preferences from SQL Server management Studion (SSMS) -> Expand Always On High Availability -> Availability Groups -> Open properties of Availability Group where the databases in part of -> Go to Backup preferences tab, where we can change the settings.
If we see the issue only from logshipping perspective, we could not have identified the issue, looking into the issue as AlwaysON along with logshipping helped us in identifying the issue.
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings