web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance forum / Backup and restore dat...
Finance forum

Backup and restore database to another on-premise environment

(0) ShareShare
ReportReport
Posted on by Ayush Thapliyal 95

I would like to know how can I copy the complete database from one on premise environment to another On Premise Environment in Dynamics 365 for Operations?

Thank You

Categories:
I have the same question (0)
  • nb1 Profile Picture
    nb1 on at

    Also looking to copy a database into a D365 FinOps On Prem environment.

    Steps I've tried unsuccessfully:

    1. Ensure same code applied to Environment as source Environment
    2. Restore new DB to on-prem SQL Server
    3. Run scripts from setup guide to Initialize and Configure DB (docs.microsoft.com/.../setup-deploy-on-premises-environments)
    4. Copy an existing on-prem admin account into the imported DB

    -- delete existing admin account

    DELETE FROM axdb.dbo.userinfo WHERE ID = 'admin'

    -- create new admin account using former account settings from another on-prem DB

    insert into axdb.dbo.userinfo ([ID],[NAME],[ENABLE],[DEL_STARTUPMENU],[STATUSLINEINFO],[TOOLBARINFO],[DEBUGINFO],[AUTOINFO],[AUTOUPDATE],[GARBAGECOLLECTLIMIT],[HISTORYLIMIT],[MESSAGELIMIT],[GENERALINFO],[SHOWSTATUSLINE],[SHOWTOOLBAR],[DEBUGGERPOPUP],[SHOWAOTLAYER],[DEL_PASSWORD],[DEL_OSACCOUNTNAME],[STARTUPPROJECT],[CONFIRMDELETE],[CONFIRMUPDATE],[REPORTFONTNAME],[REPORTFONTSIZE],[FORMFONTNAME],[FORMFONTSIZE],[PROPERTYFONTNAME],[PROPERTYFONTSIZE],[INFOLOGLEVEL],[COMPANY],[AUTOLOGOFF],[QUERYTIMELIMIT],[TRACEINFO],[REPORTTOPMARGIN],[REPORTBOTTOMMARGIN],[REPORTLEFTMARGIN],[REPORTRIGHTMARGIN],[COMPILERWARNINGLEVEL],[SID],[NETWORKDOMAIN],[NETWORKALIAS],[ENABLEDONCE],[EXTERNALUSER],[LANGUAGE],[HELPLANGUAGE],[PREFERREDTIMEZONE],[PREFERREDCALENDAR],[HOMEPAGEREFRESHDURATION],[NOTIFYTIMEZONEMISMATCH],[FILTERBYGRIDONBYDEFAULT],[GLOBALFORMOPENMODE],[DEL_DEFAULTMODELID],[SHOWMODELNAMEINAOT],[ACCOUNTTYPE],[ISSUERRECID],[CREDENTIALRECID],[GLOBALLISTPAGELINKMODE],[GLOBALEXCELEXPORTMODE],[CLIENTACCESSLOGLEVEL],[DEFAULTPARTITION],[GLOBALEXCELEXPORTFILEPATH],[EXTERNALIDTYPE],[EXTERNALID],[RECVERSION],[PARTITION],[RECID],[PREFERREDLOCALE],[IDENTITYPROVIDER],[OBJECTID],[INTERACTIVELOGON],[ISMICROSOFTACCOUNT])

    select [ID],[NAME],[ENABLE],[DEL_STARTUPMENU],[STATUSLINEINFO],[TOOLBARINFO],[DEBUGINFO],[AUTOINFO],[AUTOUPDATE],[GARBAGECOLLECTLIMIT],[HISTORYLIMIT],[MESSAGELIMIT],[GENERALINFO],[SHOWSTATUSLINE],[SHOWTOOLBAR],[DEBUGGERPOPUP],[SHOWAOTLAYER],[DEL_PASSWORD],[DEL_OSACCOUNTNAME],[STARTUPPROJECT],[CONFIRMDELETE],[CONFIRMUPDATE],[REPORTFONTNAME],[REPORTFONTSIZE],[FORMFONTNAME],[FORMFONTSIZE],[PROPERTYFONTNAME],[PROPERTYFONTSIZE],[INFOLOGLEVEL],[COMPANY],[AUTOLOGOFF],[QUERYTIMELIMIT],[TRACEINFO],[REPORTTOPMARGIN],[REPORTBOTTOMMARGIN],[REPORTLEFTMARGIN],[REPORTRIGHTMARGIN],[COMPILERWARNINGLEVEL],[SID],[NETWORKDOMAIN],[NETWORKALIAS],[ENABLEDONCE],[EXTERNALUSER],[LANGUAGE],[HELPLANGUAGE],[PREFERREDTIMEZONE],[PREFERREDCALENDAR],[HOMEPAGEREFRESHDURATION],[NOTIFYTIMEZONEMISMATCH],[FILTERBYGRIDONBYDEFAULT],[GLOBALFORMOPENMODE],[DEL_DEFAULTMODELID],[SHOWMODELNAMEINAOT],[ACCOUNTTYPE],[ISSUERRECID],[CREDENTIALRECID],[GLOBALLISTPAGELINKMODE],[GLOBALEXCELEXPORTMODE],[CLIENTACCESSLOGLEVEL],[DEFAULTPARTITION],[GLOBALEXCELEXPORTFILEPATH],[EXTERNALIDTYPE],[EXTERNALID],[RECVERSION],[PARTITION],[RECID],[PREFERREDLOCALE],[IDENTITYPROVIDER],[OBJECTID],[INTERACTIVELOGON],[ISMICROSOFTACCOUNT]

    from <database>.dbo.userinfo where id = 'admin' and name = 'admin'

    6. Wait for DB Sync to run (view this DB Sync log on the AOS server: Applications & Services > Microsoft > Dynamics > AX-DatabaseSynchronize > Operational)

    Errors at this point are more index related that I'm trying to resolve manually. If anyone has a more comprehensive guide like the ones for copying DBs between SQL server and Azure SQL in cloud environments, that would be awesome!

  • Suggested answer
    nb1 Profile Picture
    nb1 on at

    I did figure this out eventually at the end of February 2018. I highly recommend this Yammer group for decent insights into D365 FOE on-prem environments: https://www.yammer.com/dynamicsaxfeedbackprograms/

    My steps:

    1. Backup TEST AxDB then copy to SANDBOX SQL
    2. Backup SANDBOX AxDB
    3. Delete Environment through LCS

      1. On the primary Orchestration Service server logs, see that the cleanup job initiated from LCS is successful: Event Viewer > Applications and Services Logs > Microsoft > Dynamics > AX-LocalAgent > Operational

      2. Delete SANDBOX AxDB
    4. Restore TEST AxDB on SANDBOX (https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/deployment/setup-deploy-on-premises-environments#configuredb)
      1. Initialize Database Script Functions
      2. Configure Database Script Functions
    5. Redeploy On-Prem Environment from LCS
    6. Monitor Deployment Status
      1. On the primary Orchestration Service server logs, the deployment job steps initiated from LCS are listed in this log: Event Viewer > Applications and Services Logs > Microsoft > Dynamics > AX-LocalAgent > Operational
    7. Add On-Prem User accounts
      1. Existing accounts that authenticate to Azure Online will not work
      2. Don't delete those existing accounts so their Dynamics 365 ERP “person” identities are kept from TEST environment
      3. Copy Users from original AxDB into new AxDB:

        1. INSERT INTO AXDB.dbo.USERINFO SELECT * FROM AXDB_20180227.dbo.USERINFO WHERE ENABLEDONCE = 1;

      4. Add all users back to “System Users” Group (I didn't figure out a SQL script for this)
      5. Repeat adding appropriate users to role “Systems Administrator”
      6. Transfer “person” identities from MS Authentication account to On-Prem account

  • Ahmer Khalid Profile Picture
    Ahmer Khalid 117 on at

    HI,

    After restoration of AXDB you need to run following script.

    .\Reset-DatabaseUsers.ps1 -DatabaseServer '<FQDN of the SQL server>' -DatabaseName 'AXDB'

    .\Initialize-Database.ps1 -ConfigurationFilePath .\ConfigTemplate.xml -ComponentName AOS

    .\Configure-Database.ps1 -ConfigurationFilePath .\ConfigTemplate.xml -ComponentName AOS

    make sure after running  Initialise script Mapping is correct in TempDB

    svc-AXSF$ and axdbadmin user have mapping with (db_datareader, db_datawriter, db_ddladmin).

    Thanks

    Ahmer

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Coming on 11/8!

In our never-ending quest to help the Dynamics 365 Community members get answers faster …

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard > Finance

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans