Checking LastDiffBackup checks SQL database snapshots and fails

[packfan73]

Bug Report

General Troubleshooting steps

DBAChecks version 2.0.18

Run command: Invoke-DbcCheck -SqlInstance server_with_snaps -Check LastDiffBackup

Results show good for normal databases, but the database snapshots all return errors like this:

  [-] Database DB-SNAPSHOT_20240709 diff backups should be less than 25 hours old on SERVER_WITH_SNAPS 6ms
    Expected the actual value to be greater than 2024-07-09T11:41:10.0826562Z, because Taking regular backups is extraordinarily important, but got 0001-01-01T06:00:00.0000000Z.
    530:                             ($psitem.LastBackupDate.ToUniversalTime(), $psitem.LastDifferentialBackupDate.ToUniversalTime() | Measure-Object -Max).Maximum | Should -BeGreaterThan (Get-Date).ToUniversalTime().AddHours( - ($maxdiff)) -Because 'Taking regular backups is extraordinarily important' at C:\Program Files\WindowsPowerShell\Modules\dbachecks\2.0.18\checks\Database.Tests.ps1: line 530

Version Information

• Operating System Windows Server 2019
• PowerShell Version: 5.1.17763.5328
• SQL Server: 15.0.4073.23(2019)

Steps to Reproduce

Running the above code on a server with database snapshots. These are not snapshot backups, but created using CREATE DATABASE ... AS SNAPSHOT.

This same issue also occurs with the -Check LastFullBackup option, not just differentials.

 

Description of Bug

These should be excluded from checks as they can't possibly be backed up in any way. I think this would be done in the selection of databases on the server. A snapshot will have a non-NULL source_database_id coulumn in sys.databases.

If not excluded, maybe add a command line option to ignore database snapshots.

[dbaduck]

I vote for excluding Database Snapshots. You should not have to exclude them with a parameter in my opinion.