How to Restore a UAT Database to a Development Environment in D365 Finance & Operations

 How to Restore a UAT Database to a Development Environment in D365 Finance & Operations 

Restoring a UAT database to a development environment is a common activity in D365 Finance & Operations (D365 FO). It helps developers analyze issues, test fixes, and validate customizations using real data. This blog explains how to restore a UAT database to a Dev environment using a BACPAC file downloaded from LCS. 

 
 

Step 1: Download the BACPAC File from LCS 

1. Log in to Lifecycle Services (LCS). 

2. Select the required UAT environment. 

3. Navigate to the Maintain tab. 

4. In the Maintain section, go to Database. 

5. Click on the option to export or download the database. 

6. Once the export is completed, download the BACPAC file to your local machine. 

The BACPAC file contains both the database schema and data. 

 
 

Step 2: Download and Install SqlPackage 

To restore the BACPAC file, we need the SqlPackage utility. 

7. Download SqlPackage from Microsoft. 

8. Since our operating system supports Windows, select the Windows package. 

9. The downloaded file will be in ZIP format. 

10. Extract the ZIP file. 

11. Copy or note down the folder path where SqlPackage.exe is located. 

 

Running SqlPackage Command Using Command Prompt (Run as Administrator) 

Before restoring the database using SqlPackage, make sure to run the Command Prompt with administrator privileges. 

Step 1: Open Command Prompt as Administrator 

12. Click on the Start menu. 

13. Search for Command Prompt. 

14. Right-click on Command Prompt. 

15. Select Run as administrator. 

This ensures that SqlPackage has the required permissions to import the database. 

 
 

Step 2: Navigate to the SqlPackage Folder 

After opening Command Prompt as an administrator, navigate to the folder where SqlPackage.exe is located. 

Example: 

cd C:\Users\Admin6609cc02c0\Downloads\sqlpackage-win-x64-en-170.1.61.1  

Once executed, the Command Prompt path will change to the SqlPackage directory, as shown in the screenshot. 

Step 3: Restore the Database Using Command Prompt 

16. Open Command Prompt (Run as Administrator). 

17. Navigate to the folder where SqlPackage.exe is located, or use the full path in the command. 

18. Execute the following command to restore the database on your Dev machine: 

SqlPackage.exe /a:Import  
/sf:D:\ExportedBacpac\MyUATDB.bacpac  
/tsn:localhost  
/tdn:AXDB_Dev  
/p:CommandTimeout=1200  
/TargetTrustServerCertificate:true 
 

 
 

Step 4: Command Parameter Explanation 

Parameter	Description
/a:Import	Specifies the import action
/sf	Source file path (BACPAC file location)
/tsn	Target SQL Server name
/tdn	Target database name (must not already exist)
/p:CommandTimeout	Increases timeout for large databases
/TargetTrustServerCertificate	Trusts the SQL Server certificate

⚠️ Make sure the target database does not already exist, otherwise the restore will fail. 

Once the database restore is completed, our custom database name will be changed accordingly. This change is required to align the restored database with the development environment configuration.

If you want to assign the required roles, please run the below script in SSMS.

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'

UPDATE T1

SET T1.storageproviderid = 0

    , T1.accessinformation = ''

    , T1.modifiedby = 'Admin'

    , T1.modifieddatetime = getdate()

FROM docuvalue T1

WHERE T1.storageproviderid = 1 --Azure storage

DROP PROCEDURE IF EXISTS SP_ConfigureTablesForChangeTracking

DROP PROCEDURE IF EXISTS SP_ConfigureTablesForChangeTracking_V2

GO

-- Begin Refresh Retail FullText Catalogs

DECLARE @RFTXNAME NVARCHAR(MAX);

DECLARE @RFTXSQL NVARCHAR(MAX);

DECLARE retail_ftx CURSOR FOR

SELECT OBJECT_SCHEMA_NAME(object_id) + '.' + OBJECT_NAME(object_id) fullname FROM SYS.FULLTEXT_INDEXES

    WHERE FULLTEXT_CATALOG_ID = (SELECT TOP 1 FULLTEXT_CATALOG_ID FROM SYS.FULLTEXT_CATALOGS WHERE NAME = 'COMMERCEFULLTEXTCATALOG');

OPEN retail_ftx;

FETCH NEXT FROM retail_ftx INTO @RFTXNAME;

BEGIN TRY

    WHILE @@FETCH_STATUS = 0 

    BEGIN 

        PRINT 'Refreshing Full Text Index ' + @RFTXNAME;

        EXEC SP_FULLTEXT_TABLE @RFTXNAME, 'activate';

        SET @RFTXSQL = 'ALTER FULLTEXT INDEX ON ' + @RFTXNAME + ' START FULL POPULATION';

        EXEC SP_EXECUTESQL @RFTXSQL;

        FETCH NEXT FROM retail_ftx INTO @RFTXNAME;

    END

END TRY

BEGIN CATCH

    PRINT error_message()

END CATCH

CLOSE retail_ftx; 

DEALLOCATE retail_ftx; 

-- End Refresh Retail FullText Catalogs

--Begin create retail channel database record--

declare @ExpectedDatabaseName nvarchar(64) = 'Default';

declare @DefaultDataGroupRecId BIGINT;

declare @ExpectedDatabaseRecId BIGINT; 

IF NOT EXISTS (select 1 from RETAILCONNDATABASEPROFILE where NAME = @ExpectedDatabaseName)

BEGIN 

select @DefaultDataGroupRecId = RECID from RETAILCDXDATAGROUP where NAME = 'Default'; 

insert into RETAILCONNDATABASEPROFILE (DATAGROUP, NAME, CONNECTIONSTRING, DATASTORETYPE)

values (@DefaultDataGroupRecId, @ExpectedDatabaseName, NULL, 0); 

select @ExpectedDatabaseRecId = RECID from RETAILCONNDATABASEPROFILE where NAME = @ExpectedDatabaseName; 

insert into RETAILCDXDATASTORECHANNEL (CHANNEL, DATABASEPROFILE)

select RCT.RECID, @ExpectedDatabaseRecId from RETAILCHANNELTABLE RCT

inner join RETAILCHANNELTABLEEXT RCTEX on RCTEX.CHANNEL = RCT.RECID

        update RETAILCHANNELTABLEEXT set LIVECHANNELDATABASE = @ExpectedDatabaseRecId where LIVECHANNELDATABASE = 0

END; 

--End create retail channel database record

Stop the following 4 services

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

5) Rename the DB.

2 3 4 5 6

ALTER DATABASE AXDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO ALTER DATABASE AXDB MODIFY NAME = AXDB_Orig GO ALTER DATABASE AXDB_Orig SET MULTI_USER GO

6) start the below  4 services 

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