Besides being unaware that your backup is being performed as a COPY_ONLY backup instead of not being backed up at all, are there any caveats that anyone can think of if this switch is set to default Y or even if it's used at all?
A COPY_ONLY full backup is still a full backup. The only difference between the two is the COPY_ONLY will not reset certain tracking bitmaps. This means your differential backups will eventually be the size of your database.
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/copy-only-backups-sql-server
This is wrong and should be set to 1
.
If you want it set to 1, then you should configure your backup preferences properly.
The reason it is not returning a 1
is because it shouldn't be based on your current preferences. Right now, all of your replicas have the exact same backup priority which means we're going to next look at replica names. Since UK-DR-SQL001\WSS
sorts first, that should be the secondary (given that UK-SB-SQL01\WSS
is your primary) that the function returns a 1
on.
If you want the backups to happen on a different server, then set the priority appropriately.
Here is an extremely simple repro showing that given your current settings, UK-DR-SQL001\WSS
should be the replica which a 1
is returned.
CREATE TABLE #Replicas
(
ReplicaName VARCHAR(40) NOT NULL,
BackupPriority INT NOT NULL
)
GO
INSERT INTO #Replicas(ReplicaName, BackupPriority)
Values('UK-DR-SQL001\WSS', 50)
,('UK-SB-SQL01\WSS',50)
,('UK-SB-SQL02\WSS',50)
GO
-- current case UK-SB-SQL01\WSS is the PRIMARY
-- prefer secondary is set
-- default collation assumed
SELECT ReplicaName, BackupPriority
FROM #Replicas
WHERE ReplicaName <> 'UK-SB-SQL01\WSS'
ORDER BY BackupPriority DESC --most important first
, ReplicaName ASC
I'm running the Ola Hallengren Maintenance Solution and noticed the transaction logs weren't being backed up on the secondary, hence not being truncated, despite the AG backup preference being set to Prefer Secondary
It seems to be that this isn't setup or running on the DR server, or a backup would at least be attempted.
Best Answer
I'm not sure if you mean the user interface, or if the actual effect of the change didn't occur as you expected. My answer assumes the latter.
To be clear about something that I find a little confusing: this setting is not something that automatically affects how backups are run. It's simply a place where you, as a DBA, can store "metadata" about how you prefer backups to be taken.
This is documented, but it's not obvious at all from the user interface. Here's the quote from the docs:
After setting this, you need to update your backup jobs with code like this (also lifted from the docs):
Using
sp_helptext
to view the source ofsys.fn_hadr_backup_is_preferred_replica
, it can be seen that this takes into account both the AG-levelAUTOMATED_BACKUP_PREFERENCE
setting, and the replica-levelBACKUP_PRIORITY
setting.Note: since you're using Ola's scripts, this is already taken into account
If you just didn't see the value change in the user interface, I would suggest using the "script" button to show the T-SQL that's being run by the UI, and then running that script manually to see if the change is properly made.