Dynamics 365 finance database import export operations – bacpac – SqlPackage.exe

Applies to:
  • Dynamics 365 Finance
  • Dynamics 365 Supply Chain Management
  • Dynamics 365 Commerce
This is a quick post on how to run Dynamics 365 Sandbox backup/restore operations that involve moving bacpac files. We are specifically going to check on SqlPackage command.
Sometimes we want to export sandbox database and/or import the exported sql bacpac file to a non-sandbox environment. For example; export the database backup from gold environment and import it into UAT or conversion environment etc.

How to import or export a SQL bacpac file from Dynamics 365

Below are some of the steps and commands to accomplish the same.

Export the database

Backup sandbox database to LCS Asset Library

On sandbox (tier 2) environments, application uses Azure SQL Database. You can use LifeCycle Services (LCS) to export the copy of a sandbox UAT environment and the process will save it into the asset library.

Backup non-sandbox database to LCS Asset Library

On non-sandbox environments, application uses local SQL Server. You can use command prompt to export database backup as a bacpac file.

Create a copy of database

You should create a copy of the database by using backup and restore options from Microsoft SQL Server Management Studio (SSMS) or using below t-sql commands that will generate a new database. We create copy of the database so that we can work on it instead of the actual source database, as we will be deleting database users from this database before creating the bacpac file.

Prepare the database

Run the following script against the newly created database “AxDB_CopyForExport”.

Export the database as bacpac file

Open a Command Prompt window, and run the following commands.
Once you have the bacpac file, upload it to LCS Asset Library section.

Import the database

Restore to sandbox (tier 2) environment

You can restore the bacpac file from the LCS using database movement options.

Restore to non-sandbox environment

You can use the below command to import the bacpac file as a new a database.
We will not overwrite the existing database but will import it as a new database AxDb_fromUAT.

Update the database

You need to run the below script to add the database users back that we deleted earlier while exporting.

Turn on change tracking

Use the new database

To switch database, from old to the new database, first stop the following services:
  • World Wide Web Publishing Service
  • Microsoft Dynamics 365 Unified Operations: Batch Management Service
  • Management Reporter 2012 Process Service
After these services have been stopped, rename the AxDB database as AxDB_orig, rename your newly imported database to AxDB, and then restart the three services.
That’s all for database export/import for today.
If you liked the story and want to see more of this kind of content, please follow us on twitter @CursorRun or FB @CursorRun
Default image
Rahul Sharma
Rahul is a technology enthusiast, solutions architect, trainer, and blogger, working on various Microsoft and open source solutions with more than 18 years of industry experience. He specially takes interest in designing enterprise applications, cloud integrations, IoT, and other architecture rich business solutions.

Leave a Reply

%d bloggers like this: