Backup and restore in another ENV Dynamics 365 Finance and operations

1.       Take the AxDB database backup

2.       Do the Full Build.

3.       Do the Database Synchronize 

4.       Connect the LCS and download the Database 

5.       Create the new Database with Name : UATBackUp81.

6.       Restore the downloaded Database from LCS to UATBackUp81.

7.       Need to change the UATBackUp81

a.        Security User details

Script:

CREATE USER axdeployuser FROM LOGIN axdeployuser

EXEC sp_addrolemember 'db_owner', 'axdeployuser'

CREATE USER axdeployextuser WITH PASSWORD = '<password from LCS>'

IF EXISTS (select * from sys.database_principals where type = 'R' and name = 'DeployExtensibilityRole')

BEGIN

    EXEC sp_addrolemember 'DeployExtensibilityRole', 'axdeployextuser'

END

CREATE USER axdbadmin WITH PASSWORD = '<password from LCS>'

EXEC sp_addrolemember 'db_owner', 'axdbadmin'

CREATE USER axruntimeuser WITH PASSWORD = '<password from LCS>'

EXEC sp_addrolemember 'db_datareader', 'axruntimeuser'

EXEC sp_addrolemember 'db_datawriter', 'axruntimeuser'

CREATE USER axmrruntimeuser WITH PASSWORD = '<password from LCS>'

EXEC sp_addrolemember 'ReportingIntegrationUser', 'axmrruntimeuser'

EXEC sp_addrolemember 'db_datareader', 'axmrruntimeuser'

EXEC sp_addrolemember 'db_datawriter', 'axmrruntimeuser'

CREATE USER axretailruntimeuser WITH PASSWORD = '<password from LCS>'

EXEC sp_addrolemember 'UsersRole', 'axretailruntimeuser'

EXEC sp_addrolemember 'ReportUsersRole', 'axretailruntimeuser'

CREATE USER axretaildatasyncuser WITH PASSWORD = '<password from LCS>'

EXEC sp_addrolemember 'DataSyncUsersRole', 'axretaildatasyncuser'

ALTER DATABASE SCOPED CONFIGURATION  SET MAXDOP=2

ALTER DATABASE SCOPED CONFIGURATION  SET LEGACY_CARDINALITY_ESTIMATION=ON

ALTER DATABASE SCOPED CONFIGURATION  SET PARAMETER_SNIFFING= ON

ALTER DATABASE SCOPED CONFIGURATION  SET QUERY_OPTIMIZER_HOTFIXES=OFF

ALTER DATABASE <imported database name> SET COMPATIBILITY_LEVEL = 130;

ALTER DATABASE <imported database name> SET QUERY_STORE = ON;

update [dbo].[SYSSERVICECONFIGURATIONSETTING]

set value ='<tenant ID from existing database>'

where name = 'TENANTID'

update dbo.POWERBICONFIG

set TENANTID = '<tenant ID from existing database>'

update dbo.PROVISIONINGMESSAGETABLE

set TENANTID = '<tenant ID from existing database>'

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

8.       Changes the names like AxDB to AxDB_standard ,  UATBackUp81 to AXDB

USE master;

GO 

ALTER DATABASE AxDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

ALTER DATABASE AxDB MODIFY NAME = AxDB_Standard ;

GO 

ALTER DATABASE AxDB_ Standard SET MULTI_USER

GO

---------------------------------------------------------------------------

USE master;

GO 

ALTER DATABASE UATBackUp81 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

ALTER DATABASE UATBackUp81 MODIFY NAME = AxDB ;

GO 

ALTER DATABASE AxDB SET MULTI_USER

GO

9.       Do the Full DB Synchronize