Database refresh from PROD to UAT and Export DB as BACPAC file in Dynamics D365

Hi,

Database refresh from PROD to UAT 

Login to LCS --> Support --> Service Requests --> Add --> Database refresh request --> select Source as PROD and destination as UAT and Submit request to MicroSoft. MS will restore data and update you.

Fill-in the form :

The downtime is relative to UAT : your Production environment should not be affected. For safety, make sure to select times outside of intensive business hours.

One of the additional steps that Microsoft executes is disabling all the users except for the admin user. After the restore all user will not be able to login. created the SQL instruction below to enable all the required users with admin rights. REFERENCE

update userinfo set enable = 1

where id in (select User_ from SECURITYUSERROLE

where SECURITYROLE in (select recid from SECURITYROLE

where SECURITYROLE.aotname = '-SYSADMIN-' ))

 Export DB as BACPAC file in Dynamics D365

Getting access to your TEST environment (via mstsc with the port number)

Go to LCS and view your deployment. Here you will find your database account.
Please note the sever\Database connection string. You will need this info. 

Connect database in UAT

Start the SQL server management studio and complete the connection string by adding database.windows.net

Sqlserver-yourinfo.database.windows.net

Use the sqladmin account to log in.

Create Copy of Database

CREATE DATABASE PRODCopy20180424 AS COPY OF [uat_axdb_297977ERTEe3]

Put square bracket of database name and run above command

Prepare the database

Run the following script against the copy of the database to turn off change tracking, and to remove SQL Database users and a system view. The script also corrects system flags, removes references to the previous environment, withholds batches, and removes email configuration

Alter Database using copied database PRODCopy20180424

Open new query window and select db as PRODCopy20180424 and run below command

--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

[PRODCopy20180424]

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

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

UPDATE SysEmailParameters

SET SMTPRELAYSERVERNAME = ''

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

Export the database

Go to Command Prompt and run below command (find the 130 or 140 in your environment)

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

SqlPackage.exe /a:export /ssn:-dbservername.database.windows.net /sdn:[PRODCopy20180424] /tf:F:\Exportedbacpac\PROD20180424.bacpac /p:CommandTimeout=1200 /p:VerifyFullTextDocumentTypesSupported=false /sp:hcy=sqlpassword = /su:sqladmin

…don’t despair and do something else with your life : this command is extremely long (I had enough time to write this post while it ran…). Expect at least 1 hour wait.

Reference

Reference

Reference

Reference