OLA Availability Group and ChangeBackupType option

  • kevinswain

    Ten Centuries

    Points: 1213

    Hi

    I have an OLA problem

    I have an AG running on MSSQL 2017 Standard with a single Secondary

    The database is 2008 version

    When i fallover the database i would expect to see the backup change from LOG to FULL with the ChangeBackupType option set.

    When the database fall's over and the transaction log job runs i get a transaction log backup without a full backup on the new server.

    Job script

    EXECUTE [dbo].[DatabaseBackup]

    @databases = 'AVAILABILITY_GROUP_DATABASES',

    @Directory = N'F:\MSSQLBackup\SQLBackups',

    @BackupType = 'LOG',

    @verify = 'Y',

    @CleanupTime = 48,

    @checksum = 'Y',

    @LogToTable = 'Y',

    @compress = 'Y',

    @ChangeBackupType='Y'

    resulting backup command

    BACKUP LOG [xxDATABASE] TO DISK = N'F:\MSSQLBackup\SQLBackups\AWS-SQLCLUS-01$AWS-xxDATABASE\xxDATABASE\LOG\AWS-SQLCLUS-01$AWS-xxDATABASE_xxDATABASE_LOG_20200331_100802.trn' WITH CHECKSUM, COMPRESSION

    Kevin

  • anthony.green

    SSC Guru

    Points: 112444

    That is correct that a log backup happens, the full backup only needs to be taken should a FULL not already have taken place.

    As a full has already happened on the original primary server no full is needed to be taken.

    With AG's its best to store the backups in a central location to both servers so that each server can append the next file to the location, it is not advised to have backups local to each machine on the off chance one of the machines is unreachable and you loose parts of your backup chain.

     

    Change Backup option only works if the database has never had a FULL done to it on "ANY" replica

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply