How to copy an Azure SQL database from one subscription to another subscription using PowerShell

Below instructions are useful when you would like to copy the database from one Azure subscription to another subscription using PowerShell.

• Install PowerShell on your computer. For requirements and installation, refer to the url: https://docs.microsoft.com/en-us/powershell/azure/install-az-ps?view=azps-2.2.0
• After the installation, open the “Windows PowerShell ISE” as an administrator.
• Install the Azure PowerShell module using the command below (press ‘Y’ to install the modules when asked).
  • PS > Install-Module -Name Az -AllowClobber
• Connect to your azure account. Run the below command and provide your azure username and password.
  • PS > Connect-AzAccount
• Verify the subscriptions you have access.
  • PS > get-AzSubscription

Export database (as .bacpac file)

• Using the subscription details from above, set the subscription parameters to the source database subscription.
  • PS > $SourceSubscriptionId = “<SubscriptionId>”
  • PS > $SourceTenantId = “<TenantId>”
• Run the below command to set the source subscription.
  • PS > Get-AzSubscription -SubscriptionId $SourceSubscriptionId -TenantId $SourceTenantId | Set-AzContext
• Set the PowerShell variables for the database export.
  • PS > $ExportResourceGroupName = “<Source ResourceGroupName>”
  • PS > $ExportServerName = “<Source DB Server name>”
  • PS > $ExportDatabaseName = “<Source DB name>”
  • PS > $ExportAdministratorLogin = “<Source DB username>”
  • PS > $ExportAdministratorLoginPassword = “<Source DB password>”
• Set the BLOB storage variables for the database export. The same details shall be used while importing the database.
  • PS > $StorageKeyType = “StorageAccessKey”
  • PS > $StorageKey = “<Storage_key> ”
  • PS > $StorageUri = “<Storage_url>” (An example of storage url is “http://test.blob.core.net/bacpacs/DatabaseExport.bacpac&#8221; )
• Now run the below PowerShell command to export the database into BLOB.
  • PS > $ExportDB = New-AzSqlDatabaseExport -ResourceGroupName $ExportResourceGroupName -ServerName $ExportServerName -DatabaseName $ExportDatabaseName -StorageKeyType $StorageKeyType -StorageKey $StorageKey -StorageUri $StorageUri -AdministratorLogin $ExportAdministratorLogin -AdministratorLoginPassword $(ConvertTo-SecureString -String $ExportAdministratorLoginPassword -AsPlainText -Force)
• Wait for the database export to complete (Status: Succeeded, in the below command). Run the below command to see the database export status.
  • PS > Get-AzSqlDatabaseImportExportStatus -OperationStatusLink $ExportDB.OperationStatusLink

Import database (from .bacpac file exported)

• Verify the subscriptions you have access.
  • PS > get-AzSubscription
• Using the subscription details above, set the subscription parameters to the destination database subscription.
  • PS > $DestinationSubscriptionId = “<SubscriptionId>”
  • PS > $DestinationTenantId = “<TenantId>”
• Run the below command to set the destination subscription.
  • PS > Get-AzSubscription -SubscriptionId $SourceSubscriptionId -TenantId $SourceTenantId | Set-AzContext
• Set the PowerShell variables for the database import.
  • PS > $ImportResourceGroupName = “<DestinationResourceGroupName>”
  • PS > $ImportServerName = “<Destination DB Server name>”
  • PS > $ImportDatabaseName = “<Destination DB name>”
  • PS > $ImportAdministratorLogin = “<Destination DB username>”
  • PS > $ImportAdministratorLoginPassword = “<Destination DB password>”
• Now run the below PowerShell command to import the database from the BLOB.
  • PS > $importDB = New-AzSqlDatabaseImport -ResourceGroupName $ImportResourceGroupName -ServerName $ImportServerName -AdministratorLogin $ImportAdministratorLogin -AdministratorLoginPassword $(ConvertTo-SecureString -String $ImportAdministratorLoginPassword -AsPlainText -Force) -DatabaseMaxSizeBytes “250000000” -DatabaseName $ImportDatabaseName  -Edition “Standard” -ServiceObjectiveName “S1” -StorageKey $StorageKey -StorageKeyType “StorageAccessKey” -StorageUri $StorageUri
• Wait for the database import to complete (Status: Succeeded, in the below command). Run the below command to see the database import status.
  • PS > Get-AzSqlDatabaseImportExportStatus -OperationStatusLink $ImportDB.OperationStatusLink

Things to Consider

• Should have an Azure account with two different subscriptions.
• Should have the db_owner access on the source database and the ‘dbmanager’ role on the destination Azure SQL server.
• Should have “SQL Server Contributor” role on the source and the destination resource groups.
• Should have “Storage Blob Data Owner” role on the Azure storage account.
• Import/Export might fail if there is a mismatch of resource group and server names. These are case sensitive.