Copying a Production Database into a Development/Test Environment

Disclaimer: In no way am I making claim that this is the best, correct, or accepted approach to take when bringing down a copy of Production into Development. The outlined method has worked for me and served my purposes well.. with that said, follow my steps at your own risk!


1. Stop the AOS on Development or Test
2. Backup the AX Database in Production, and Restore it into Development or Test
3. Restore all the Security permissions/mappings to the AX Database (Model them after Production)
4. Write a T-SQL script to replace Production Server & User names in the Database with that which are related to Development or Test (see below for tables/columns that need to be changed)
5. On Development or Test, delete all files in the application folder (C:\Program Files\Microsoft Dynamics AX\50\Application\Appl\[Your AX Instance]) where the file extension ends with the letter “i”
6. Start the AOS on Development or Test (this may take awhile)
7. Open the AOT, right-click the Data Dictionary, and select Synchronize


Tables in the AX Database that need to be Modified (see step #4 above)

I had written a utility that mined the database for all references to Production system names. The following is the script that I came up with to “convert” a Production Database to Development/Test. Note: not all changes may actually be necessary (such as in the log tables) but for consistency, I change them anyway.

Declare @AOS varchar(15)
Declare @SP varchar(15)
Declare @SQL varchar(15)
Declare @axbcp varchar(15)

Set @AOS = '[Your DEV AOS Server Name]'
Set @SP = '[Your DEV SharePoint/Enterprise Portal Server Name]'
Set @SQL = '[Your DEV SQL Server Name]'
Set @axbcp = '[Your DEV axbcp User Account Name]'

Declare @P_AOS varchar(15)
Declare @P_SP varchar(15)
Declare @P_SQL varchar(15)
Declare @P_axbcp varchar(15)

Set @P_AOS = '[Your PRODUCTION AOS Server Name]'
Set @P_SP = '[Your PRODUCTION SharePoint/Enterprise Portal Server Name]'
Set @P_SQL = '[Your PRODUCTION SQL Server Name]'
Set @P_axbcp = '[Your PRODUCTION axbcp User Account Name]'


----------------------------------------------------------------------------------
Update [SYSBCPROXYUSERACCOUNT] Set NETWORKALIAS = @axbcp WHERE NETWORKALIAS = @P_axbcp
----------------------------------------------------------------------------------


Update [BATCH] Set SERVERID = REPLACE(upper(SERVERID), upper(@P_AOS), upper(@AOS))
Where upper(SERVERID) like '%' + upper(@P_AOS) + '%'
-----------------------------------------------------------------------------------


Update [BATCHHISTORY] Set SERVERID = REPLACE(upper(SERVERID), upper(@P_AOS), upper(@AOS))
Where upper(SERVERID) like '%' + upper(@P_AOS) + '%'
-----------------------------------------------------------------------------------


Update [BATCHSERVERGROUP] Set SERVERID = REPLACE(upper(SERVERID), upper(@P_AOS), upper(@AOS))
Where upper(SERVERID) like '%' + upper(@P_AOS) + '%'
------------------------------------------------------------------------------------


Update [EPWEBSITEPARAMETERS] Set [INTERNALURL] = REPLACE(upper([INTERNALURL]), upper(@P_SP), upper(@SP))
Where upper([INTERNALURL]) like '%' + upper(@P_SP) + '%'
Update [EPWEBSITEPARAMETERS] Set [EXTERNALURL] = REPLACE(upper([EXTERNALURL]), upper(@P_SP), upper(@SP))
Where upper([EXTERNALURL]) like '%' + upper(@P_SP) + '%'
------------------------------------------------------------------------------------


Update SRSSERVERS Set [SERVERURL] = REPLACE(upper([SERVERURL]), upper(@P_SQL), upper(@SQL))
Where upper([SERVERURL]) like '%' + upper(@P_SQL) + '%'
Update SRSSERVERS Set [AXAPTAREPORTFOLDER] = REPLACE(upper([AXAPTAREPORTFOLDER]), upper(@P_SQL), upper(@SQL))
Where upper([AXAPTAREPORTFOLDER]) like '%' + upper(@P_SQL) + '%'
Update SRSSERVERS Set [REPORTMANAGERURL] = REPLACE(upper([REPORTMANAGERURL]), upper(@P_SQL), upper(@SQL))
Where upper([REPORTMANAGERURL]) like '%' + upper(@P_SQL) + '%'
------------------------------------------------------------------------------------


Update [SYSCLIENTSESSIONS] Set CLIENTCOMPUTER = REPLACE(upper(CLIENTCOMPUTER), upper(@P_AOS), upper(@AOS))
Where upper(CLIENTCOMPUTER) like '%' + upper(@P_AOS) + '%'
Update [SYSCLIENTSESSIONS] Set CLIENTCOMPUTER = REPLACE(upper(CLIENTCOMPUTER), upper(@P_SP), upper(@SP))
Where upper(CLIENTCOMPUTER) like '%' + upper(@P_SP) + '%'
Update [SYSCLIENTSESSIONS] Set CLIENTCOMPUTER = REPLACE(upper(CLIENTCOMPUTER), upper(@P_SQL), upper(@SQL))
Where upper(CLIENTCOMPUTER) like '%' + upper(@P_SQL) + '%'
------------------------------------------------------------------------------------


Update [SYSEMAILSMTPPASSWORD] Set [AOSID] = REPLACE(upper([AOSID]), upper(@P_AOS), upper(@AOS))
Where upper([AOSID]) like '%' + upper(@P_AOS) + '%'
------------------------------------------------------------------------------------


Update [SYSLASTVALUE] Set [DESIGNNAME] = REPLACE(upper([DESIGNNAME]), upper(@P_AOS), upper(@AOS))
Where upper([DESIGNNAME]) like '%' + upper(@P_AOS) + '%'
Update [SYSLASTVALUE] Set [DESIGNNAME] = REPLACE(upper([DESIGNNAME]), upper(@P_SQL), upper(@SQL))
Where upper([DESIGNNAME]) like '%' + upper(@P_SQL) + '%'
Update [SYSLASTVALUE] Set [DESIGNNAME] = REPLACE(upper([DESIGNNAME]), upper(@P_SP), upper(@SP))
Where upper([DESIGNNAME]) like '%' + upper(@P_SP) + '%'
------------------------------------------------------------------------------------


Update SYSSERVERCONFIG Set [SERVERID] = REPLACE(upper([SERVERID]), upper(@P_AOS), upper(@AOS))
Where upper([SERVERID]) like '%' + upper(@P_AOS) + '%'
------------------------------------------------------------------------------------


Update SYSSERVERSESSIONS Set [AOSID] = REPLACE(upper([AOSID]), upper(@P_AOS), upper(@AOS))
Where upper([AOSID]) like '%' + upper(@P_AOS) + '%'
------------------------------------------------------------------------------------


Update SYSUSERLOG Set [COMPUTERNAME] = REPLACE(upper([COMPUTERNAME]), upper(@P_AOS), upper(@AOS))
Where upper([COMPUTERNAME]) like '%' + upper(@P_AOS) + '%'
Update SYSUSERLOG Set [COMPUTERNAME] = REPLACE(upper([COMPUTERNAME]), upper(@P_SP), upper(@SP))
Where upper([COMPUTERNAME]) like '%' + upper(@P_SP) + '%'
Update SYSUSERLOG Set [COMPUTERNAME] = REPLACE(upper([COMPUTERNAME]), upper(@P_SQL), upper(@SQL))
Where upper([COMPUTERNAME]) like '%' + upper(@P_SQL) + '%'
------------------------------------------------------------------------------------


Update SYSWORKFLOWPARAMETERS Set [SITEURL] = REPLACE(upper([SITEURL]), upper(@P_SP), upper(@SP))
Where upper([SITEURL]) like '%' + upper(@P_SP) + '%'
------------------------------------------------------------------------------------



Re-Syncing Enterprise Portal

Unfortunately, by restoring a Production Database into Development/Test, it appears to break connectivity with Enterprise Portal. As of now, the only way I have figured out how to resolve this issue is by doing the following:

Verify Your SharePoint Administrative Access

1. START > Administrative Tools > SharePoint Central Administration
2. Go to Application Management > SharePoint Site Management > Site Collection Administrations
3. Click the Site Collection drop down > Change Site Collection
4. Click the "/sites/DynamicsAx" URL and click OK
5. Make sure your name is listed as either the Primary or Secondary site collection administrator. NOTE: if you are not the Primary or Secondary site collection administrator, you will get an "Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)" error when attempting to install Enterprise Portal

Reinstall Enterprise Portal

1. Load the Setup.exe (run as Administrator) from your Dynamics AX 2009 installation path
2. Select “Add or modify components”
3. Select “Role Centers and Enterprise Portal” and click Next
4. Verify that the “Domain\user name” is correct for your environment (if not make the change to the NETWORKALIAS column in the SYSBCPROXYUSERACCOUNT table in the AX database, and then restart the setup) and enter in the password for the account
5. Select the existing Dynamics AX EP website
6. Check the “Configure for Windows SharePoint Services” checkbox
7. Check the “Create Web site” checkbox
8. Verify the Site URL
9. Click Next
10. Click “Yes” to allow the setup to delete and recreate
11. Click Install

Run a command prompt (run as Administrator) and enter the following command lines:

1. cd c:\Program Files\Microsoft Dynamics AX\50\Setup
2. AxUpdatePortal –updateWebSites –verbose > “C:\EPUpdate.log”
3. iisreset

Enterprise Portal should once again be functioning. At this point you will need to go into your EP site, and add your user permissions again.