1

I’m using the @AvailabilityGroups= parameter to backup availability groups, is it possible to NOT backup (exclude) a database in the Availability group?

If using the @Databases parameter I could use - but I am using the @AvailabilityGroups parameter instead.

Thanks

0

You can't use both the @Databases and @AvailabilityGroups parameter. If you try, you'll get this error:

Msg 50000, Level 16, State 1, Procedure DatabaseBackup, Line 633 [Batch Start Line 0] You can only specify one of the parameters @Databases and @AvailabilityGroups.

For your condition, you'd want to only use @Databases with something like

@Databases = 'DB1, DB2'
@Databases = 'AVAILABILITY_GROUP_DATABASES, -DB1'
  • Thanks @scsimon. If I use @ Databases = 'AVAILABILITY_GROUP_DATABASES, -DB1' will the BackupPreference of the availability group be used if this backup runs on every SQLServer of the availability group? As I only want the backup to be performed on the 'PreferSecondary' SQLServer? – J. Skoyles Sep 19 at 16:15
  • I'm almost certain the preferred backup setting is only used by the Maintenance Plan for user databases. I am 100% certain that using Ola's scripts, you can run this on the primary or secondary replica. If you only want this ran on the primary, or only on the secondary, you can use sys.fun_hadr_is_primary_replica in Step 1 of your Agent Job to check this and eithe rquite the job, or proceed to the second step where you are doing your backups. – scsimon Sep 19 at 17:39
  • Hi, I've dome some testing, if you use the below an all server in a availability group, the backup will only be created from the prefered secondary. – J. Skoyles Sep 20 at 15:54
  • Hi, I've dome some testing, if you use the below on all SQL servers in a availability group, the backup will only be created from the preferred secondary. You have to ensure you backup priority is set correctly, so Server02 = 50 Server03 = 40 Server04 = 30 server01 = 20 sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @ Databases = '<DB names>', @ BackupType = 'FULL', @ Verify = 'N', @CleanupTime = 72, @ CheckSum = 'Y', @ Compress ='Y', @ CopyOnly = 'Y', " -b <had to add space after @ > – J. Skoyles Sep 20 at 16:01

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.