Tag: database

Step 1: Turn off the below services –
a. World Wide Web Publishing Service
b. Management Reporter 2012 Process Service
c. Microsoft Dynamics 365 Unified Operations: Batch Management Service

Step 2: Rename Existing AxDB using below SQL query –

USE master; 

GO 

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

Step 3: Run command prompt as administrator and run below commands one after another –

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

SqlPackage.exe /a:import /sf:J:\UATDbBackup\UATBackup_27Apr\AXDB.bacpac /tsn:localhost /tdn:AxDB_New /p:CommandTimeout=1200

In 2nd command “sf” specifies a path where bacpac file has been stored and “tdn” specifies the name of new database that is going to be imported.
Wait until the command completes successfully. It takes approximate 2-3 hours to complete the import operation.

Step 4: Once command mentioned in Step 3 runs successfully, you can navigate to SQL server and rename the newly imported database using below SQL query –

USE master; 

GO 

ALTER DATABASE AxDB_New SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

ALTER DATABASE AxDB_New MODIFY NAME = AxDB ;

GO 

ALTER DATABASE AxDB SET MULTI_USER

GO

Step 5: Once imported database is renamed as AxDB, you need to run below SQL Query against the same –

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 FROM LOGIN axdeployextuser
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

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

Step 6: Once you execute above Query, Follow below Query Execution one after another –

ALTER DATABASE [AxDB] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 6 DAYS, AUTO_CLEANUP = ON);

update DMFDataSource<br>set ENTITYSTORECONNECTIONSTRING = ''<br>where  ENTITYSTORECONNECTIONSTRING <> ''<br>

truncate table IntegrationActivityTable

Step 7: Start all services that are stopped in Step 1.

Step 8: Open Visual Studio and Synchronize database using menu under Dynamics 365

That’s it !!!

To Export the database from UAT to Asset Library, you can refer Microsoft docs link given below,

https://docs.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/database/export-database

Thank you!