DIFFs promoted to FULLs

Question

We use Ola Hallengren's DatabaseBackup stored procedure to backup a load of SharePoint databases on a SQL Server 2012 instance into Azure blob storage. We have been doing this for quite a while without any issues. However, for the last 6 weeks our DIFFs are randomly being promoted to FULL and we cannot find out why.

This is the output from the agent step

BACKUP DATABASE [Database] TO URL = N'https://strorgage.blob.core.windows.net/server/instance/Database/2020/11/diff/Database_FULL_20201105_200000.bak' 
WITH NO_CHECKSUM, COMPRESSION, CREDENTIAL = N'*storeageaccountname*'

If you have a look at the generted URL you will note that the procedures is storing in the DIFF directory, but creating a FULL backup file.

https://strorgage.blob.core.windows.net/server/instance/Database/2020/11/diff/Database_FULL_20201105_200000.bak
                                                                        --^           --^

DatabaseBackup (Ola proc) is from 2019-06-14 so it needs an upgrade to be fair, but it has worked well for over 18 months.

We do not call Ola code directly as we have a small wrapper procedure which builds up the virtual path name for Azure, but essentially this is how we call Ola's code.

This is an issue were for some unknown reason DIFF backups are being promoted to FULL, this causes petabytes of Azure blob backups instead of gigabytes - each day.

EXECUTE dbo.DatabaseBackup
    @Database = @DatabaseName,
    @URL = @BackupPath,
    @Credential = @StorageAccount,
    @BackupType = @backupType,
    @Compress = @Compression,
    @LogToTable = 'Y',
    @ChangeBackupType = 'Y',
    @Updateability = @DatabaseReadOnlyState,
    @DirectoryStructure = NULL,
    @AvailabilityGroupDirectoryStructure = NULL

Do you have any thoughts on that?

Answer 1

You say that this promotion of DIFF to FULL backups is "random" but I'd wager you can find a connection between this activity and data churn (or index maintenance) in the database itself.

Because you're using ChangeBackupType='Y', the backup job is looking at sys.dm_db_file_space_usage to see how much of the database has been changed, and performs a FULL backup if it exceeds a threshold (I'm having difficulty discerning the default threshold from the source code). You can change that threshold by adjusting the ModificationLevel parameter, which is a percentage. From the documentation

ModificationLevel
Specify a percentage when a differential backup will be changed to a full backup. This option can only be used together with @ChangeBackupType = 'Y'. DatabaseBackup checks allocated_extent_page_count and modified_extent_page_count in sys.dm_db_file_space_usage to calculate how much of a database that has been modified.

Answer 2

Introduction

Looking at your code, it seems that you are already using the @ChangeBackupType parameter Ola provided in his SQL Server Maintenance Solution for the DatabaseBackup stored procedure. The documentation of this parameter provides the following bit of information:

DatabaseBackup checks differential_base_lsn in sys.master_files to determine whether a differential backup can be performed. If a differential backup is not possible, then the database is skipped by default. Alternatively, you can set ChangeBackupType to Y to have a full backup performed instead.

^relevant ...and...

DatabaseBackup checks last_log_backup_lsn in sys.database_recovery_status to determine whether a transaction log backup in full or bulk-logged recovery model can be performed. If a transaction log backup is not possible, then the database is skipped by default. Alternatively, you can set ChangeBackupType to Y to have a differential or full backup performed instead.

^{not relevant}

^{Reference: DatabaseBackup (ola.hallengren.com)}

Assumption

Seeing as you are using the parameter in question and assuming that your databases are all running in FULL recovery model, then I would expect Ola' scripts to do as they were told and just perform a differential backup, like you were previously observing.....

However

...something is altering the SharePoint databases in such a way, that Ola's procedure is assuming that the database requires a FULL backup. Ola checks various situations, one of which is based on the parameter....

ModificationLevel

There is the additional parameter @ModificationLevel that would convert a DIFF backup to a FULL backup if the first parameter @ChangeBackupType = 'Y' is set. Looking at Ola's code provides us with this:

  IF @ModificationLevel IS NOT NULL AND @BackupType <> 'DIFF'
  BEGIN
    INSERT INTO @Errors ([Message], Severity, [State])
    SELECT 'The value for the parameter @ModificationLevel is not supported.', 16, 3
  END

That means that if the parameter @ModifcationLevel is set to a value and @ChangeBackupType is set to Y, then the backup procedure will convert the DIFF backup to a FULL backup If the amount of pages changed triggers the case.

Because you haven't set @ModificationLevel it remains NULL as can be seen in Ola's code:

@ModificationLevel int = NULL,

This doesn't seem to be the case in your situation, unless of course your parameter's value for @ModificationLevel is not NULL.

Solution 1

In which case, we have found the culprit. Change the value for @ModificationLevel back to NULL and all is well.

Further Reasons for Conversion

Another reason the backup would change from DIFF to FULL is the parameter @ChangeBackupType itself.

The description (from above) was written as:

DatabaseBackup (the procedure) checks differential_base_lsn in sys.master_files to determine whether a differential backup can be performed. If a differential backup is not possible, then the database is skipped by default. Alternatively, you can set ChangeBackupType to Y to have a full backup performed instead.

Chekcing the Code

Ola wrote this in the code:

SELECT @CurrentDifferentialBaseLSN = differential_base_lsn
FROM sys.master_files
WHERE database_id = DB_ID(@CurrentDatabaseName)
AND [type] = 0
AND [file_id] = 1

and this part here:

   IF @CurrentBackupType = 'DIFF'
    BEGIN
      SELECT @CurrentDifferentialBaseIsSnapshot = is_snapshot
      FROM msdb.dbo.backupset
      WHERE database_name = @CurrentDatabaseName
      AND [type] = 'D'
      AND checkpoint_lsn = @CurrentDifferentialBaseLSN
    END

    IF @ChangeBackupType = 'Y'
    BEGIN
      IF @CurrentBackupType = 'DIFF' AND @CurrentDifferentialBaseIsSnapshot = 1
      BEGIN
        SET @CurrentBackupType = 'FULL'
      END
    END;

What does this mean?

Translating Ola's Code

Well it reads a bit like this:

1. Get the value of differntial_base_lsn for the current database

2. If the backup type is DIFF then...

   • Read the is_snapshot column in the msdb.dbo.backupset table for the current database with the @CurrentDifferentialBaseLSN and the backup type is D (Database backup)
   • If
     • @ChanageBackupType is Y` and
     • @CurrentBackupType is `DIFF and
     • @CurrentDifferentialBaseIsSnapshot is 1
   • Then
     • Set @CurrentBackupType to FULL

Here you have a possible situation...

Solution 2

...if your database was backed up by a third party solution (CommVault, NetApp, et. al.), then the 3-rd party solution will have created a valid and consistent database backup using the SQL Server VSS Writer service, which will make a note in the msdb.dbo.backupset that a snapshot copy of the database was taken, which sets the is_snapshot parameter for that database for the given differntial_base_lsn which your DIFF would be based upon.

Because of this the DIFF backup you are trying to perform can no longer be based on the is_snapshot backup and must create a new FULL backup again, to reset the is_snapshot and the differntial_base_lsn value, and to create a new base for future DIFF backups.

Find the Other Backup

You will have to determine which 3rd-party (or other backup solutionb) is meddling with your implemented soltuion and ensure that they are either...

1. modified to co-exist
2. minimized to one backup solution.