How can I cancel all import/export operations on an Azure SQL Server using PowerShell

user_10735126 0
Viewed 1031

I'm trying to work out the PowerShell syntax for cancelling all pending import/export operations on an Azure SQL Server. I know that I can use Stop-AzSqlDatabaseActivity cmdlet but this requires a database name (the database might not already exist so piping Get-AzSqlDatabase won't work). Is there something I can do without specifying the databases, only the server?

Thanks!

azure-powershell azure-sql-database powershell
2 Answers

Other than the Stop-AzSqlDatabaseActivity cmdlet, you can also use Database Operations-Cancel API Rest API to cancel import or export operations & you need to pass the Database name which is a mandatory parameter As per the current Azure Documentation.

user_159687200

Open new PowerShell window, you may use cloud shell on Azure portal as well by clicking the cloud shell button at the top right at your portal screen.

enter image description here

Copy and paste the following PowerShell code and execute it - it will create a function for the current PowerShell session

function Cancel-AzSQLImportExportOperation
{
    param
    (
        [parameter(Mandatory=$true)][string]$ResourceGroupName
        ,[parameter(Mandatory=$true)][string]$ServerName
        ,[parameter(Mandatory=$true)][string]$DatabaseName
    )

    $Operation = Get-AzSqlDatabaseActivity -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName $DatabaseName | Where-Object {($_.Operation -like "Export*" -or $_.Operation -like "Import*") -and $_.State -eq "InProgress"}
    
    if(-not [string]::IsNullOrEmpty($Operation))
    {
        do
        {
            Write-Host -ForegroundColor Cyan ("Operation " + $Operation.Operation + " with OperationID: " + $Operation.OperationId + " is now " + $Operation.State)
            $UserInput = Read-Host -Prompt "Should I cancel this operation? (Y/N)"
        } while($UserInput -ne "Y" -and $UserInput -ne "N")

        if($UserInput -eq "Y")
        { 
            "Canceling operation"
            Stop-AzSqlDatabaseActivity -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName $DatabaseName -OperationId $Operation.OperationId
        }
        else 
        {"Exiting without cenceling the operation"}
        
    }
    else
    {
        "No import or export operation is now running"
    }
}

Cancel-AzSQLImportExportOperation

use the function

Cancel-AzSQLImportExportOperation​

to cancel an Import or Export operation you need to provide the Resource Group name, Server name and Database name where the operation is currently running.

user_81841390
Related