5

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!

Anna Gevel
  • 1,103
  • 1
  • 11
  • 20
Dave Hall
  • 51
  • 1
  • 5

2 Answers2

5

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.

Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30
1

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.

VenkateshDodda
  • 4,723
  • 1
  • 3
  • 12
  • Thanks, we ended up working round a different way of restoring (from serverless databases using the CREATE DATABASE FROM syntax) to get us round this. Seems it was an issue with the stage the blob was at importing and this way restores much more nicely anyway. – Dave Hall Apr 05 '22 at 10:58