New-DbaDbSnapshot doesnt create snapshot on a mirrored database

[pralli123]

Environmental information

#### PowerShell version:

Name                           Value                                                                                                                                                          
----                           -----                                                                                                                                                          
PSVersion                      5.1.14393.2879                                                                                                                                                 
PSEdition                      Desktop                                                                                                                                                        
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}                                                                                                                                        
BuildVersion                   10.0.14393.2879                                                                                                                                                
CLRVersion                     4.0.30319.42000                                                                                                                                                
WSManStackVersion              3.0                                                                                                                                                            
PSRemotingProtocolVersion      2.3                                                                                                                                                            
SerializationVersion           1.1.0.1                                                                                                                                                        




#### dbatools Module version:


Name    : dbatools
Path    : C:\Program Files\WindowsPowerShell\Modules\dbatools\0.9.832\dbatools.psd1
Version : 0.9.832

SQL Server:

Microsoft SQL Server 2008 R2 (SP3-GDR) (KB4057113) - 10.50.6560.0 (X64)   Dec 28 2017 15:03:48   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 14393: ) (Hypervisor) 

Report

New-DbaDbSnapshot doesn't create a snapshot when run on a mirrored database. Works on normal ones

Errors Received

PSMessageDetails      : 
Exception             : System.Management.Automation.ExtendedTypeSystemException: The following exception occurred while trying to enumerate the collection: "An exception occurred while 
                        executing a Transact-SQL statement or batch.". ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a 
                        Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: The database "MIRRORED_DATABASE" cannot be opened. It is acting as a mirror database.
                           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 ---
                           at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
                           at Microsoft.SqlServer.Management.Smo.ExecuteSql.ExecuteImmediate(String query)
                           at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm)
                           at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb)
                           at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
                           at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
                           at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()
                           at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)
                           at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
                           at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorDataReader(Request req)
                           at Microsoft.SqlServer.Management.Smo.SqlSmoObject.InitChildLevel(Urn levelFilter, ScriptingPreferences sp, Boolean forScripting, IEnumerable`1 extraFields)
                           at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.InitializeChildCollection(Boolean refresh, ScriptingPreferences sp, String filterQuery, IEnumerable`1 
                        extraFields)
                           at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.GetEnumerator(ScriptingPreferences sp)
                           at System.Management.Automation.EnumerableOps.GetEnumerator(IEnumerable enumerable)
                           --- End of inner exception stack trace ---
                           at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
                           at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
                           at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
                           at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
                           at System.Management.Automation.Interpreter.Interpreter.Run(InterpretedFrame frame)
                           at System.Management.Automation.Interpreter.LightLambda.RunVoid1[T0](T0 arg0)
                           at System.Management.Automation.PSScriptCmdlet.RunClause(Action`1 clause, Object dollarUnderbar, Object inputToProcess)
                           at System.Management.Automation.PSScriptCmdlet.DoProcessRecord()
                           at System.Management.Automation.CommandProcessor.ProcessRecord()
TargetObject          : 
CategoryInfo          : NotSpecified: (:) [], ExtendedTypeSystemException
FullyQualifiedErrorId : ExceptionInGetEnumerator
ErrorDetails          : 
InvocationInfo        : System.Management.Automation.InvocationInfo
ScriptStackTrace      : at New-DbaDbSnapshot<Process>, C:\Program Files\WindowsPowerShell\Modules\dbatools\0.9.832\allcommands.ps1: line 76117
                        at Select-DefaultView<Process>, C:\Program Files\WindowsPowerShell\Modules\dbatools\0.9.832\allcommands.ps1: line 118924
                        at Get-DbaDatabase<Process>, C:\Program Files\WindowsPowerShell\Modules\dbatools\0.9.832\allcommands.ps1: line 31757
                        at <ScriptBlock>, <No file>: line 1
PipelineIterationInfo : {}

Steps to Reproduce

import-module dbatools
$SQLServer = "sqlserver"
$Database = "MIRRORED_DATABASE"
New-DbaDbSnapshot -SqlInstance $SQLServer -Database $Database -NameSuffix "{0}_ss" -verbose

Expected Behavior

Snapshot is created on Mirrored database

Actual Behavior

No snapshot is created. Verbose message is:

[New-DbaDbSnapshot] MIRRORED_DATABASE is not accessible, skipping

[potatoqualitee]

Thank you for the details! 🏆 Will turn that Verbose into a throw/continue since it does not seem possible for SMO to do.

[potatoqualitee]

(Can you get it to work in regular T-SQL?)

[pralli123]

Yes I can make it working in T-SQL
CREATE DATABASE MIRRORED_DATABASE_SS ON ( NAME = MIRRORED_DATABASE_data, FILENAME = 'I:\SQLDATA\MIRRORED_DATABASE_SnapShot.ss') AS SNAPSHOT OF MIRRORED_DATABASE;

I think the problem is when New-DbaDbSnapshot tries to create it its checking if the database in question is accessible. A mirrored database is of course never directly accessible but can be snapshotted.

So in line 187 it could be checking this "-and $db.IsMirroringEnabled -ne $true" :

            ## double check for gotchas
            foreach ($db in $dbs) {
                if ($db.IsDatabaseSnapshot) {
                    Write-Message -Level Warning -Message "$($db.name) is a snapshot, skipping"
                } elseif ($db.name -in $NoSupportForSnap) {
                    Write-Message -Level Warning -Message "$($db.name) snapshots are prohibited"
                } elseif ($db.IsAccessible -ne $true -and $db.IsMirroringEnabled -ne $true) {
                    Write-Message -Level Verbose -Message "$($db.name) is not accessible, skipping"
                } else {
                    $InputObject += $db
                }
            }

[potatoqualitee]

excellent info, thank you!