Set-DbaDbQueryStoreOption fails if database snapshots exist and aren't explicitly excluded

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

Exception calling "Alter" with "0" argument(s): "Alter failed for  'Microsoft.SqlServer.Management.Smo.ObjectKeyBase'. "
At line:75358 char:25
+                         $db.QueryStoreOptions.Alter()
+                         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : FailedOperationException

WARNING: [16:37:57][Set-DbaDbQueryStoreOption] Could not modify configuration. | The operation cannot be performed on a database snapshot.
ALTER DATABASE statement failed.

writeErrorStream      : True
PSMessageDetails      : 
Exception             : System.Exception: The operation cannot be performed on a database snapshot.
                        ALTER DATABASE statement failed. ---> Microsoft.Data.SqlClient.SqlException: The operation cannot be performed on a database snapshot.
                        ALTER DATABASE statement failed.
                           at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
                           at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
                           --- End of inner exception stack trace ---
TargetObject          : [**db_name_redacted**]
CategoryInfo          : InvalidOperation: ([**db_name_redacted**]:PSObject) [Write-Error], Exception
FullyQualifiedErrorId : dbatools_Set-DbaDbQueryStoreOption,Stop-Function
ErrorDetails          : The operation cannot be performed on a database snapshot.
                        ALTER DATABASE statement failed.
InvocationInfo        : System.Management.Automation.InvocationInfo
ScriptStackTrace      : at Stop-Function, <No file>: line 97916
                        at Set-DbaDbQueryStoreOption<Process>, <No file>: line 75460
                        at <ScriptBlock>, <No file>: line 7
PipelineIterationInfo : {0, 1}

Steps to Reproduce

Create a database snapshot of any database on an instance - this can only be done via a T-SQL command rather than through the GUI of SQL Management Studio. See the BOL for pre-requisites and details of the command.

CREATE DATABASE database_snapshot_name
ON
(
    NAME = logical_file_name,
    FILENAME = 'os_file_name'
)
AS SNAPSHOT OF source_database_name

Now try to standardise your Query Store settings for the instance by using Set-DbaDbQueryStoreOption without specifically excluding this new snapshot.

Set-DbaDbQueryStoreOption -SqlInstance "**instance_name_redacted**" `
                -State ReadWrite `
                -StaleQueryThreshold 90 `
                -FlushInterval 900 `
                -CollectionInterval 30 `
                -MaxSize 500 `
                -MaxPlansPerQuery 200 `
                -WaitStatsCaptureMode On

Whilst it's relatively simple to pass in an exclusion list that includes any snapshots, it would be simpler if the command handled this as a known limitation.

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

2.1.6

Other details or mentions

No response

What PowerShell host was used when producing this error

Windows PowerShell (powershell.exe), Windows PowerShell ISE (powershell_ise.exe)

PowerShell Host Version

Name Value

PSVersion 5.1.19041.3803
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.19041.3803
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

SQL Server Edition and Build number

Microsoft SQL Server 2019 (RTM-CU22-GDR) (KB5029378) - 15.0.4326.1 (X64) Aug 18 2023 14:05:15 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Enterprise 10.0 (Build 19045: ) (Hypervisor)

.NET Framework Version

.NET Framework 4.8.9195.0

dataplat / dbatools