Sql-server – Backing Up an AG with Ola Hallengren

availability-groupsbackupola-hallengrensql serversql server 2014

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.

Best Answer

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.

Related Solutions

SQL Server – Implement Ola Hallengren Backup Plan for Different Databases

No you don't need to install the Ola's solution again since the initially installed commandexecute.sql and databasebackup.sql would have been saved on master DB as SP.

You just need to create a new job , as one created early but this time for 5 left over DB;s and schedule it weekly per you're timings.

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',-- Name of those 5 DB's would come here
@Directory = 'C:\Backup',-- You're backup path
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@CheckSum = 'Y'

enter image description here You can use above script while creating the T-SQL job and schedule it accordingly.

More details on how to do above refer to schedule job

Also, just to make sure both, initial backup and this newly created backup job not to run on same time you can put a condition or per the analysis when you're daily backup job completes, you can schedule this new weekly/nightly backup job for remaining 5 DB's

Ola Hallengren Backup Solution Error After Moving Files – Job Stops

After further digging (and almost emailing Ola) I found the source of my confusion.

After querying the CommandLog table (@LogToTable = 'Y') found that while the TSQL job steps were reporting the error, the job actually completed, logged the commands to the table as successful (and of course the files existed on disk.)

It looks like not only do TSQL job steps stop reporting on the first error, which is referenced in a different part of the FAQ.

If you are seeing that the SQL Server Agent History reports a failing job, verify that the same failure is reported in the master.dbo.CommandLog table, and ensure you have @LogToTable = 'Y' specified.

Ola's job is behaving as described in the FAQ, and the reference to TSQL job steps was the hint I needed.

Best Answer

Related Solutions

SQL Server – Implement Ola Hallengren Backup Plan for Different Databases

Ola Hallengren Backup Solution Error After Moving Files – Job Stops

Related Question