how to export bacpac file to azure blob storage from sql server using sqlpackage

Question

I have some databases in the SQL server and I want to migrate them to the Azure SQL database for this I am using sqlpackage.exe for exporting and importing the databases. the issue is while exporting the database bacpac files are made in local disk, not in the azure storage account. I want to know how I can move it to an Azure storage account.

Answer 1

Hi @Shubhi Jain , welcome to Microsoft Q&A forum.

This could be achieved in 2 steps, in the first step you have already generated the bacpac file. In 2nd step you can use AzCopy command as mentioned in below article:

How to extract bacpac using sqlpackage from onprem and directly send it to azure storage account?

Another way is to use SSMS to export the bacpac to Azure Storage Account:

Migrate an on-premises SQL Server database to an Azure SQL database

Please let us know if this helps or else we can discuss further on this.

----------

If answer helps, you can mark it 'Accept Answer'

Answer 2

Hi @Shubhi Jain , thanks for your response.

<Writing it as another answer as word limit was crossing 1600 characters>

After we have generated the bacpac we can run below command to upload bacpac to storage account. You can generate your own SAS and use it here:

azcopy copy "<location of bacpac>" "https://xxxxx.blob.core.windows.net/testcontainer/?sv=2020-08-04&ss=bfqt&srt=sco&sp=gghghhgtfx&se=2021-09-18T15:00:04Z&st=2021-09-14T07:00:04Z&spr=https&sig=sdhjdshjdhjhjhwgb0bD6hY2T%2Bs%2FJxtfkJIRA%3D"  

Get started with AzCopy

After this we can run below command to import this bacpac to azure SQL Database:

$importRequest = New-AzSqlDatabaseImport -ResourceGroupName $resourceGroupName `  
    -ServerName $serverName `  
    -DatabaseName $databaseName `  
    -DatabaseMaxSizeBytes 100GB `  
    -StorageKeyType "StorageAccessKey" `  
    -StorageKey $(Get-AzStorageAccountKey -ResourceGroupName $resourceGroupName -StorageAccountName $storageAccountName).Value[0] `  
    -StorageUri "https://$storageaccountname.blob.core.windows.net/$storageContainerName/$bacpacFilename" `  
    -Edition "Standard" `  
    -ServiceObjectiveName "S3" `  
    -AdministratorLogin "$adminSqlLogin" `  
    -AdministratorLoginPassword $(ConvertTo-SecureString -String $password -AsPlainText -Force)  

PowerShell

Please note this entire process cannot be achieved using sqlpackage, so we need to use alternative approach on the same.

We can discuss further in case more information is needed.

----------

Please don't forgot to click on accept it as answer button wherever the information provided helps you. This can be beneficial to other community members as well..

Answer 3

What should i do to get .bacpac directly to azure blob container from azure sql database?