Silence Output From Azure SQL Powershell Commands

user_1088099 0
Viewed 573

I have a Powershell script to copy our production database to our test environment as well as associated clean up activities. I'd like to clean up the information outputted for the user to the console, and am having trouble figuring out clean up the error messages I get from my Az.sql commands.

One of my first actions is to attempt to see if a temporary database exists for and needs to be cleaned up

#clean up existing
$availableDatabase = Get-AzSqlDatabase -ResourceGroupName $TestResourceGroup -ServerName $TestServerName -DatabaseName $TestTempDatabaseName

If it's not there I'll get the following error output in the console:

Remove-AzSqlDatabase : The Resource 
'Microsoft.Sql/servers/xxxx/databases/reap_prod_copy_2' under resource group 'xxxx' was not found.
At C:\Users\xxxx\Desktop\reap-replace-test.ps1:17 char:1
+ Remove-AzSqlDatabase -ResourceGroupName $TestResourceGroup -ServerNam ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : CloseError: (:) [Remove-AzSqlDatabase], CloudException
+ FullyQualifiedErrorId : Microsoft.Azure.Commands.Sql.Database.Cmdlet.RemoveAzureSqlDatabase

I would like to capture this output and provide a simple "Does not exist" message instead, or even just completely move on without any output. I've tried putting "| Out-Null" on the end, but that doesn't seem to effect the output in any way. Any insights?

azure-sql-database powershell
2 Answers

You just need to use if(){}else{} statement to do that, try the script as below, it works fine on my side.

$availableDatabase = Get-AzSqlDatabase -ResourceGroupName $TestResourceGroup -ServerName $TestServerName -DatabaseName $TestTempDatabaseName -ErrorAction SilentlyContinue
if ($availableDatabase){

    Remove-AzSqlDatabase -ResourceGroupName $TestResourceGroup -ServerName $TestServerName -DatabaseName $TestTempDatabaseName
    Write-Host "Remove the sql db" $TestTempDatabaseName

}else{
    Write-Host "The sql db" $TestTempDatabaseName "does not exist" 

    }

Test result:

enter image description here

enter image description here

user_94556590

You can use a try catch block to capture the exception.

try {
    #clean up existing
    $availableDatabase = Get-AzSqlDatabase -ResourceGroupName $TestResourceGroup -ServerName $TestServerName -DatabaseName $TestTempDatabaseName
} 
catch {
    Write-Host "Does not exist"
    $availableDatabase = $null
}

if ($availableDatabase -ne $null) {
    # ... Do stuff with database
}

The above will execute everything in the try block and if it encounters an error of any type it will execute everything in the catch block. The if statement may or may not be necessary depending on implementation.

See about_Try_Catch_Finally

Note: The above catch block will catch any error, regardless of if it was caused by a missing table or not. It is generally a best practice to include a specific exception type after catch when possible to avoid silencing unrelated errors that may need to be fixed.

user_50962870
Related