Backup Availability Group using Hallengren solution

[dpaskiet]

@ola-hallengren I am a DBA of 17 years and now for a company in north eastern Pennsylvania in the US. I have inherited a large cluster of nonsense in my new role. I have used your scripts before but not in the way they are currently set up here and I think it has been done woefully wrong. My backups are not reliable. I do not think they are using the BU correctly with regards to the AG’s. I am looking for a little help here. I have 15 AG’s and want to separate out the backup process. I have some large DB's in each AG, as well as some db's not in an AG, and the BU needs to be separated. This may seem a bit of a dumb ask but current efforts using the doc on-line are not producing the results I need.

Scenario:
If my AG groups are named AG_PROD_SET_1, AG_PROD_SET_2 and AG_PROD_SET_3. How would I specify that the full and/or log backup use the databases in that AG_PROD_SET_1 only? I have two examples of the current usage below but they don’t seem to work as I would expect.

EXECUTE [dbo].[DatabaseBackup]
@databases = 'AVAILABILITY_GROUP_DATABASES',
@Directory = N'\BUserver\SQL04INstance_backups',
@BackupType = 'LOG',
@compress = 'Y',
@verify = 'N',
@Cleanuptime = 168,
@Checksum = 'Y',
@LogToTable = 'Y'

EXECUTE [dbo].[DatabaseBackup]
@databases = 'AVAILABILITY_GROUP_DATABASES',
@Directory = N'\BUserver\SQL04INstance_backups',
@BackupType = 'LOG',
@compress = 'Y',
@verify = 'N',
@Cleanuptime = 168,
@Checksum = 'Y',
@LogToTable = 'Y'

EXECUTE [dbo].[DatabaseBackup]
@databases = 'USER_DATABASES, -AVAILABILITY_GROUP_DATABASES',
@Directory = N'\BUserver\SQL04INstance_backups',
@BackupType = 'LOG',
@compress = 'Y',
@verify = 'N',
@Cleanuptime = 168,
@Checksum = 'Y',
@LogToTable = 'Y'

[Stiffboard]

Make it less complicated and only use USER_DATABASES. Point to same UNC path and everything will end up in same folder. Use AG setting to decide whether backups should run on primary or secondary. If you got the right enterprise license.

[dpaskiet]

Can you provide an example? I have always just used the user_databases but everything here at my current position goes off AG's. I am hoping to keep their same mindset and just separate things. I agree that the user_databases option is far less complicated and easier to manage.

[olahallengren]

I agree that the simplest approach is to have one set of jobs backing up all databases, regardless of availability groups. However, if you prefer to back up the databases in availability groups separately, you can use the following parameter: https://ola.hallengren.com/sql-server-backup.html#AvailabilityGroups

[dpaskiet]

Can you give me an example of how to use the parameter? I have looked at the docs already and cant make it work as I intend.

[olahallengren]

Could you try this:

EXECUTE [dbo].[DatabaseBackup]
@AvailabilityGroups = 'AG_PROD_SET_1',
@Directory = N'\BUserver\SQL04INstance_backups',
@BackupType = 'LOG',
@compress = 'Y',
@verify = 'N',
@Cleanuptime = 168,
@Checksum = 'Y',
@LogToTable = 'Y'

[dpaskiet]

Thanks. Maybe I fat fingered my attempts but that is what I thought I did. I appreciate your time.

[olahallengren]

I'm happy that you got it working.