Journal entry

Restoring a Dynamics 365 Finance & Operations database from a Tier 2 (UAT) environment to a Tier 1 (Dev) environment

KD-0152 · · Administration · 4 min read

Overview

Importing a database from a Tier 2 environment into a Tier 1 Dynamics 365 Finance & Operations environment is a common task for developers and administrators who need production-like data in their development environments. The process involves two primary phases, each consisting of several discrete steps.

In this article, I’ll walk through both phases end-to-end, including the SQL scripts and import compatibility issues, as well as the commands you’ll need along the way.

Phases of the Import Process

The process is broken into two main phases:

  • Phase 1 — Export the database from the Tier 2 environment using LCS
  • Phase 2 — Import the database into the Tier 1 (cloud-hosted) environment

Phase 1: Export the Database from the Tier 2 Environment Using LCS

The first phase focuses on exporting the database from the Tier 2 environment through Lifecycle Services (LCS). Follow the steps below to complete the export:

  1. Navigate to the Tier 2 (sandbox/UAT) Environment Details page in LCS.
  2. Select Maintain → Move database.
  3. Choose Export database and specify a name for the .bacpac file.
  4. Once the export completes, the .bacpac file will appear in your LCS project’s Asset Library under the Database backups section.
  5. Select the database backup file, then click the Generate SAS link.
  6. When the confirmation message appears, click OK, then paste the link into a new tab in your web browser to download the file.

Phase 2: Import the Database into the Tier 1 Environment

Important: Download the latest version of SqlPackage for .NET Core on Windows from Microsoft. Avoid using the older version bundled with SQL Server, as it can cause compatibility issues during import.

After exporting the database from Tier 2, the next phase is to import the .bacpac file into the Tier 1 cloud-hosted environment.

Step 1 — Copy the .bacpac file

Copy the .bacpac file to the Tier 1 environment’s hard drive.

Step 2 — Stop the services

Use services.msc or a PowerShell script to stop the following services:

  • World Wide Web Publishing Service
  • Microsoft Dynamics 365 Unified Operations: Batch Management Service
  • Management Reporter 2012 Process Service

Step 3 — Rename the existing AxDB

Open Microsoft SQL Server Management Studio and run the following script to rename the existing AxDB:

ALTER DATABASE AxDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE AxDB MODIFY NAME = AxDB_Old
GO
ALTER DATABASE AxDB_Old SET MULTI_USER;
GO

Step 4 — Run the SqlPackage import

Run Command Prompt as administrator, navigate to the folder containing SqlPackage.exe, and execute the following command (J:\MSSQL_BACKUP\UAT.bacpac is my path, adjust the .bacpac path to match your environment):

SqlPackage.exe /a:import /sf:J:\MSSQL_BACKUP\UAT.bacpac /tsn:localhost /tdn:AxDB /p:CommandTimeout=50000 /TargetTrustServerCertificate:True

Heads up: The database import will typically take several hours to complete. Plan accordingly.

Step 5 — Run the post-import SQL script

Once the SqlPackage import is complete, open SQL Server Management Studio and execute the following script on the imported database. This script will:

  • Recreate SQL users
  • Reset Azure Blob Storage references
  • Refresh Commerce full-text catalogs
  • Re-enable change tracking
  • Configure the retail channel database record
-- Recreate SQL users
CREATE USER axdeployuser FROM LOGIN axdeployuser
EXEC sp_addrolemember 'db_owner', 'axdeployuser'

CREATE USER axdbadmin FROM LOGIN axdbadmin
EXEC sp_addrolemember 'db_owner', 'axdbadmin'

CREATE USER axmrruntimeuser FROM LOGIN axmrruntimeuser
EXEC sp_addrolemember 'db_datareader', 'axmrruntimeuser'
EXEC sp_addrolemember 'db_datawriter', 'axmrruntimeuser'

CREATE USER axretaildatasyncuser FROM LOGIN axretaildatasyncuser
CREATE USER axretailruntimeuser FROM LOGIN axretailruntimeuser
CREATE USER axdeployextuser FROM LOGIN axdeployextuser

CREATE USER [NT AUTHORITY\NETWORK SERVICE]
  FROM LOGIN [NT AUTHORITY\NETWORK SERVICE]
EXEC sp_addrolemember 'db_owner', 'NT AUTHORITY\NETWORK SERVICE'

-- Reset Azure Blob Storage references to local
UPDATE T1
SET T1.storageproviderid = 0,
    T1.accessinformation = '',
    T1.modifiedby = 'Admin',
    T1.modifieddatetime = getdate()
FROM docuvalue T1
WHERE T1.storageproviderid = 1;

-- Clean up change tracking stored procedures
DROP PROCEDURE IF EXISTS SP_ConfigureTablesForChangeTracking
DROP PROCEDURE IF EXISTS SP_ConfigureTablesForChangeTracking_V2

Step 6 — Restart the services

Restart the services you stopped in Step 2 to bring the environment back online.

Wrapping Up

That’s it — your Tier 1 environment should now be running with the data from your Tier 2 (UAT) environment. This is a powerful workflow for troubleshooting production-like issues locally, validating data migrations, or testing customizations against realistic datasets.

If you found this useful, feel free to share or leave a comment with your own tips and gotchas from working through this process.