Step-by-Step Guide to Restoring Production Database in D365 FO Development

To have an up-to-date refresh DB in your Dev box or to debug some issues that are happening in the production only, keep reading my article.

To refresh your Dev box Database, follow the below steps:

Log in to LCS https://lcs.dynamics.com/

Click on the project that you are working on.

From the right of the screen, click on the full details of the Test environment that you will use to complete this process (you can't refresh Development's DB directly from production; we should refresh Test first).

From the Test full details page, click on Maintain, and choose the Move database option as shown below:

Select Refresh database from select the change type list, then a new page will open to choose the source environment (select the production environment).

Based on MS "Copying production data during business hours or peak hours could have an impact on the production system. It's highly recommended to do the refresh database operation during off-peak hours and limit only one refresh operation at a time".

After the refresh completion on Test, you will have a copy from production on your Test environment.

Now you can you take an export backup from Test DB on the .bacpac extension.

From the Test full details page, click on Maintain, and choose the Move database option again, then select Export database option.

Export database page will be open, click on the check box and click submit button.

The export process will be started, and the test environment will be down until the export has finished.

Once Export is done, the backup will be saved on the asset library.

From the Burger icon select asset library.

From the Asset library, Click on Database backup, click on the latest backup, then the download of the backup will start.

Open your Dev box.

Copy the downloaded bacpac file to your Dev box C drive.

In the Dev box download SqlPackage .

Unzip SqlPackage file into C Drive , C:\SQLTool.

Close Visual studio and stop the following 4 services:

• World Wide Web Publishing Service

• Microsoft Dynamics 365 Unified Operations: Batch Management Service

• Microsoft Dynamics 365 Unified Operations: DMF Service

• Management Reporter 2012 Process Service

Take the existing AxDB as backup on different drive.

Rename AxDB to AxDB_origin05242024 using the following script

ALTER DATABASE AxDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

ALTER DATABASE AxDB MODIFY NAME = AxDB_origin05242024 
GO

ALTER DATABASE AxDB_origin2024 SET MULTI_USER;
GO

Open CMD as Administrator, and change the directory to C:\SQLTool (Where you unzipped the SqlPackage).

Run the below command based on your values:

• 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(bacpac file).

SqlPackage.exe /a:import /sf:"C:\Users\Admin68bddd1058\Downloads\UAT\ImportDB.bacpac" /tsn:NAG-NEWDEV02-1 /tdn:AxDB /p:CommandTimeout=200000 /TargetEncryptConnection:False /mfp:"Model.xml"Note Maybe you will face the following error:

*** Error importing database:Could not import package.

*Error SQL72014: Core Microsoft SqlClient Data Provider: Msg 4630, Level 16, State 1, Line 1 The permission 'KILL DATABASE CONNECTION' is not supported in this version of SQL Server. Alternatively, use the server level 'ALTER ANY CONNECTION' permission.

Error SQL72045: Script execution error. The executed script:

GRANT KILL DATABASE CONNECTION TO [ms_db_configreader];*

Just in case you faced it do the following steps:

1. Copy bacpac file to another location, and change it to .zip by changing its extension.

2. Open .zip folder and copy the model.xml file only to some other location.

3. Delete .zip folder.

4. Open the model.xml file and delete the following two elements.

5.    <Element Type="SqlPermissionStatement" Name="[Grant.KillDatabaseConnection.Database].[ms_db_configreader].[dbo]">
      <Property Name="Permission" Value="1114" />
      <Relationship Name="Grantee">
      <Entry>
      <References Name="[ms_db_configreader]" />
      </Entry>
      </Relationship>
      <Relationship Name="SecuredObject">
      <Entry>
      <References Disambiguator="1" />
      </Entry>
      </Relationship>
      </Element>
      <Element Type="SqlPermissionStatement" Name="[Grant.KillDatabaseConnection.Database].[ms_db_configwriter].[dbo]">
      <Property Name="Permission" Value="1114" />
      <Relationship Name="Grantee">
      <Entry>
      <References Name="[ms_db_configwriter]" />
      </Entry>
      </Relationship>
      <Relationship Name="SecuredObject">
      <Entry>
      <References Disambiguator="1" />
      </Entry>
      </Relationship>
      </Element>
   

6. Save the file.

7. Update CMD command to include the model.xml file by adding /mfp: model.xml path

8. The Updated CMD command will be:

SqlPackage.exe /a:import /sf:C:\ImportDB.bacpac /tsn:devxxxxxx /tdn:AxDB /p:CommandTimeout=200000 /TargetEncryptConnection:False /mfp:D:\model.xml

It will take time until the import is done

Once the import is completed, open SSMS and run the following SQL script in AxDB.

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

Start the 4 services that you stopped.

Open Visual Studio, Go to Dynamics 365 menu --> Model Management --> Refresh Models.

Go to Dynamics 365 -->Build Models, Select ApplicationSuite model and check build reference packages.

Once build is successfully done, synchronize the database.

After the Build and Synchronization are competed, you can start working as usual with a refreshed database.