Sql-server – Ola Hallengren MaintenanceSolution – Differential – the value for @ModificationLevel is not supported

backupola-hallengrensql server

I just installed this great tool, mostly it's working fine, just that if I include for a differential backup the parameter @ModificationLevel = 50 (or anything else) the action fails:

Msg 50000, Level 16, State 1, Procedure DatabaseBackup, Line 2268
The value for the parameter @ModificationLevel is not supported.

Example:

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'ALL_DATABASES', @CleanupTime = 2160, @Compress = 'Y', 
@Directory = '\\cem01nas2\sql_backups',
@BackupType = 'DIFF', @ChangeBackupType = 'Y', @ModificationLevel = 50 
GO

Gives the error, but if I remove the @ModificationLevel parameter it works. Any ideas?

Best Answer

This indicates that you are hitting the validation (taken from Ola Hallengren's site here)

  IF @ModificationLevel IS NOT NULL AND NOT EXISTS(SELECT * FROM sys.all_columns WHERE object_id = OBJECT_ID('sys.dm_db_file_space_usage') AND name = 'modified_extent_page_count')
  BEGIN
    INSERT INTO @Errors ([Message], Severity, [State])
    SELECT 'The value for the parameter @ModificationLevel is not supported.', 16, 1
  END

  IF @ModificationLevel IS NOT NULL AND @ChangeBackupType = 'N'
  BEGIN
    INSERT INTO @Errors ([Message], Severity, [State])
    SELECT 'The value for the parameter @ModificationLevel is not supported.', 16, 2
  END

  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

Please check the conditions and also keep in mind the @ModificationLevel is the difference needed for a Full backup to occur. If there is not enough difference it won't do the Full backup.

IF @ChangeBackupType = 'Y'
    BEGIN
      IF @CurrentBackupType = 'LOG' AND @CurrentRecoveryModel IN('FULL','BULK_LOGGED') AND @CurrentLogLSN IS NULL AND @CurrentDatabaseName <> 'master'
      BEGIN
        SET @CurrentBackupType = 'DIFF'
      END
      IF @CurrentBackupType = 'DIFF' AND (@CurrentDatabaseName = 'master' OR @CurrentDifferentialBaseLSN IS NULL OR (@CurrentModifiedExtentPageCount * 1. / @CurrentAllocatedExtentPageCount * 100 >= @ModificationLevel))
      BEGIN
        SET @CurrentBackupType = 'FULL'
      END
    END
Related Question