In Azure, the Import/Export operation of the Azure SQL database is a vital part of the database migration methods. It is important to choose the most viable option as per the database migration strategy and business requirements.

There are several common scenarios where Azure PaaS database can be scripted, copied, migrated, moved, or backed up. These scenarios include:

• Starting the backup
• Creating a snapshot of the database
• Exporting Azure SQL database to an on-premise server
• Copying a database between different database environments
• Migrating a database from on-premise to Azure PaaS database
• Maintaining the integrity of the database state prior to making any changes
• Making a database clone of a production database to troubleshoot and investigate performance issues
• Auditing the database clone process

When it comes to importing or exporting a database between on-premises and Azure SQL, there are several options available. If you need faster and predictable import/export options, consider the following:

• Import or Export using PowerShell
• Import or Export to a BACPAC file using SQLPackage utility
• Import or Export to a BACPAC file using SQL Server Management Studio (SSMS)
• Import or Export Azure SQL database using PowerShell

Let’s take a look at how to export an Azure SQL database to Azure blob storage using PowerShell:

# Create a connection context
Connect-AzAccount
Get-AzSubscription -SubscriptionName <SubscriptionName> | Set-AzContext

# Prepare the connection string
$AdminUser = 'myAzuredemp19'
$AdminPassword = ConvertTo-SecureString -String 'SEDF7e%$#20' -AsPlainText -Force
$Credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $AdminUser, $AdminPassword

# Get the storage key and storage URI
$storageAccountName = 'yourStorageAccountName'
$ResourceGroupName = 'yourResourceGroupName'
$context = New-AzStorageContext -StorageAccountName $storageAccountName -StorageAccountKey (Get-AzStorageAccountKey -Name $storageAccountName -ResourceGroupName $ResourceGroupName)[0].value
$container = Get-AzStorageContainer -Name $containername -Context $Context
$storageURI = $container.CloudBlobContainer.Uri.OriginalString + "/sqlagent.bacpac"

# Prepare export command
$export = New-AzSqlDatabaseExport `
    -ResourceGroupName $ResourceGroupName `
    -ServerName $ServerName `
    -DatabaseName $DatabaseName `
    -AdministratorLogin $Credential.UserName `
    -AdministratorLoginPassword $Credential.Password `
    -StorageKeyType StorageAccessKey `
    -StorageUri $storageURI `
    -StorageKey (Get-AzStorageAccountKey -Name $storageAccountName -ResourceGroupName $ResourceGroupName)[0].value

# Get the status of the export operation
Get-AzSqlDatabaseImportExportStatus -OperationStatusLink $export.OperationStatusLink

It is important to note that there may be some issues when starting an export operation using the New-AzSqlDatabaseExport cmdlet. If you encounter the error message “Missing the required ‘networkIsolation’ parameters for ImportExport operation”, you can use the older version of the Az.Sql module (2.9.1) to resolve the issue.

Another method to import or export a BACPAC file is by using the SQLPackage utility. Here is an example of how to export a database from an on-premise server:

C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin>SqlPackage.exe /TargetFile:"D:\PS_Scripts\Prashanth\agenjob.bacpac" /Action:Extract /SourceServerName:"sqlagentjob.database.windows.net" /SourceDatabaseName:"agentjob" /SourceUser:"mydempuser2020" /SourcePassword:"MKIu7*$%34e%15"

If you encounter an error message regarding the database compatibility level, you can resolve it by setting the compatibility level of the database to 130 using T-SQL.

Alternatively, you can use SQL Server Management Studio (SSMS) to import or export a BACPAC file. This method is useful for migrating an on-premises SQL Server database to an Azure SQL database.

In summary, performing import/export operations on Azure SQL databases using PowerShell provides a flexible and efficient way to migrate, backup, and clone databases. It is important to consider the service tiers, performance, and database size when choosing the appropriate method for your specific scenario.

By following the steps outlined in this article, you can easily perform Azure SQL database import/export operations using PowerShell.