Importing and Restoring production database to DEV environment in D365FO using Bacpac File in D365 Finance and Operations

Copying a production database to a development environment is a common requirement for Dynamics 365 Finance and Operations (D365FO) developers. The process can be complex and requires careful planning and execution. In this blog, we will discuss how to copy a production database to a DEV environment using a .bacpac database file step by step.

Go to Production /UAT Machine from where you want to take backup using LCS

Download .bacpac file from the production database

Go to LCS enviromert Asset library select Database backup tab and click on database you exported from production it will download into you PC in .bacpac format.Keep bacpac file in any drive

Rename existing db in SQL management studio

Suppose you have an AxDB database that you want to import. In this case, you need to change the name of the database to something like AxDB_old, using the below query executed in master.

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

Get sql.net tool install it

Download latest sql.net package because sometimes existing SQL package will throw an error that database bacpac file is courrpted, so to avoid such scenarios download and install latest package from the following link

https://docs.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download?view=sql-server-ver15

When the download is completed, extract and keep it in any drive

Note : We should have backpac file and Sql package avilable to proceed further .In below command

Step 1: I kept the extracted sqlpackage folder in the C drive's Downloads folder. So, my directory looks like this: C:\Users\Admin6680303d7e\Downloads\sqlpackage-win7-x64-en-US-15.0.4897.1.

Step 2: I executed the sqlpackage

Import bacpack file using Command rompt

Run command prompt as an administrator and go to sql package directory

Note:: Before running scrips close Visual studio and stop following services

Now this is the command we want to execute on sqlpackage directory

SqlPackage.exe /a:import /sf:C:\MyProdDB-UATbackup.bacpac /tsn:localhost /tdn:AxDB /p:CommandTimeout=20000 /TargetEncryptConnection:False

where,

tsn (target server name) – The name of the SQL Server to import into.
tdn (target database name) – The name of the database to import into. The database should not already exist.
sf (source file) – The path and name of the file to import from.
/p:CommandTimeout=20000 seconds aproximately 5 hours timeout for command
/TargetEncryptConnectionLFalse this is the updated script which will keep us way from certificate errors

Note:: If you face AXDB_primary.mdf is already existing on any location then stop Sql service and need to move that mdf file and log file to another location then start sql service and try again.

Once you start the process, observe the running process for 5 minutes. This will allow you to re-run the process in case of any errors. However, please note that the whole importing process may take approximately 5 hours to complete, depending on the size of your database (which is about 6 GB) .

Once it will completed the import we need to update the AxDB created by using script suggested by microsoft here is the link : https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/database/dbmovement-scenario-exportuat#update-the-database

Update the database (run the script on AxDB database)

CREATE USER axdeployuser FROM LOGIN axdeployuse
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 recordr

Script is suggested by Microsoft which will retain all the rights from previous database.

5. Stop following services

a. Management Reporter 2012 process Service

b. Microsoft Dynamics 365 Unified Operations: Batch Management Service

c. World Wide Web Publishing Service

6. Rename DB

a. AxDB to AxDB_Ori

b. AxDB_DDMMYYYY (New Restored DB) to AxDB

Use Script

USE master;
GO
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

USE master;
GO
ALTER DATABASE <Restore DB name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE <Restore DB name> MODIFY NAME = AxDB ;
GO
ALTER DATABASE AxDB SET MULTI_USER
GO

7. Start following services

a. Management Reporter 2012 process Service

b. Microsoft Dynamics 365 Unified Operations: Batch Management Service

c. World Wide Web Publishing Service

d. Start IIS website from IIS manager

Build Model and Sync DB

Open DEV URL and check once

Dynamix Spot - D365 FnO | Ax | X++