Export Database from UAT Environment and restore in the Dev Environment – Restore the database in the local Dev VM – Microsoft Dynamics 365 for Finance and Operations

Scenario : During testing we face many problems and those problem can only find by debugging the code with the actual data. Then we need to take the backup of the UAT environment and restore it to the development environment.

Solution : Below are the steps needs to perform to take the backup and restore

Export the Database:

1. Go to LCS
2. From sandbox Environment Details page, click the Maintain menu, and then select Move database.
3. Select the Export Database option
4. Database will exported as bacpac file to the Database backup option in the Asset Library

Import the Database:

1. Copy the bacpac file to the Development enviornment
2. Import as a data tier application
3. If data tier application failed then import using below commands

4. cd C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin

   SqlPackage.exe /a:import /sf:D:\Exportedbacpac\uatBackup.bacpac /tsn:localhost /tdn:SSProd /p:CommandTimeout=1200

    

   • 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.

1. After successfully database import run below commands to update the database logins details as per current environment (at the end of the article) (No need to run this command if the current environment is local dev vm not Microsoft managed)
2. Run the retrovision tool to use the new database
3. Stop the services
   1. World wide web publishing service
   2. Batch Management service
   3. Management reporter service
4. rename the database as AxDB after stopping the service and rename the old one as AxDBOld

   1. REATE 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 Catalog