Copy Finance and Operations databases from Sandbox UAT to Dev or SQL Server on premise

Copy Finance and Operations databases from Sandbox UAT to Dev or SQL Server on premise

Example FB project

Access Sandbox SQL Instant

Go to LCS / Sandbox UAT environment

Remote to BI server

 

Run SSMS and connect to SQL UAT instant

 

 

Server name : fb-uat-appdbserver-cfe4084e.database.windows.net

 

 

Create a copy of the source database

 

Prepare the database

 

--Prepare a database in Azure SQL Database for export to SQL Server.

--Disable change tracking on tables where it is enabled.

declare

@SQL varchar(1000)

set quoted_identifier off

declare changeTrackingCursor CURSOR for

select 'ALTER TABLE [' + t.name + '] DISABLE CHANGE_TRACKING'

from sys.change_tracking_tables c, sys.tables t

where t.object_id = c.object_id

OPEN changeTrackingCursor

FETCH changeTrackingCursor into @SQL

WHILE @@Fetch_Status = 0

BEGIN

exec(@SQL)

FETCH changeTrackingCursor into @SQL

END

CLOSE changeTrackingCursor

DEALLOCATE changeTrackingCursor

--Disable change tracking on the database itself.

ALTER DATABASE

-- SET THE NAME OF YOUR DATABASE BELOW

MyNewCopy

set CHANGE_TRACKING = OFF

--Remove the database level users from the database

--these will be recreated after importing in SQL Server.

declare

@userSQL varchar(1000)

set quoted_identifier off

declare userCursor CURSOR for

select 'DROP USER [' + name + ']'

from sys.sysusers

where issqlrole = 0 and hasdbaccess = 1 and name <> 'dbo'

OPEN userCursor

FETCH userCursor into @userSQL

WHILE @@Fetch_Status = 0

BEGIN

exec(@userSQL)

FETCH userCursor into @userSQL

END

CLOSE userCursor

DEALLOCATE userCursor

--Delete the SYSSQLRESOURCESTATSVIEW view as it has an Azure-specific definition in it.

--We will run db synch later to recreate the correct view for SQL Server.

if(1=(select 1 from sys.views where name = 'SYSSQLRESOURCESTATSVIEW'))

DROP VIEW SYSSQLRESOURCESTATSVIEW

--Next, set system parameters ready for being a SQL Server Database.

update sysglobalconfiguration

set value = 'SQLSERVER'

where name = 'BACKENDDB'

update sysglobalconfiguration

set value = 0

where name = 'TEMPTABLEINAXDB'

--Clean up the batch server configuration, server sessions, and printers from the previous environment.

TRUNCATE TABLE SYSSERVERCONFIG

TRUNCATE TABLE SYSSERVERSESSIONS

TRUNCATE TABLE SYSCORPNETPRINTERS

TRUNCATE TABLE SYSCLIENTSESSIONS

TRUNCATE TABLE BATCHSERVERCONFIG

TRUNCATE TABLE BATCHSERVERGROUP

--Remove records which could lead to accidentally sending an email externally.

UPDATE SysEmailParameters

SET SMTPRELAYSERVERNAME = '', MAILERNONINTERACTIVE = 'SMTP' --LANE.SWENKA 9/12/18 Forcing SMTP as Exchange provider can still email on refresh

--Remove encrypted SMTP Password record(s)

TRUNCATE TABLE SYSEMAILSMTPPASSWORD

GO

UPDATE LogisticsElectronicAddress

SET LOCATOR = ''

WHERE Locator LIKE '%@%'

GO

TRUNCATE TABLE PrintMgmtSettings

TRUNCATE TABLE PrintMgmtDocInstance

--Set any waiting, executing, ready, or canceling batches to withhold.

UPDATE BatchJob

SET STATUS = 0

WHERE STATUS IN (1,2,5,7)

GO

-- Clear encrypted hardware profile merchand properties

update dbo.RETAILHARDWAREPROFILE set SECUREMERCHANTPROPERTIES = null where SECUREMERCHANTPROPERTIES is not null

 

 

Export the database

Open a Command Prompt window and run the following commands.

.\SqlPackage.exe /a:export /ssn:fb-uat-appdbserver-cfe4084e.database.windows.net /sdn:fb-uat-03102018 /tf:j:\Exportedbacpac\fb-uat-03102018.bacpac /p:CommandTimeout=1200 /p:VerifyFullTextDocumentTypesSupported=false /sp:0Lo*eDkjZ**Og* /su:axdbadmin

Waiting around 2 hours to complete and then copy to Sandbox SQL Dev & Test

Import database

Copy bacpac file to new SQL instant

Right click database, select import data tier

Keep current name, and data file path settings

Waiting ~ 2 hours to complete import

 

Rename database to the correct database name on your environment

 

 

Stop MR, BATCH, IIS and DMF services

 

 

 

Update database

 

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

ALTER DATABASE [<your AX database name>] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 6 DAYS, AUTO_CLEANUP = ON)

GO

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

 

 

Full build -> fix issues if have

Restart IIS, Batch, DMF and MR services