Copy production database to DEV environment in D365FO

Database movements are activities that do occur during the lifecycle of Finance and Operation project.

There are different database movement scenarios as the need arises. Please check Microsoft article

In the post, I will take you through a step-by-step on how to Copy production database to DEV environment in D365FO.

LCS(Life cycle service) is the entry point of every D365FO project and we can perform almost all actions on our environments on LCS like, Refresh, Export, and Import database. However, Microsoft does not allow us to move a Production database directly to the DEV environment.  We can achieve that with the steps below:

Step 1. Take a backup of the Live database to test. (Database refresh)

This can be done on LCS portal by doing a database refresh.

Step 2. Export the refreshed data base to Asset library on LCS

Step3. Download the database from LCS to the Development environment.

To download, click on the database file on LCS and voila.

Note that the database is saved on the asset library as a .bacpac file. We are import this file using powershell and Sqlpackage

Step4. Get the Sqlpackage

Download here

It is advisable to have both the database file and the sqlpackage file in the same directory. Before importing, rename your current AxDB to AxDbold with the following SQL script in Management studio.

Then create a new database called AxDB.

Step5. Import the .bacpac database.

.\SqlPackage.exe /a:import /sf:D:\Exportedbacpac\my.bacpac /tsn:localhost /tdn:<target database name> /p:CommandTimeout=1200

Here is an explanation of the parameters:

• tsn (target server name) – The name of the Microsoft SQL Server instance 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.

Below my result :

.\SqlPackage.exe /a:import /sf:C:\testbackup.bacpac /tsn:localhost /tdn:axdb /p:CommandTimeout=1200

Step6. Update the databse

Run the following script against your database to add the users you deleted while creating .bacpac file.

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
EXEC sp_addrolemember 'DataSyncUsersRole', 'axretaildatasyncuser'

CREATE USER axretailruntimeuser FROM LOGIN axretailruntimeuser
EXEC sp_addrolemember 'UsersRole', 'axretailruntimeuser'
EXEC sp_addrolemember 'ReportUsersRole', 'axretailruntimeuser'

CREATE USER axdeployextuser WITH PASSWORD = '<password from LCS>'
EXEC sp_addrolemember 'DeployExtensibilityRole', '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


ALTER DATABASE [<your AX database name>] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 6 DAYS, AUTO_CLEANUP = ON)
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.