No warning or error when CopyOnly is set to N on secondary node

[mikbreper]

Description
When the variable CopyOnly is set to 'N' on a secondary replica, the procedure does not report that the backup is not executed. What I would expect was along the lines of the MS standard error message for this operation:
Msg 3059, Level 16, State 1, Line 1
This BACKUP or RESTORE command is not supported on a database mirror or secondary replica.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

An entry in the commandlog for a failed attempt of backup could perhaps be useful.

This is not a huge issue, but it can cause a bit of head ache when troubleshooting why backups aren't being performed as expected on secondary.

SQL Server version and edition
Microsoft SQL Server 2022 (RTM-CU16) (KB5048033) - 16.0.4165.4 (X64) Nov 6 2024 19:24:49 Copyright (C) 2022 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2022 Datacenter 10.0 (Build 20348: ) (Hypervisor)

Version of the script
2025-01-04 20:17:18

What command are you executing?
EXEC msdb.dbo.DatabaseBackup @databases = [mydatabase]
, @url = 'myurl'
, @LogToTable = 'Y'
, @BackupType = 'Full'
, @verify = N
, @compress = Y
, @Checksum = N
, @copyonly = N
, @NumberOfFiles = 4
, @BufferCount = 50
, @MaxTransferSize = 20971520
, @Encrypt = Y
, @EncryptionAlgorithm = AES_256
, @ServerCertificate = 'mycert';

What output are you getting?
Date and time: 2025-01-22 11:12:14
Server: myserver
Version: 16.0.4165.4
Edition: Developer Edition (64-bit)
Platform: Windows
Procedure: [msdb].[dbo].[DatabaseBackup]
Parameters: @databases = 'MyDatabase', @Directory = NULL, @BackupType = 'Full', @verify = 'N', @Cleanuptime = NULL, @CleanupMode = 'AFTER_BACKUP', @compress = 'Y', @CompressionAlgorithm = NULL, @copyonly = 'N', @ChangeBackupType = 'N', @backupsoftware = NULL, @Checksum = 'N', @Blocksize = NULL,
@BufferCount = 50, @MaxTransferSize = 20971520, @NumberOfFiles = 1, @MinBackupSizeForMultipleFiles = NULL, @MaxFileSize = NULL, @CompressionLevel = NULL, @description = NULL, @threads = NULL, @Throttle = NULL, @Encrypt = 'Y',
@EncryptionAlgorithm = 'AES_256', @ServerCertificate = 'mycert', @ServerAsymmetricKey = NULL, @EncryptionKey = NULL, @ReadWriteFileGroups = 'N', @OverrideBackupPreference = 'N', @NoRecovery = 'N', @url = 'myurl', @credential = NULL,
@MirrorDirectory = NULL, @MirrorCleanupTime = NULL, @MirrorCleanupMode = 'AFTER_BACKUP', @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}',
@DirectoryStructureCase = NULL, @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}',
@FileNameCase = NULL, @TokenTimezone = 'LOCAL', @FileExtensionFull = NULL, @FileExtensionDiff = NULL, @FileExtensionLog = NULL, @In...
Version: 2025-01-04 20:17:18
Source: https://ola.hallengren.com

Date and time: 2025-01-22 11:12:14
Database: [MyDatabase]
State: ONLINE
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Recovery model: FULL
Encrypted: No
Availability group: myag
Availability group role: SECONDARY
Availability group backup preference: SECONDARY
Is preferred backup replica: Yes
Differential base LSN: 39000001520000001
Last log backup LSN: 39000001676800001
Allocated extent page count: N/A
Modified extent page count: N/A

Date and time: 2025-01-22 11:12:15

Completion time: 2025-01-22T11:12:09.5960246+01:00

[olahallengren]

Imagine that you want to do full (non copy_only) backups on the primary replica, and assure that it works after a failover. Then you want to have the jobs configured identically on both servers, and you don't want them to fail.

I was thinking about outputting some informational message that the database was not backed up (and for what reason) that does not make the job fail.

Would that work for you?

[mikbreper]

I think that would be a good solution.

Thank you, and keep up the good work. Extremely happy with the work you've put down to make it compatible with s3 storage and all the improvements of late.

[olahallengren]

Start by reproducing the issue in SSMS. This can be in any database.

I will add this to the backlog. I just have some other issues that I need to fix first.

Thank you, and keep up the good work. Extremely happy with the work you've put down to make it compatible with s3 storage and all the improvements of late.

Thank you. I am happy you like it.