False Positive: AvailabilityGroup w/preference SECONDARY not executing @CopyOnly backups issued from PRIMARY replica

[graybeam]

Description of the issue In a database availability group scenario where the backup prefrence is "SECONDARY", and you issue a backup on the PRIMARY and supply the @CopyOnly parameter, the backup does not occur however it gives you a false-positive result message that it completed successfully. If you omit @CopyOnly = 'Y', the backup occurs as issued on the primary to the destination supplied.

SQL Server version and edition Microsoft SQL Server 2019 (RTM-CU18) (KB5017593) - 15.0.4261.1 (X64) Sep 12 2022 15:07:06 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Datacenter 10.0 (Build 17763: ) (Hypervisor)

Version of the script --// Source: https://ola.hallengren.com //-- --// License: https://ola.hallengren.com/license.html //-- --// GitHub: https://github.com/olahallengren/sql-server-maintenance-solution //-- --// Version: 2022-12-03 17:23:44

What command are you executing? EXECUTE [master].[dbo].[DatabaseBackup] @Databases = 'TestDatabase', @Directory = N'G:\MSSQL\Backup\Copy_Only', @BackupType = 'FULL', @CheckSum = 'Y', @LogToTable = 'N', @Verify = 'N', @COMPRESS = 'Y', @COPYONLY = 'Y'

What output are you getting? Date and time: 2023-03-10 19:19:01 Server: APUS-PQ19-DB01 Version: 15.0.4261.1 Edition: Enterprise Edition: Core-based Licensing (64-bit) Platform: Windows Procedure: [master].[dbo].[DatabaseBackup] Parameters: @Databases = 'TestDatabase', @Directory = 'G:\MSSQL\Backup\Copy_Only', @BackupType = 'FULL', @Verify = 'N', @CleanupTime = NULL, @CleanupMode = 'AFTER_BACKUP', @Compress = 'Y', @CopyOnly = 'Y', @ChangeBackupType = 'N', @BackupSoftware = NULL, @CheckSum = 'Y', @BlockSize = NULL, @BufferCount = NULL, @MaxTransferSize = NULL, @NumberOfFiles = NULL, @MinBackupSizeForMultipleFiles = NULL, @MaxFileSize = NULL, @CompressionLevel = NULL, @Description = NULL, @Threads = NULL, @Throttle = NULL, @Encrypt = 'N', @EncryptionAlgorithm = NULL, @ServerCertificate = NULL, @ServerAsymmetricKey = NULL, @EncryptionKey = NULL, @ReadWriteFileGroups = 'N', @OverrideBackupPreference = 'N', @NoRecovery = 'N', @URL = NULL, @Credential = NULL, @MirrorDirectory = NULL, @MirrorCleanupTime = NULL, @MirrorCleanupMode = 'AFTERBACKUP', @MirrorURL = NULL, @AvailabilityGroups = NULL, @Updateability = 'ALL', @AdaptiveCompression = NULL, @ModificationLevel = NULL, @LogSizeSinceLastLogBackup = NULL, @TimeSinceLastLogBackup = NULL, @DataDomainBoostHost = NULL, @DataDomainBoostUser = NULL, @DataDomainBoostDevicePath = NULL, @DataDomainBoostLockboxPath = NULL, @DirectoryStructure = '{ServerName}${InstanceName}{DirectorySeparator}{DatabaseName}{DirectorySeparator}{BackupType}{Partial}{CopyOnly}', @AvailabilityGroupDirectoryStructure = '{ClusterName}${AvailabilityGroupName}{DirectorySeparator}{DatabaseName}{DirectorySeparator}{BackupType}{Partial}{CopyOnly}', @FileName = '{ServerName}${InstanceName}{DatabaseName}{BackupType}{Partial}{CopyOnly}{Year}{Month}{Day}{Hour}{Minute}{Second}{FileNumber}.{FileExtension}', @AvailabilityGroupFileName = '{ClusterName}${AvailabilityGroupName}{DatabaseName}{BackupType}{Partial}{CopyOnly}{Year}{Month}{Day}{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}', @FileExtensionFull = NULL, @FileExtensionDiff = NULL, @FileExtensionLog = NULL, @Init = 'N', @Format = 'N', @ObjectLevelRecoveryMap = 'N', @ExcludeLogShippedFromLogBackup = 'Y', @DirectoryCheck = 'Y', @StringDelimiter =... Version: 2022-12-03 17:23:44 Source: https://ola.hallengren.com

Date and time: 2023-03-10 19:19:02 Database: [TestDatabase] State: ONLINE Standby: No Updateability: READ_WRITE User access: MULTI_USER Recovery model: FULL Encrypted: No Is accessible: Yes Availability group: USPQContent-AG Availability group role: PRIMARY Availability group backup preference: SECONDARY Is preferred backup replica: No Differential base LSN: 15042000000069900001 Last log backup LSN: 15060000003359100001 Allocated extent page count: N/A Modified extent page count: N/A

Date and time: 2023-03-10 19:19:02

Completion time: 2023-03-10T11:19:02.2593947-08:00

[Stan-C421]

I came across the same issue some time ago, please specify the following: OverrideBackupPreference = 'Y'

OverrideBackupPreference

Override the backup preference for availability groups. This option only applies to copy-only full backups and regular transaction log backups.

Value | Description -- | -- Y | Override the backup preference for availability groups. N | Do not override the backup preference for availability groups. This is the default.

[olahallengren]

It is working as designed.

The intention is that you should have the copy-only full backup jobs (and all other backup jobs) running on all replicas (and with the same parameters).