DB restore from PROD to UAT/Sandbox and UAT/Sandbox to Dev box

  DB restore from PROD to Sandbox and Sandbox to Dev box

  1. PROD to UAT DB restore: 

     Follow following link to restore DB from PROD to Sandbox 

     https://parashuramd365fo.blogspot.com/2023/06/refresh-database-from-production.html

  2. UAT/Sandbox to Dev box DB restore:

     2.1. Export DB from LCS to the Asset Library

          Login LCS - Sand box environment full details page, use Maintain > move database > Select export and start  

2.2. This will export DB and stored on asset library. Navigate to the Asset Library > Database Backup >

Select uploaded exported backup and save to local folder (Let's supposed save into D://DBBackup/)

2.3. Prepare for DB import

a. Log into the target environment using a RDC

b. Download the latest SQL package.exe with the Windows .Net Core zip package

Use following link to download and install SQL package

https://docs.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download?view=sql-server-ver15

c. Copy the downloaded SQL package and extract on - backup stored location (D://DBBackup/) and

install on (D://DBBackup/) location

d. Open the command prompt and run following command

> sqlpackage

e. Execute following script on command prompt to start DB restore 

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

Sample:

SqlPackage.exe /a:import /sf:D:\<Name of bacpac file created on Source>.bacpac /tsn:localhost /tdn:<target database name> /TargetTrustServerCertificate:True /p:CommandTimeout=0

Example:

SqlPackage.exe /a:import /sf:G:\uatbackup.bacpac /tsn:localhost /tdn:UATBackup_05_29_2023 /TargetTrustServerCertificate:True  /p:CommandTimeout=0

The above script create new DB  "UATBackup_05_29_2023" in SQL and that would take some hours to execute script.

* If you get following error while executing the script.

'SqlPackage.exe' is not recognized as an internal or external command,

operable program or batch file.

Follow the following steps to solve issue:

Download package sqlpackage-win7-x64-en-16.1.8089.0 in your VM

Extract and placed downloaded folder to your stored backup file location

Copy "sqlpackage" from folder and place on backup file location. Run the "sqlpackage"

Now the final updated as script:

f. Once Above script execute successfully, login SQL >> rename AxDB to some other name >> rename

newly restored DB "UATBackup_05_29_2023" to AxDB

g. Execute following script to add role in restored DB

Script:

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 Catalog

h. Open VS and run DB sync