This blog post is for my notes, you can find more details on Microsoft link.
There are some extra steps in the Microsoft link, which I never used. I usually follow only these steps and it works for me. So Instead I need to search on Microsoft link. I wrote this post for my future use.
Step 1: You have to restore production database to UAT and then from uat to export it to Asset library
Step 2: Download Backpac file in specific location
Step 3: Open Command prompt with run as administrator for locally hosted VM and go to following location
Cd C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin
Step 4: run the following command
SqlPackage.exe /a:import /sf:D:\Exportedbacpac\my.bacpac /tsn:localhost /tdn:<target database name> /p:CommandTimeout=1200
I modified the above statement as
SqlPackage.exe /a:import /sf:C:\Temp\hsauatbackup.bacpac /tsn:localhost /tdn:AxDb_6 /p:CommandTimeout=1200
Step 5: run the following command.
After the database is restored. Run the following command in SQL Server management
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;
Delete the AXDB. Create backup.
Rename current database to AXDB