SQL DB backup and restore data

We're performing a database backup and restore across a different AOS instance today (AX 2012 R2).  Great fun.  Fortunately in my case we are doing it within the same domain which helps considerably.

The first thing to do is perform the SQL Backup of the server, so that we can then apply a restore on a different AOS installation. Before that though, see the fantastic MSDN blog post 'Moving between Microsoft Dynamics AX 2012 Environments' and it's asociated Word document.  Note the comment about checking that the two installations must be of the same 'state' to be able to do this.  It's essential to have both services with the same patches and models applied.

Below is my thrown together list of SQL commands I've generated while going through the document attached to the blog, adding a few of my own entities such as the DMF framework parameters.  Run the select commands below and write the output to a file, before performing the restore to ensure that you have the original values saved somewhere.  The Make sure to change the <01@NewAOS> and <02@OldAOS> placeholders with the actual AOS instance name.  Also the server names for <SRV01> (and <SRVDB> for the reports/cubes/db) and <SRV02> placeholders will require changing.  I've not needed to create scripts for saving users across domains, as I said before.

-- ESSENTIAL: Verify the Kernel and Application versions in the Database 
print '>> Kernel and Application versions'
select * from SYSSETUPLOG where DESCRIPTION = 'finished' order by CREATEDDATETIME desc

-- AIF, check for more than one Web site!
print '>> AIF'
select * from AIFWEBSITES
/*
update AIFWEBSITES set URL = 'http://SRV01/MicrosoftDynamicsAXAif60' where URL = 'http://SRV02/MicrosoftDynamicsAXAif60'
update AIFWEBSITES set NAME = 'SRV01-Default Web Site-MicrosoftDynamicsAXAif60' where NAME = 'SRV02-Default Web Site-MicrosoftDynamicsAXAif60'
update AIFWEBSITES set VIRTUALDIRECTORYSHARE = '\\SRV01\MicrosoftDynamicsAXAif60' where VIRTUALDIRECTORYSHARE = '\\SRV02\MicrosoftDynamicsAXAif60'
*/

-- Batch and AOS
print '>> Batch and AOS'
select * from SysServerConfig
select * from BatchServerConfig
select * from SysClusterConfig

select * from BatchServerGroup
select * from Batch

/*
update BatchServerConfig set SERVERID = '01@NewAOS' where SERVERID = '01@OldAOS'
-- SysServerConfig will get updated if you have already started the AOS, but it will not be treated as a batch server.
update SysServerConfig set enablebatch = 1 where SERVERID = '01@NewAOS'
--update SysServerConfig set CLUSTERREFRECID = '5637144576' where SERVERID = '01@NewAOS'
--update SysServerConfig set SERVERGUID = '724181F9-3740-4A6D-8850-3749F514011F' where SERVERID = '01@NewAOS'

update Batch set SERVERID = '01@NewAOS' where SERVERID = '01@OldAOS'
update BatchServerGroup set SERVERID = '01@NewAOS' where SERVERID = '01@OldAOS'
*/
-- BI and SRSS
print '>> BI and SRSS'
select * from BIANALYSISSERVER
select * from BICONFIGURATION
select * from SRSSERVERS

/*
-- Execute the below for *each* cube deployed, e.g. 'PurchCube'
update BIANALYSISSERVER set SERVERNAME = 'SRVDB' 
 --, DEFAULTDATABASENAME = 'PurchCube initial'
 where SERVERNAME = 'SRV02'
 -- and DEFAULTDATABASENAME = 'PurchCube'

-- Do *each* of your modified db cube databases have the same name?...  Project file location?...
update BICONFIGURATION 
    set CONNECTIONSTRING = 'Provider=SQLNCLI10.1;Data Source=SRVDB;Integrated Security=SSPI;Initial Catalog=MicrosoftDynamicsAX'
        --,PROJECTFILENAME = 'C:\Users\usr1\AppData\Local\Temp\2\PurchCube2\PurchCube.dwproj'
        --,PROJECTNAME = 'ZZZCubes'
    where CONNECTIONSTRING = 'Provider=SQLNCLI10.1;Data Source=SRV02;Integrated Security=SSPI;Initial Catalog=MicrosoftDynamicsAX'
        -- and PROJECTFILENAME = 'C:\Users\usr1\AppData\Local\Temp\1\PurchCube\PurchCube.dwproj'
        -- and PROJECTNAME = 'Dynamics AX'

-- Reporting
update SRSSERVERS set SERVERID = '01@NewAOS',
     SERVERURL = 'http://SRVDB/ReportServer',
 REPORTMANAGERURL = 'http://SRVDB/Reports',
 AOSID = '01@NewAOS',
 CONFIGURATIONID = 'SRVDBMSSQLSERVER',
 AXAPTAREPORTFOLDER = 'DynamicsAX',
 SERVERINSTANCE = 'MSSQLSERVER'
    where serverid = '01@OldAOS'

--It is recommended to redeploy new ODC files (users should ensure all connections strings have correct server names and databases.)
--Redeploy any Enterprise Portal changes that exist in the AOT to the test EP website.
*/

-- Document Management
print '>> Document Management'
select * from SystemParameters
/*

*/

-- Help Server, Performance thingy
Print '>> Help Server, Performance thingy'
select VALUE from SYSGLOBALCONFIGURATION where NAME = 'HelpServerLocation'
select VALUE from SYSGLOBALCONFIGURATION where NAME = 'SCOMPERFORMANCEAOS'
select * from SYSGLOBALCONFIGURATION 
/*
update SYSGLOBALCONFIGURATION set VALUE = 'http://SRV01:8080/DynamicsAX6HelpServer/HelpService.svc' 
    where NAME = 'HelpServerLocation' AND VALUE = 'http://SRV02/DynamicsAX6HelpServer/HelpService.svc'
update SYSGLOBALCONFIGURATION set VALUE = '01@NewAOS' 
    where NAME = 'SCOMPERFORMANCEAOS' AND VALUE = '01@OldAOS'
-- Customisations on the AOS?  Check if the max table join limit has been changed but there's no need to update the value
--update SYSGLOBALCONFIGURATION set VALUE = '30'
--    where NAME = 'MAXJOINLIMIT' AND VALUE = '26'
*/

--System Service Accounts, AX user accounts
print '>> System Service Accounts, AX user accounts'
select * from SysBCProxyUserAccount
select * from SysWorkflowParameters
select * from userinfo
Select * from sysServerSessions

/*
-- Launch the command line, as we need to update the userinfo where id = 'admin'
cmd.exe C:\Users\jondoe>Whoami /user
INFORMACIÓN DE USUARIO
----------------------
Nombre de usuario SID
================= ==============================================
DOMAIN\jondoe     S-1-5-21-1362911470-1944611129-2500288337-1131

-- To obtain other domain user SIDs see http://technet.microsoft.com/en-us/sysinternals/bb897417.aspx (Translates SIDs to names and vice versa)
*/

/*
-- Business Connector
update SysBCProxyUserAccount set SID='S-1-5-21-1362911470-1944611129-2500288337-1132', Networkdomain = 'DOMAIN.INT', networkalias = 'AXPROCON' 
    where SID = 'S-1-5-21-1362911470-1944611129-2500288337-1123'

-- Workflow is activated?
-- update SysWorkflowParameters set EXECUTIONUSERID = 'Admin' 
--    where EXECUTIONUSERID = 'wfexc'

--With a change of domain, update the key users and I suggest to delete all of the other users and reimport.
--Changing the Microsoft Dynamics AX *admin* users account to new Domain Account information 
select SID, Networkdomain, networkalias from userinfo -- where networkalias = '<user_alias>'
update userinfo set SID='S-1-5-21-1362911470-1944611129-2500288337-1131', Networkdomain = 'DOMAIN', networkalias = 'jondoe' 
    where id = 'admin'
-- AXPRODBA --> AOS service account
update userinfo set SID='S-1-5-21-1362911470-1944611129-2500288337-1133', Networkdomain = 'DOMAIN', networkalias = 'AXPROAOS',  NAME = 'AXPRODBAOS'    
    where id = 'oldaxprodba'
-- Workflow account
--update userinfo set SID='S-1-5-21-1362911470-1944611129-2500288337-1132', Networkdomain = 'DOMAIN.INT', networkalias = 'AXPROCON', NAME = 'AXPROCON'
--    where id = 'wfexc'

--Setting Database Permissions for the AOS service account.  Will already be set if you have started the AOS service.
--select * from sysServerSessions  --AOSAccount
update sysServerSessions 
    set AOSID = 'SRV01@2712',
        AOSACCOUNT = 'DOMAIN\AXPROAOS'
        --, WORKLOAD = '11811151'
    where SERVERID = '1' AND AOSID = 'SRV02@2712'
--The recommended approach would be to remove all records from the sysserversessions table while there are no AOS instances running and have the record recreated as the AOS service starts.
delete from sysServerSessions
*/

-- Workflow
-- Run the Workflow infrastructure configuration again to rebuild the jobs?
Print '>> Workflow'
select * from BATCHJOB where CAPTION like 'workflow%'
select * from BATCH where CAPTION like 'workflow%'
/*
-- Already updated the Batch.SERVERID value, above
*/

-- SysFileStoreParameters Document
print '>> SysFileStoreParameters Document'
select * from SysFileStoreParameters
/*
-- update file store directory?
--update SysFileStoreParameters set FILEPATH = '' where KEY_ = 0
*/

-- Email params
Print '>> Email params'
select * from SysEmailParameters
select * from SysEmailSMTPPassword
/*
-- SysEmailParameters
--update SysEmailParameters set SMTPRELAYSERVERNAME = 'smtp.gmail.com', 
-- SMTPPORTNUMBER = '465', SMTPUSERNAME = 'TEST@gmail.com'
-- where KEY = 0
-- password is encoded, and too long to insert here
--update SysEmailSMTPPassword set PASSWORD = '', AOSID = 'SRV01@2712'
--  where AOSID = 'SRV02@2712'
*/

-- Enterprise Portal WebSiteParams
print '>> Enterprise Portal WebSiteParams'
select * from EPWebSiteParameters
select * from EPGLOBALPARAMETERS
--select * from EPDOCUPARAMETERS
--select * from EPPERSONALIZE
select * from COLLABSITEPARAMETERS
/*
update EPWebSiteParameters 
    set INTERNALURL = 'http://SRV01/sites/DynamicsAx'
    , SITEID = '835C2EE4-CBB9-4D6A-8CD6-FE0BD5F2ED51'
    , EXTERNALURL = 'http://SRV01/sites/DynamicsAx' 
    where COMPANYID = 'DAT'
    
update EPGLOBALPARAMETERS 
    set HOMEPAGESITEID = '835C2EE4-CBB9-4D6A-8CD6-FE0BD5F2ED51'
    where HOMEPAGESITEID = 'C73610FD-C4CE-4AA7-A501-007B4D8E1BC0'
update EPGLOBALPARAMETERS 
    set DEVELOPMENTSITEID = '835C2EE4-CBB9-4D6A-8CD6-FE0BD5F2ED51'
    , SEARCHSERVERURL = 'http://SRV01/sites/DynamicsAxClientSearch/_vti_bin/search.asmx'
    where HOMEPAGESITEID = '835C2EE4-CBB9-4D6A-8CD6-FE0BD5F2ED51'

update COLLABSITEPARAMETERS 
    set ROOTURL = 'http://SRV01/sites/DynamicsAx'
    where ROOTURL = 'http://SRV02/sites/DynamicsAx'
*/

-- DMF (Import/Export Framework)
select * from DMFParameters
select ds.*, dsp.*
  from DMFDATASOURCE ds
    inner join DMFDATASOURCEPROPERTIES dsp on dsp.SOURCE = ds.SOURCENAME
/*
update DMFParameters 
 set SHAREDFOLDERPATH = '\\SRV01\dmf\'
 --, VALIDATIONSTATUS = 0 -- Juuuust in case
 where KEY_ = 0
-- TODO: Recreate file-formats manually
*/

-- Project Server Integration
Print '>> Project Server Integration'
select * from ProjServerSettings
select * from SyncParameters
/*
-- TODO: Update
*/

-- Fix form cache problems, requires an AOS restart.  Not necessary if Ax Client only ever 'points' to one AX instance
select * from SysSQMSettings 
/*
update SysSQMSettings set GLOBALGUID = '00000000-0000-0000-0000-000000000000'
*/

Use at your own risk... But at least I can get the AOS up and running again now.