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.
A slider pane will open on the page where you can use the Export database action.
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.
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.
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.
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
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.
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]