Install-DbaMaintenanceSolution: verification cannot be turned off, even when backing up to NUL should force us to

[ReeceGoding]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

N/A, technically. This doesn't fail in a way that dbatools will see. You either silently get the wrong thing or only get an error when you run the Agent Job, but the job only errors because we've told it to do something it can't.

The two problems I demonstrate here are:

1. -Verify cannot be turned off by any means I could find.
2. Backing up to "NUL" while -Verify is turned on is permitted by Install-DbaMaintenanceSolution even though [dbo].[DatabaseBackup] does not permit this.

I don't think that we're currently doing anything to defend against the second problem. As for the first, I suspect the issue is on this line but I don't see it.

Steps to Reproduce

Recall the documentation for the -Verify parameter of Install-DbaMaintenanceSolution.

.PARAMETER Verify
Verifies backup integrity immediately after creation by performing a RESTORE VERIFYONLY operation.
Defaults to enabled (Y) if not specified. Verification adds time to backup operations but ensures backups are restorable.
Only applies when InstallJobs is specified.

Now consider the following three situations.

Verify and backup to default location

The below gives us exactly what we have asked for. It works.

$primary = # Your machine.

$params = @{
SqlInstance = $primary
InstallJobs = $true
Force = $true
ReplaceExisting = $true
Verify = $true
}
Install-DbaMaintenanceSolution @params

The resulting job step can be obtained from

(Get-DbaAgentJobStep -SqlInstance $primary -Job 'DatabaseBackup - USER_DATABASES - LOG').Command

and on my machine, this is

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES',
@Directory = N'/var/opt/mssql/data',
@BackupType = 'LOG',
@Verify = 'Y',
@CleanupTime = NULL,
@CheckSum = 'Y',
@LogToTable = 'N'

If we repeat this process with -Verify unspecified, we get the same result. This is what the documentation told us.

Don't verify

So what about if we don't want to verify? Well, if $true turned it on and not mentioning -Verify also turned it on, the only sensible remaining choices are $false or $null.

$primary = # Your machine. I find that it helps to re-connect.

$params = @{
SqlInstance = $primary
InstallJobs = $true
Force = $true
ReplaceExisting = $true
Verify = $false
}
Install-DbaMaintenanceSolution @params

(Get-DbaAgentJobStep -SqlInstance $primary -Job 'DatabaseBackup - USER_DATABASES - LOG').Command

The returned T-SQL is

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES',
@Directory = N'/var/opt/mssql/data',
@BackupType = 'LOG',
@Verify = 'Y',
@CleanupTime = NULL,
@CheckSum = 'Y',
@LogToTable = 'N'

Similarly,

$primary = # Your machine. I find that it helps to re-connect.

$params = @{
SqlInstance = $primary
InstallJobs = $true
Force = $true
ReplaceExisting = $true
Verify = $null
}
Install-DbaMaintenanceSolution @params

(Get-DbaAgentJobStep -SqlInstance $primary -Job 'DatabaseBackup - USER_DATABASES - LOG').Command

gives the same. Neither of these have turned off -Verify.

Verify and backup to Nul

You may ask why I don't want to verify. It's because sometimes, Ola forces you not to! The below gives us what we ask for, but Ola does not allow it because you cannot both verify and backup to NUL

$primary = # Your machine. I find that it helps to re-connect.

$params = @{
SqlInstance = $primary
InstallJobs = $true
Force = $true
ReplaceExisting = $true
Verify = $true
BackupLocation = "NUL"
}
Install-DbaMaintenanceSolution @params

(Get-DbaAgentJobStep -SqlInstance $primary -Job 'DatabaseBackup - USER_DATABASES - LOG').Command

The returned T-SQL is

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES',
@Directory = N'NUL',
@BackupType = 'LOG',
@Verify = 'Y',
@CleanupTime = NULL,
@CheckSum = 'Y',
@LogToTable = 'N'

which is not allowed. If you have a user database to backup, then you can dig the error message out of

Start-DbaAgentJob -SqlInstance $primary -Job 'DatabaseBackup - USER_DATABASES - LOG'
(Get-DbaAgentJobHistory -SqlInstance $primary -Job 'DatabaseBackup - USER_DATABASES - LOG').Message

said error includes

[SQLSTATE 01000] (Message 50000) [SQLSTATE 01000] (Message 50000) The value for the parameter @verify is not supported. Verify is not supported when backing up to NUL. [SQLSTATE 42000] (Error 50000)

Please confirm that you are running the most recent version of dbatools

2.7.17

Other details or mentions

No response

What PowerShell host was used when producing this error

Windows PowerShell (powershell.exe)

PowerShell Host Version

Name Value

---

PSVersion 7.6.0-preview.6
PSEdition Core
GitCommitId 7.6.0-preview.6
OS Debian GNU/Linux forky/sid
Platform Unix
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion 2.4
SerializationVersion 1.1.0.1
WSManStackVersion 3.0

SQL Server Edition and Build number

Microsoft SQL Server 2025 (RTM-CU1) (KB5078298) - 17.0.4006.2 (X64)
Jan 22 2026 18:05:52
Copyright (C) 2025 Microsoft Corporation
Enterprise Developer Edition (64-bit) on Linux (Ubuntu 24.04.3 LTS)

.NET Framework Version

.NET 10.0.0

[ReeceGoding]

Best guess: line 733

if ($InstallJobs -and ($ChangeBackupType -or $Compress -or $CopyOnly -or $Verify -or $CheckSum -or $ModificationLevel)) 

is triggered because Ola checks your system's config and dynamically turns on checksumming.

  ----------------------------------------------------------------------------------------------------
  --// Get default checksum                                                                       //--
  ----------------------------------------------------------------------------------------------------

  IF @Checksum IS NULL
  BEGIN
    SELECT @Checksum = CASE WHEN EXISTS(SELECT * FROM sys.configurations WHERE name = 'backup checksum default' AND value_in_use = 1) THEN 'Y'
                            WHEN NOT EXISTS(SELECT * FROM sys.configurations WHERE name = 'backup checksum default' AND value_in_use = 1) THEN 'N' END
  END

[ReeceGoding]

I've looked deeper. This may be simple. Ola's default is to turn verification on. The code

  INSERT INTO @Jobs ([Name], CommandTSQL, DatabaseName, OutputFileNamePart01, OutputFileNamePart02)
  SELECT 'DatabaseBackup - USER_DATABASES - LOG',
         'EXECUTE [dbo].[DatabaseBackup]' + CHAR(13) + CHAR(10) + '@Databases = ''USER_DATABASES'',' + CHAR(13) + CHAR(10) + CASE WHEN @BackupURL IS NOT NULL THEN '@URL = N''' + REPLACE(@BackupURL,'''','''''') + '''' ELSE '@Directory = ' + ISNULL('N''' + REPLACE(@BackupDirectory,'''','''''') + '''','NULL') END + ',' + CHAR(13) + CHAR(10) + '@BackupType = ''LOG'',' + CHAR(13) + CHAR(10) + '@Verify = ''Y'',' + CHAR(13) + CHAR(10) + '@CleanupTime = ' + ISNULL(CAST(@CleanupTime AS nvarchar),'NULL') + ',' + CHAR(13) + CHAR(10) + '@Checksum = ''Y'',' + CHAR(13) + CHAR(10) + '@LogToTable = ''' + @LogToTable + '''',
         @DatabaseName,
         'DatabaseBackup',
         'LOG'

produces this SQL on my machine

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES',
@Directory = NULL,
@BackupType = 'LOG',
@Verify = 'Y',
@CleanupTime = NULL,
@Checksum = 'Y',
@LogToTable = 'Y'

Our code for modifying this is

# Add Verify parameter for all backup jobs
if ($Verify) {
    if ($modifiedCommand -notmatch "@Verify") {
        $modifiedCommand = $modifiedCommand -replace "(@LogToTable = '[YN]')", "`$1,$([System.Environment]::NewLine)@Verify = 'Y'"
    }
}

Which only triggers if $Verify is truthy. But if $Verify is truthy, then I cannot be asking to turn verification off.

I wonder if checksums are also impossible to turn off?

[ReeceGoding]

Skimmed over the tests. It seems that the tests only check for turning things on, not off.
https://github.com/dataplat/dbatools/blob/development/tests/Install-DbaMaintenanceSolution.Tests.ps1#L224