Sql-server – Excluding Read only Log backups using ola-hallengren script

availability-groupsola-hallengrensql serversystem-tables

I know that Full backups are excluded from readable secondaries, but how do I exclude log backups from read-only secondaries?

The is_read_only column in sys.databases does not get set on secondaries, so that is not an option.

This link talks about the is_read_only column but is not authoritative enough.

Best Answer

The main restrictions on backups in an Availability Group are:

  • Full backups on a secondary must be COPY_ONLY.
  • Differential backups cannot be taken on a secondary.

Your backup application should take into account the backup preference setting of your availability group, but it is not forced to do so. Ola's scripts do respect this setting and only backup on the preferred replica.

If you're seeing log backups on your secondary, check the backup preferences in the AG, as they may have Prefer Secondary set, which means log backups could happen on any replica. Change this to Primary Only and you won't see log backups on your secondary with Ola's scripts.

NOTE: is_read_only in "sys.databases" only indicates if the database is in READ_ONLY or READ_WRITE mode. This is not the same as a readable AG secondary database. See this link for more info.

Related Question