How to copy a database from cloud tier 1 to on-premises in Dynamics 365 for Finance and Operations

In this post I'm going to explain how to copy a Dynamics 365 for Finance and Operations database from a cloud Tier 1 environment (also known as a onebox, or demo environment) to an on-premises environment. This might be useful if you're using a Tier 1 to create your golden configuration environment, which you'll use to seed the on-premises environments later.

I will post how to move a database in the other direction soon.

This process is relatively simple compared to the cloud version, because we're not switching between Azure SQL and SQL Server - it's all SQL Server. The basic gist of the process is:

1. Backup the database on the Tier 1 (no preparation needed)
2. Restore the database to the on-premises SQL instance
3. Run a script against the restore DB to update some values
4. Start an AOS in the on-premises and wait for it to automatically run database synchronize and deploy reports

First back up the database on the Tier 1 environment and restore it to the on-premises environment - don't overwrite the existing on-premises database, keep that one and restore the new one with a different name - because we're going to need to copy some values across from the old DB to the new DB.

Now run this script against the newly restored DB, make sure to set the values for the database names correctly:

--Remove the database level users from the database 
--these will be recreated after importing in sql server. 
use AXDB_onebox --******************* SET THE NEWLY RESTORED DATABASE NAME**************************** 
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 
--now recreate the users copying from the existing database: 
use AXDB --******************* SET THE OLD ON-PREMISES 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' 
GO 
use AXDB_onebox --******************* 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 
--Storage isn't copied from one environment to another because it's stored outside 
--of the database, so clearing the links to stored documents
UPDATE T1 SET T1.STORAGEPROVIDERID = 0, T1.ACCESSINFORMATION = '', T1.MODIFIEDBY = 'Admin', T1.MODIFIEDDATETIME = getdate()
FROM DOCUVALUE T1
WHERE T1.STORAGEPROVIDERID = 1 --Azure storage 
--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) 
--SysFlighting is empty IN on-premises environments, so clean it up 
GO TRUNCATE TABLE SYSFLIGHTING 
--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 ON-PREMISES DATABASE NAME****************************
WHERE x.ID = 'Admin'
        AND USERINFO.ID = 'Admin' 

Now the database is ready, we're going to rename the old on-premises database from AXDB to AXDB_old, and the newly restored database from AXDB_onebox to AXDB. This means we don't have to change the AOS configuration to point to a new database - we're using the same users and the same database name.

All we need to do is restart all the AOS processes (either reboot the machines or restart the AOS apps from service fabric explorer).

When the AOSes restart one of them will run a database synchronize & deploy reports - because they can tell the database changed. You can watch progress in the AOS event log – create a custom event log view for all events under “Services and applications\Microsoft\Dynamics”. When this is finished you’ll see a record appear in SF.SYNCLOG in the AXDB.

A few other things to note:

• Only the Admin user can log in - because I'm assuming that the users from the onebox environment were all AAD cloud users, and that's not what the on-premises environment uses. The script above fixed the Admin user, but left the others as-is.
• To get Management Reporter working again, perform a reset.
• Storage (things like document handling documents) aren't kept in the database, so copy the database hasn't copied those things across. In the script above we cleared the links in the DocuValue table, so that we don't try and open docs from Azure storage which aren't there.
• The script has withheld all batch jobs, to stop anything running which shouldn't.
• Data stored in fields that were encrypted in the Tier1 environment, won't be readable in the restored database - there aren't many fields that are like this, details can be found here.