Azure SQL database export tip

Author's note: This blog post is inspired from D365-FinOps implementation experience but applies to the wider SQL Azure community.

In some of the environment topologies you might use in the implementation life-cycle of Dynamics 365 Finance and Operations you might need to manually export data from an Azure SQL database in one environment to another database server in another environment. The steps to be executed in this process are well detailed out in the following links on the Microsoft docs portal:

https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/database/copy-database-from-azure-sql-to-sql-server

https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/database/copy-database-from-sql-server-to-azure-sql

First thing to note is that the Azure SQL -> to SQL server documentation has been very recently updated to reflect the fact that you can now export the Azure SQL database into your LCS asset library, which is very very cool.

However, if you are migrating data from an older platform update and you do not have that option is not available and thus you have to do it manually, the old school way to generate the .bacpac with a command line script such as “SqlPackage.exe /a:export….”

The snag is that, in some environments, when exporting the Azure database using command line, the process seems to freeze in the “Resolving references in schema model” step even if you leave it hanging for a very long time (12 hours or more for a not-that-big database).

The workaround for the above situation (if you ever encounter it) is to use SQL Server management studio and trigger the export from there. The screenshot below shows the menu path to use to trigger the same job being done by the command line above but this time using the SSMS client. In this manner the database bacpac export usually executes in just a few hours.

This tip does not only apply only to Dynamics 365 Finance and Operations environments of course but is applicable to all SQL Azure databases. Actually this article will soon become “no longer applicable” for the Dynamics 365 Finance and Operations community, once everyone is on the latest platform updates. For the rest of you folks out there, this remains applicable of course.

I hope this helps and till next time.