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:
  • Ahmer Khalid Profile Picture
    Ahmer Khalid 117 on at
    RE: Backup and restore database to another on-premise environment

    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

  • Suggested answer
    nb1 Profile Picture
    nb1 on at
    RE: Backup and restore database to another on-premise environment

    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

  • nb1 Profile Picture
    nb1 on at
    RE: Backup and restore database to another on-premise environment

    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!

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

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 293,245 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,927 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156 Moderator

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans