EnableException doesn't seem to do anything when using Restore-DbaDatabase

[tomaustin700]

Verified issue does not already exist?

Yes

What error did you receive?

Hi,

We are using Restore-DbaDatabase within a Powershell script within some Azure DevOps pipelines and due to the amount of concurrent pipelines we could have we have seen issues with Restore-DbaDatabase failing due to deadlocking. I have tried to handle this by catching the exception and retrying however it seems that Restore-DbaDatabase doesn't actually throw an exception in this scenario and just throws a warning instead even when EnableException is turned on.

Steps to Reproduce

Hard to duplicate my exact scenario however attempting to restore a file that doesn't exist does duplicate it, I'd expect a error to be thrown in this scenario as EnableException is turned on however just a warning is thrown

$server = "testserver"
$dbName = "testdb"
$backupName = "C:\Backup.bak
Restore-DbaDatabase -SqlInstance $server -DatabaseName $dbName  -Path $backupName -ReplaceDbNameInFile  -WithReplace -EnableException	

Are you running the latest release?

Yes

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 5.1.19041.1320
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.19041.1320
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) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Datacenter 10.0 <X64> (Build 17763: ) (Hypervisor)

.NET Framework Version

4.8

[andreasjordan]

You can not repro the issue like that. The warning you are getting is really only a warning followed by a return: https://github.com/dataplat/dbatools/blob/f9f49af365b8a79256bde3633988f1d8c6195423/functions/Restore-DbaDatabase.ps1#L674-L677

What is the text of the warning you are getting in your original issue? Then we can have a look at that part of the code.

[tomaustin700]

@andreasjordan Hi, this is the error we are seeing

[Invoke-DbaAdvancedRestore] Failed to restore db 
CC8_Automation_31cf6aeea04, stopping | System.Data.SqlClient.SqlError: Transaction (Process ID 102) was deadlocked on 
lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

[andreasjordan]

So here it failes: https://github.com/dataplat/dbatools/blob/b052b92c84a5cc13c821cb8fa266f8fd4295407a/functions/Invoke-DbaAdvancedRestore.ps1#L371-L377

This command is called here using EnableException: https://github.com/dataplat/dbatools/blob/b052b92c84a5cc13c821cb8fa266f8fd4295407a/functions/Restore-DbaDatabase.ps1#L741-L745

So I don't see any reason why there is no Exception raised.

[andreasjordan]

Maybe you can test the property ExitError of the returned object as a workaround? It should hold the error message.

[andreasjordan]

Maybe @Stuart-Moore can help?

[andreasjordan]

I played a little bit with the code - but I can not reproduce the issue. Invoke-DbaAdvancedRestore is always throwing an exception if I do an impossible restore.

Just to be sure: What is your current version of dbatools?

Are you getting any other commands to throw an exception? Like Invoke-DbaQuery -SqlInstance sql01 -Query "SELECT * FROM xyz" -EnableException with a correct SqlInstance but a nonexisting table?

[tomaustin700]

Hi @andreasjordan Dba tools version is 1.0.153 . Running the Invoke-DbaQuery you gave does produce an error so that's all good. The problem we have is the restore deadlock error is happening very intermittently (one/twice a month) so it can be hard to do any further debugging with it as we don't know when it's going to happen.

[andreasjordan]

Sorry, to say - your version is very old. If you still have problems with the current version I can have a deeper look.

[tomaustin700]

I didn't realise how out of date our version was, apologies about that. I'll update the version across our ADO agents and let you know if we see this issue again. Thanks for your assistance.

[tomaustin700]

Hi @andreasjordan, we've updated to the latest version of dbatools and unfortunately we are still seeing the same issue.

[andreasjordan]

Thanks, will try to come up with more ideas how to analyse this.

[andreasjordan]

Sorry to say: I don't have any idea. Is the problem still repoducable on your side?

[potatoqualitee]

} catch { 
     Write-Message -Level Verbose -Message "Failed, Closing Server connection" 
     $restoreComplete = $False 
     $ExitError = $_.Exception.InnerException 
     Stop-Function -Message "Failed to restore db $database, stopping" -ErrorRecord $_ -Continue 
     break 
 } finally { 

that break is never hit becuase of the -Continue.

try { 
     $FilteredBackupHistory | Where-Object { $_.IsVerified -eq $true } | Invoke-DbaAdvancedRestore -SqlInstance $RestoreInstance -WithReplace:$WithReplace -RestoreTime $RestoreTime -StandbyDirectory $StandbyDirectory -NoRecovery:$NoRecovery -Continue:$Continue -OutputScriptOnly:$OutputScriptOnly -BlockSize $BlockSize -MaxTransferSize $MaxTransferSize -BufferCount $Buffercount -KeepCDC:$KeepCDC -VerifyOnly:$VerifyOnly -PageRestore $PageRestore -EnableException -AzureCredential $AzureCredential -KeepReplication:$KeepReplication -StopMark:$StopMark -StopAfterDate:$StopAfterDate -StopBefore:$StopBefore -ExecuteAs $ExecuteAs 
 } catch { 
     Stop-Function -Message "Failure" -ErrorRecord $_ -Continue -Target $RestoreInstance 
 } 

That Invoke-DbaAdvancedRestore needs an -EnableException added to it.

[andreasjordan]

Invoke-DbaAdvancedRestore has -EnableException - it's just somewhere in the middle. Maybe we should use splatting here...

[potatoqualitee]

Ha! thank you. Maybe it needs an ErrorAction Stop too?

[andreasjordan]

Now I changed the code to reproduce the issue. I added a "throw" just before the catch block starting line 371 of Invoke-DbaAdvancedRestore, so that every restore "failes". And it works as expected: Goes to the catch block, and Stop-Function raises the exception. The finally block is still executed, but then it leaves Invoke-DbaAdvancedRestore and returns to Restore-DbaDatabase where it re-raises the exception. And I get the expected red message.

What I don't get is the message "[Invoke-DbaAdvancedRestore] Failed to restore db..." that @tomaustin700 is seeing. Will do more tests....

[andreasjordan]

I can only see this message when I'm directly starting Invoke-DbaAdvancedRestore without -EnableException.

So I have no idea what causes this behaviour. Maybe @FriedrichWeinmann can tell us more in which cases the message "Failed to restore db" would be visible.

And @tomaustin700 : As this issue is some months old: Does this still is the case? Can you reproduce with the current version of dbatools?

[tomaustin700]

Hi @andreasjordan, we've not seen this issue for quite a few months now. We were only seeing it before when we were getting deadlocks but this is no longer happening. At the time we could reproduce using the latest version (as of 21st Feb) but I've not seen it since then.

[andreasjordan]

Thanks for the feedback. Then I will close this for now, but we can reopen if you see the issue again.