Export a database

You can use Microsoft Dynamics Lifecycle Services (LCS) to export a database for Dynamics 365 for Finance and Operations from a sandbox user acceptance testing (UAT) environment to the Asset library.

Self-service export database

From your sandbox Environment Details page, click the Maintain menu, and then select Move database.
Move database menu

A slider pane will open on the page where you can use the Export database action.
Export database menu

The environment will be unavailable for other servicing operations, such as Sandbox refresh or package deployment during this time. The source environment will be usable from a Dynamics user perspective.

After the export operation completes successfully, sign off on the servicing operation on your Environment details page. You can then see the asset in your Asset Library in the Database backups section. Asset library backup files

The .bacpac files are stored here and can be manually downloaded to your Tier 1 developer environments for import. In the future, Microsoft will provide APIs to trigger the export action, as well as list the available backup files in your asset library. This includes the secured URL for automatically downloading a backup asset file or copying it directly to your secure blob storage using Microsoft Azure Storage SDKs.

Export operation failure

If the export operation isn't successful, you can do a rollback. If you select the Rollback option after the initial failure of the operation, your source sandbox environment is restored to the state that it was in before the export began.

To determine the root cause of the failure, download the runbook logs by using the available buttons before you start the rollback operation.

Data elements that aren't exported

When you export a database backup from an environment, some elements of the database aren't exported in the backup file. Here are some examples:

  • Email addresses in the LogisticsElectronicAddress table.
  • Batch job history in the BatchJobHistory, BatchHistory, and BatchConstraintHistory tables.
  • SMTP password in the SysEmailSMTPPassword table.
  • SMTP Relay server in the SysEmailParameters table.
  • Print Management settings in the PrintMgmtSettings and PrintMgmtDocInstance tables.
  • Environment-specific records in the SysServerConfig, SysServerSessions, SysCorpNetPrinters, SysClientSessions, BatchServerConfig, and BatchServerGroup tables.
  • Document attachments in the DocuValue table. This includes any Office Templates that were overriden in the source environment.
  • Connection string in the PersonnellIntegrationConfiguration table
  • All users except the admin will be set to Disabled status.
  • All batch jobs are set to Withhold status.

Known issues

Export ran for some time and then reached a "Preparation failed" state

The export process can time out on Azure SQL Database when large databases are involved. In some cases, the export process can be recovered by using the Resume action from LCS. The Lifecycle Services team is working to identify known error codes, so these can be added to the logs for the export database operation to help guide users toward a resolution. These known error codes will be added in a future release of LCS. If you encounter an issue, you can manually export by following the “Manual export” section below.

Export doesn't show any progress in LCS

The export process differs from other database movement operations and the general package deployment doesn't use a runbook. Therefore, the progress indicator in LCS doesn't show any output, as it would typically show in other scenarios. The LCS team is working to identify known error codes, so these can be added to the logs for the export database operation to help guide users toward a resolution. These known error codes will be added in a future release of LCS. If you encounter an issue, you can export manually by following the “Manual export” section below.

Manual export

Encrypted and environment-specific values can't be imported into a new environment. After you've completed the import, you must re-enter some data from your source environment in your target environment.

Document the values of encrypted fields

Because of a technical limitation that is related to the certificate that is used for data encryption, values that are stored in encrypted fields in a database will be unreadable after that database is imported into a new environment. Therefore, after an import, you must manually delete and re-enter values that are stored in encrypted fields. New values that are entered in encrypted fields after an import will be readable. The following fields are affected. The field names are given in Table.Field format.

Field name Where to set the value
CreditCardAccountSetup.SecureMerchantProperties Select Accounts receivable > Payments setup > Payment services.
ExchangeRateProviderConfigurationDetails.Value Select General ledger > Currencies > Configure exchange rate providers.
FiscalEstablishment_BR.ConsumerEFDocCsc Select Organization administration > Fiscal establishments > Fiscal establishments.
FiscalEstablishmentStaging.CSC This field is used by the Data Import/Export Framework (DIXF).
HcmPersonIdentificationNumber.PersonIdentificationNumber Select Human resources > Workers > Workers. On the Worker tab, in the Personal information group, select Identification numbers.
HcmWorkerActionHire.PersonIdentificationNumber This field has been obsolete since Microsoft Dynamics AX 7.0 (February 2016). It was previously in the All worker actions form (Human resources > Workers > Actions > All worker actions).
SysEmailSMTPPassword.Password Select System administration > Email > Email parameters.
SysOAuthUserTokens.EncryptedAccessToken This field is used internally by AOS. It can be ignored.
SysOAuthUserTokens.EncryptedRefreshToken This field is used internally by AOS. It can be ignored.

If you're running Retail components, document encrypted and environment-specific values

The values on the following pages are either environment-specific or encrypted in the database. Therefore, all the imported values will be incorrect.

  • Payments services (Accounts receivable > Payments setup > Payments services)
  • Hardware profiles (Retail and commerce > Channel setup > POS setup > POS profiles > Hardware profiles)

Create a copy of the source database

Because you must turn off change tracking and delete database users before you can export the source Azure SQL database, you should create a copy of that database. You can then work with the copy instead of deleting information from the original database. The following SQL statement creates a copy of the axdb_mySourceDatabaseToCopy database and names it MyNewCopy. Edit this script so that it uses the names of your databases.

CREATE DATABASE MyNewCopy AS COPY OF axdb_mySourceDatabaseToCopy

This SQL statement runs asynchronously. In other words, although it appears to be completed after one minute, it actually continues to run in the background. For more information, see CREATE DATABASE (Azure SQL Database). To monitor the progress of the copy operation, run the following query against the MASTER database in the same instance.

SELECT * FROM sys.dm_database_copies

Warning

Retaining copies of the database for an extended period is not allowed in any Finance and Operations environment. Microsoft reserves the right to delete any copies of the database older than 7 days without any prior notice.

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. All these changes are required for a successful export and import of the database. These changes also help to ensure that when the AOS computer is started in the target environment, nothing automatically starts to run.

Note

You must edit the following ALTER DATABASE command so that it uses the name of your database copy.

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

--Remove certificates in database from Electronic Signature usage
DECLARE @SQLElectronicSig nvarchar(512)
DECLARE certCursor CURSOR for
select 'DROP CERTIFICATE ' + QUOTENAME(c.name) + ';'
from sys.certificates c;
OPEN certCursor;
FETCH certCursor into @SQLElectronicSig;
WHILE @@Fetch_Status = 0
BEGIN
print @SQLElectronicSig;
exec(@SQLElectronicSig);
FETCH certCursor into @SQLElectronicSig;
END;
CLOSE certCursor;
DEALLOCATE certCursor;


-- Re-assign full rext catalogs to [dbo]
BEGIN
    DECLARE @catalogName nvarchar(256);
    DECLARE @sqlStmtTable nvarchar(512)

    DECLARE reassignFullTextCatalogCursor CURSOR
       FOR SELECT DISTINCT name
       FROM sys.fulltext_catalogs 
       
       -- Open cursor and disable on all tables returned
       OPEN reassignFullTextCatalogCursor
       FETCH NEXT FROM reassignFullTextCatalogCursor INTO @catalogName

       WHILE @@FETCH_STATUS = 0
       BEGIN
              SET @sqlStmtTable = 'ALTER AUTHORIZATION ON Fulltext Catalog::[' + @catalogName + '] TO [dbo]'
              EXEC sp_executesql @sqlStmtTable
              FETCH NEXT FROM reassignFullTextCatalogCursor INTO @catalogName
       END
       CLOSE reassignFullTextCatalogCursor
       DEALLOCATE reassignFullTextCatalogCursor
END

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

--Change ownership of alternate schemas to DBO
ALTER AUTHORIZATION ON schema::shadow TO [dbo]
ALTER AUTHORIZATION ON schema::[BACKUP] TO [dbo]

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

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

SqlPackage.exe /a:export /ssn:<server>.database.windows.net /sdn:<database to export> /tf:D:\Exportedbacpac\my.bacpac /p:CommandTimeout=2400 /p:VerifyFullTextDocumentTypesSupported=false /sp:<SQL password> /su:<sql user>

Here is an explanation of the parameters:

  • ssn (source server name) – The name of the Azure SQL Database server to export from.
  • sdn (source database name) – The name of the database to export.
  • tf (target file) – The path and name of the file to export to.
  • sp (source password) – The SQL password for the source SQL Server.
  • su (source user) – The SQL user name for the source SQL Server. We recommend that you use the sqladmin user. This user is created on every Finance and Operations SQL instance during deployment. You can retrieve the password for this user from your project in Dynamics Lifecycle Services.

After the export is completed, run the following command to delete the database copy.

DROP DATABASE [MyNewCopy]