question

ShubhiJain-5090 avatar image
0 Votes"
ShubhiJain-5090 asked Sumarigo-MSFT edited

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

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.

azure-sql-databaseazure-sql-virtual-machines
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AnuragSharma-MSFT avatar image
0 Votes"
AnuragSharma-MSFT answered ShubhiJain-5090 commented

Hi @ShubhiJain-5090, 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'


· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @AnuragSharma-MSFT
Thanks for the response
But after this, while importing the bacpac file using sqlpackage i can't take the source a storage account for that what should I do. Is there any way that I can do this without using any GUI i.e I could do it from PowerShell or cmd?

0 Votes 0 ·

Hi @ShubhiJain-5090, thanks for your reply.

Do you want to import it from Azure Storage Account to on-premise SQL Server Database or Azure SQL Database?

0 Votes 0 ·

Hello @AnuragSharma-MSFT thanks for the reply
I want to import it to Azure SQL Database from the Azure storage account.
and the export should be from on-premises SQL server to azure blob storage.

0 Votes 0 ·
AnuragSharma-MSFT avatar image
0 Votes"
AnuragSharma-MSFT answered

Hi @ShubhiJain-5090, 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 131825-image.png wherever the information provided helps you. This can be beneficial to other community members as well..



image.png (8.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.