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.
If you see below error:
*** Changes to connection setting default values were incorporated in a recent release. More information is available at https://aka.ms/dacfx-connection
*** Error exporting database:Could not connect to database server.
A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)
*** The settings for connection encryption or server certificate trust may lead to connection failure if the server is not properly configured.
Add this parameter at the end. /SourceTrustServerCertificate:True
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 our twitter handle
@CursorRun or FB page
@CursorRun
