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:
- Go to LCS
- From sandbox Environment Details page, click the Maintain menu, and then select Move database.
- Select the Export Database option
- Database will exported as bacpac file to the Database backup option in the Asset Library
Import the Database:
- Copy the bacpac file to the Development enviornment
- Import as a data tier application
- If data tier application failed then import using below commands
-
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.
- 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)
- Run the retrovision tool to use the new database
- Stop the services
- World wide web publishing service
- Batch Management service
- Management reporter service
- rename the database as AxDB after stopping the service and rename the old one as AxDBOld
-
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
-