I'm trying to understand the labyrinth that is the rules about AG backups.I've got Ola Hallengren's scripts installed on machines TST01/TST02. 02 is the preferred replica for backups. I have an AG with AdventureWorks in it and one database outside the AG - DBAutility.

I have the below in my SQL Agent job on TST01.

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q “EXECUTE [dbo].[DatabaseBackup] @Databases = ‘User_Databases’, @CopyOnly = ‘Y’,@Directory = N’C:\MyDirectory\’, @BackupType = ‘FULL’, @Verify = ‘Y’, @CleanupTime = 168, @CheckSum = ‘Y’, @LogToTable = ‘Y'” -b

When I run the job on TST01 I get a backup of DBAUtility taken from TST01 and written to that machine. I get no backup of AdventureWorks. I'm taking this approach from a post from Brent Ozar.

Brent on Ola H and AG Backups

I know Ola has other parameters for AG backups, but I couldn't seem to get that to work as expected either.

  • Did you add the scripts/backup job to the secondary as well? You will need to run it there, since it's the preferred replica.
    – MrTCS
    Aug 1 '17 at 16:34
  • Yes. Jobs are in both places. When I ran the job on the secondary it did what I expected. I think I was being tripped up by thinking about the prod environment we're building where we have a lot of SSIS jobs and so we want those not to run on the secondary. Maintenance jobs would be different though.
    – Lee M
    Aug 1 '17 at 18:01

I do this a lot with my Availability Group backups (using Ola's scripts). Since you have @CopyOnly = 'Y', and you have a Secondary server set as your preferred replica for backups, Ola's script will ignore this backup on your Primary and use the Secondary server for the FULL_COPY_ONLY backups (if the script is run there).

If you were to do away with the @CopyOnly = 'Y' parameter, the scripts would take a FULL backup on your Primary server (FULLs and DIFFs are not allowed on Secondaries).

Ola's scripts are smart when it comes to Availability Groups. If you have a preferred secondary specified, his scripts will try to use it for any backups that don't HAVE to be done on the Primary. Because of this, I wound up creating identical backup jobs across my Primary and Secondary servers, and each one runs whichever backups are appropriate based on the current status of each server.

  • A little question regarding the backup location when you do it like this (set up identical on all the servers): Do you have one common location on the network (a file share or somehting) where you back up to no matter what server it is? We usually use a local disc drive on each server for backups. Just wondered if not having a joint location would be a new point of failure (what if the whole primary site goes down)?
    – GHauan
    Jan 8 '18 at 11:51
  • I do both. I initially write the backups locally, and as part of my backup job have a script that also copies the backup over to a network share. However, the main reason I do this is because of a slow network connection to our file share. For me it is much faster to write the backups locally (which is the important part), and then copy them over the slow network (don't care if this takes a little longer). I will soon be writing my backups directly to Azure storage, and may or may not be keeping local copies. Still undecided on that.
    – Eric Cobb
    Jan 9 '18 at 15:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.