Copy database from D365FO On premises to Cloud hosted environment

We can move database from On premises environment to a cloud hosted environment for testing purposes. This process is quite seamless as both environment are SQL severs. Hence, we backup from A and restore on B.

 

A. Take a back up of AXDB on the On-premises environment

B. Restore the database on the Cloud hosted environment(CHE).

C. Run some sql script against the CHE to update certain values.

D. Synchronize the CHE database.

 

I would skip the steps A and B and move straight to the scripts.

Step 1

USE AXDBonprem;
SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('axdbadmin');

Step 2


ALTER AUTHORIZATION ON SCHEMA::db_owner TO dbo;
ALTER AUTHORIZATION ON SCHEMA::db_ddladmin TO dbo;
ALTER AUTHORIZATION ON SCHEMA::db_datareader TO dbo;
ALTER AUTHORIZATION ON SCHEMA::db_datawriter TO dbo;


step3

USE AXDBonprem;
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' and name != 'NT AUTHORITY\NETWORK SERVICE'
OPEN userCursor
FETCH userCursor into @userSQL
WHILE @@Fetch_Status = 0
BEGIN
exec(@userSQL)
FETCH userCursor into @userSQL
END
CLOSE userCursor
DEALLOCATE userCursor

step4

–now recreate the users copying from the existing database:
use AXDB –******************* SET THE OLD TIER 1 DATABASE NAME****************************

go
IF object_id('tempdb..#UsersToCreate') is not null
DROP TABLE #UsersToCreate
go
select 'CREATE USER [' + name + '] FROM LOGIN [' + name + '] EXEC sp_addrolemember "db_owner", "' + name + '"' as sqlcommand
into #UsersToCreate
from sys.sysusers
where issqlrole = 0 and hasdbaccess = 1 and name != 'dbo' and name != 'NT AUTHORITY\NETWORK SERVICE'


step 5


go
use AXDBonprem --******************* SET THE NEWLY RESTORED DATABASE NAME****************************
go
declare
@userSQL varchar(1000)
set quoted_identifier off
declare userCursor CURSOR for
select sqlcommand from #UsersToCreate
OPEN userCursor
FETCH userCursor into @userSQL
WHILE @@Fetch_Status = 0
BEGIN
exec(@userSQL)
FETCH userCursor into @userSQL
END
CLOSE userCursor
DEALLOCATE userCursor


STEP 6
COPY ADMIN USER

use AXDBonprem
--Update the Admin user record, so that I can log in again
UPDATE USERINFO
SET SID = x.SID, NETWORKDOMAIN = x.NETWORKDOMAIN, NETWORKALIAS = x.NETWORKALIAS,
IDENTITYPROVIDER = x.IDENTITYPROVIDER
FROM AXDB..USERINFO x --******************* SET THE OLD TIER 1 DATABASE NAME****************************
WHERE x.ID = 'Admin' and USERINFO.ID = 'Admin'


STEP 7

Now, let’s rename the orginal AXDB to AXDB_old

USE master
GO
ALTER DATABASE AxDB
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE AxDB
Modify Name = AxDBold
GO
ALTER DATABASE AxDBold
SET MULTI_USER
GO

Step 8

Rename AXDBonprem to AXDB


USE master
GO
ALTER DATABASE AxDBonprem
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE AxDBonprem
Modify Name = AxDB
GO
ALTER DATABASE AxDB
SET MULTI_USER
GO