57) Database Restore – Dynamics 365 for finance and operations

-

Restore Database from Tier 1 to Tier 2+ Environment.

Tier 1 environment has windows sql while tier 2 environment has Azure Sql. So directly we can’t take backup from one and restore into another.

Export Database in Bacpac format

We have to first prepare database to take backup by running sql scripts. Please follow below steps.

Please make sure that source environment is not in maintenance mode. If it is then please turn off maintenance mode otherwise you will face error while importing database into tier 2 environment.

BONOUS TIP
  • Take a backup of tier 1 environment database(.bak) file format
  • Create new Database on Tier1 Environment(E.G. CopyOfAxDB)
  • Restore backup on CopyOfAxDB which was taken earlier.

By performing all above steps we have created replica of tier 1 database. Now we will perform all sql scripts on “CopyOfAxDB” which will not impact on existing tier 1 environment.


  • Execute Below script against “CopyOfAxDB”
update sysglobalconfiguration
set value = 'SQLAZURE'
where name = 'BACKENDDB'

update sysglobalconfiguration
set value = 1
where name = 'TEMPTABLEINAXDB'

drop procedure XU_DisableEnableNonClusteredIndexes
drop schema [NT AUTHORITY\NETWORK SERVICE]
drop user [NT AUTHORITY\NETWORK SERVICE]
drop user axdbadmin
drop user axdeployuser
drop user axmrruntimeuser
drop user axretaildatasyncuser
drop user axretailruntimeuser
  • After Running above script, your database is ready to be exported in bacpac file format.
  • Right Click on Database, Go to Tasks and select “Export Data Tier Application”
  • Provide required details in wizard and complete the wizard.
  • Above wizard will export database in bacpac file formart which can be directly imported into tier 2+ Environment
  • Incase you have faced any time out related error while execution of wizard you can run following command to export database through SQLPackage.exe
cd C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin\
SqlPackage.exe /a:export /ssn:localhost /sdn:<database name to export> /tf:D:\Exportedbacpac\AxDBGoldenConfig.bacpac /p:CommandTimeout=1200 /p:VerifyFullTextDocumentTypesSupported=false

By executing above command you will not face timeout error and will be able to export database. Please make a note that in above command location of sqlpackage.exe can differ based on version of sql server installed.

Import Bacpac file in tier 2+ Environment.

  • Go to Project Asset Library in LCS and upload bacpac file into Database Backup.
  • Now visit Environment Details page of tier 2 environment where you want to restore.
  • Go to Maintain, Then click on Move Database
  • Now you will get a screen with various options. Click on Import Database and then select bacpac file from asset library and finish the wizard.
  • Wait until database is imported.

By following above steps you can easily restore database from windows Sql to Azure Sql in Dynamics 365 Finance and Operations. Environment related setups and encrypted values can be re configure in destination environment once restore activity is completed.

Restore Database from Tier 2+ environment to Tier 1 Environment.

Generally this requirement comes up when we wanted to troubleshoot any UAT issue in devBox. If you want to restore your UAT environment database into your devbox then follow below steps.

Export Bacpac file from UAT Environment.

  • Visit Environment Details page in LCS
  • Go to Maintain and then click on Move Database
  • Select Export Database, It will popup export database wizard
  • After completing wizard system will start exporting database.
  • Once Export is finished, You will get bacpac file in project asset library.

Import Bacpac file in DevBox

  • Download bacpac file from asset library to local folder.
  • Run below command in command prompt to import database
cd C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin

SqlPackage.exe /a:import /sf:D:\Exportedbacpac\UAT.bacpac /tsn:localhost /tdn:<target database name> /p:CommandTimeout=1200
  • Please make sure that target database name is not existing in database server. You have to create new database while importing bacpac file.
  • once import is finished you have to update database to support windows sql.
  • Run below query against imported database.
CREATE USER axdeployuser FROM LOGIN axdeployuser
EXEC sp_addrolemember 'db_owner', 'axdeployuser'

CREATE USER axdbadmin FROM LOGIN axdbadmin
EXEC sp_addrolemember 'db_owner', 'axdbadmin'

CREATE USER axmrruntimeuser FROM LOGIN axmrruntimeuser
EXEC sp_addrolemember 'db_datareader', 'axmrruntimeuser'
EXEC sp_addrolemember 'db_datawriter', 'axmrruntimeuser'

CREATE USER axretaildatasyncuser FROM LOGIN axretaildatasyncuser
EXEC sp_addrolemember 'DataSyncUsersRole', 'axretaildatasyncuser'

CREATE USER axretailruntimeuser FROM LOGIN axretailruntimeuser
EXEC sp_addrolemember 'UsersRole', 'axretailruntimeuser'
EXEC sp_addrolemember 'ReportUsersRole', 'axretailruntimeuser'

CREATE USER axdeployextuser FROM LOGIN axdeployextuser
EXEC sp_addrolemember 'DeployExtensibilityRole', 'axdeployextuser'

CREATE USER [NT AUTHORITY\NETWORK SERVICE] FROM LOGIN [NT AUTHORITY\NETWORK SERVICE]
EXEC sp_addrolemember 'db_owner', 'NT AUTHORITY\NETWORK SERVICE'

UPDATE T1
SET T1.storageproviderid = 0
    , T1.accessinformation = ''
    , T1.modifiedby = 'Admin'
    , T1.modifieddatetime = getdate()
FROM docuvalue T1
WHERE T1.storageproviderid = 1 --Azure storage

DROP PROCEDURE IF EXISTS SP_ConfigureTablesForChangeTracking
DROP PROCEDURE IF EXISTS SP_ConfigureTablesForChangeTracking_V2
GO
  • Turn on change tracking if it is enabled in source database.
ALTER DATABASE [your database name] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 6 DAYS, AUTO_CLEANUP = ON);
  • Now your database is ready to use with D365 Finance and Operations.
  • Stop Batch Management Service, Management Reporter Service and World Wide Web Service.
  • Swap the name of database(E.g. AxDB to AxDB_Orig and CopyOFUAT to AxDB) by rename or Take backup of CopyOFUAT as bak file and restore it in AxDB as we were doing in AX2012.
  • Start all the services which we stopped in earlier step.

I hope above article is helpful to all of you in backup and restore database for Dynamics 365 finance and operations. If you face any issue please ask your questions in comment box. Happy Daxing!

Note: Before Implementing above steps please confirm it with your solution architect.

Comments

Post a Comment

Popular posts from this blog

Customization on Sales invoice Report in D365 F&O

-
 We have a requirement to create a new design and show some new fields in the sales invoice report.  So for that we need to consider the below steps :-  1) Controller class than extends standard class and create alogic. 2) Create a print management class to call the new design. 3) Extension of DP class to write a logic. 4) Report design. ==================================================================== 1) Controller class : -  /// <summary> /// Extension of the controller class to call the custom design of report "MMSSalesInvoice.report" for MMS legal entity. /// </summary> class MMSSalesInvoiceController extends SalesInvoiceController {        public static MMSSalesInvoiceController construct()         {             return new MMSSalesInvoiceController();         }         public static void main(Args _args)         { ...
Read more

75) COC - Create a coc of the table modified method

-
 /// <summary> /// The <c>AvaEntAssetChecklistVariableOptionsDbt_Extension</c> contains the TransMountain extending logic for the <c>EntAssetChecklistVariableOptions</c> table. /// </summary> [ExtensionOf(tableStr(EntAssetChecklistVariableOptions))] final class AvaEntAssetChecklistVariableOptionsDbt_Extension {     /// <summary>     /// Processes the modified field event for TransMountain fields.     /// </summary>     /// <param name = "_fieldId">reference to field id</param>     public void modifiedField(FieldId _fieldId)     {         EntAssetChecklistVariableOptions entAssetChecklistVariableOptions;         next modifiedField(_fieldId);         // Default must be marked for only one check list line for the varaible.         if (_fieldId == fieldNum(EntAssetChecklistVariableOptions,...
Read more

46) D365 FO: SHAREPOINT FILE UPLOAD USING X++

-
Image
Requirement : Uploading a file in Sharepoint online using X++ and inside from Microsoft Dynamics 365 finance and operations. Prerequisites: The current user id of Microsoft Dynamics 365 Finance and operations should have proper rights and privileged in Sharepoint Online. Sample Code: In this scenario i am creating a text file in Microsoft Dynamics 365 finance and operations and then uploading its memory Stream in Sharepoint online using X++ . You can modify the X++ code in D365 finance and operations as per your need. class RunnableClassRun { /// /// Runs the class with the specified arguments. /// /// The specified arguments. public static void main(Args _args) { System.UriBuilder builder = new System.UriBuilder(“ https://XXXXXX.sharepoint.com&#8221 ;); str extId = xUserInfo::getExternalId(); Microsoft.Dynamics.AX.Framework.FileManagement.SharePointDocumentStorageProvider provider; Microsoft.Dynamics.AX.Framework.FileManagement.DocumentLocation documentLocation ...
Read more