Recently I ran into an error when trying to copy a small database bacpac into an Azure Managed Instance. It’s said that SSMS is able to do this task. Let’s see it in action.
How can you import a bacpac to an Azure Managed Instance? Simply connect to the Managed Instance using the newest build of SSMS (SQL Server Management Studio). Next, right-click Databases and choose Import Data-tier Application..
Next follow the import wizard. Oh dear! Looks like we can’t because the wizard fails to load.
A quick googling on Bing pointed to some forum posts that suggest adding registry keys to fix this issue. Not having time for troubleshooting I decided to go with ‘ole reliable… the command line.
SQLPACKAGE.EXE is a command line utility that can be used to export and import Data-tier Applications. To learn more about SQLPACKAGE check out this reference:
https://docs.microsoft.com/en-us/sql/tools/sqlpackage
I’ve found it quite useful to create a batch file (.bat) when importing many bacpac files because you can run it and then check the results later.
When using SQLPACKAGE to import your command should include /a:import /tcs: /sf. The reference above gives a lot of examples for importing and exporting.
Take special note that when importing to Azure SQL Database you’ll want to validate the database configuration to be sure it’s sized appropriately. For example, using /p:DatabaseMaximumSize=150 will result in a premium SKU with a database of 150 GB. Please don’t forget this because Azure SQL DB can be costly.
It’s worth noting that you can import bacpac files using the Azure portal but only for Azure SQL Database and in my experience the Azure portal import often fails with larger database bacpac files and can take hours before the import starts. With that said I generally use SQLPACKAGE instead.