AX DB Restore Scripts – Full Script

Just over a month ago I embarked on a series of posts on SQL scripts that I use when restoring Dynamics AX databases from one environment to another. We have covered topics including server setups, general configurations as well as data cleans to get your new environment up and smoothly running as quickly as possible. We are now at the end of the series and I trust it has been helpful to you in structuring your scripts.

I’d just like to remind you that these posts and scripts are not meant to be used as is but rather to serve as a guide to adapt for your specific scenario and to prompt your own thinking as to what you will need.

That said you can download my templated script here or view it below (download)

Declare @AOS varchar(30) = '[AOSID]' --must be in the format '01@SEVERNAME'
---Reporting Services---
Declare @REPORTINSTANCE varchar(50) = 'AX'
Declare @REPORTMANAGERURL varchar(100) = 'http://[DESTINATIONSERVERNAME]/Reports_AX'
Declare @REPORTSERVERURL varchar(100) = 'http://[DESTINATIONSERVERNAME]/ReportServer_AX'
Declare @REPORTCONFIGURATIONID varchar(100) = '[UNIQUECONFIGID]'
Declare @REPORTSERVERID varchar(15) = '[DESTINATIONSERVERNAME]'
Declare @REPORTFOLDER varchar(20) = 'DynamicsAX'
Declare @REPORTDESCRIPTION varchar(30) = 'Dev SSRS';

---SSAS Services---
Declare @SSASSERVERNAME varchar(20) = '[YOURSERVERNAME]\AX'
Declare @SSASDESCRIPTION varchar(30) = 'Dev SSAS'; -- Description of the server configuration

---BC Proxy Details ---
Declare @BCPSID varchar(50) = '[BCPROXY_SID]'
Declare @BCPDOMAIN varchar(50) = '[yourdomain]'
Declare @BCPALIAS varchar(50) = '[bcproxyalias]'
---Service Accounts ---
Declare @WFEXECUTIONACCOUNT varchar(20) = 'wfexc'
Declare @PROJSYNCACCOUNT varchar(20) = 'syncex'
---Help Server URL---
Declare @helpserver varchar(200) = 'http://[YOURSERVER]/DynamicsAX6HelpServer/HelpService.svc'

---Outgoing Email Settings---
Declare @SMTP_SERVER varchar(100) = 'smtp.mydomain.com' --Your SMTP Server
Declare @SMTP_PORT int = 25
---DMF Folder Settings ---
Declare @DMFFolder varchar(100) = '\\[YOUR FILE SERVER]\AX import\ '

---Email Template Settings---
Declare @EMAIL_TEMPLATE_NAME varchar(50) = 'Dynamics AX Workflow QA - TESTING'
Declare @EMAIL_TEMPLATE_ADDRESS varchar(50) = 'workflowqa@mydomain.com'
---Email Address Clearing Settings---
DECLARE @ExclUserTable TABLE (id varchar(10))
insert into @ExclUserTable values ('userid1'), ('userid2')

--List of users separated by | to keep enabled, while disabling all others 
Declare @ENABLE_USERS NVarchar(max) = '|Admin|TIM|'
--List of users separated by | to disable, while keeping all the rest enabled all others  
--Declare @DISABLE_USERS NVarchar(max) = '|BOB|JANE|'

--*****BEGIN UPDATES*******---

---Update AOS Config---
delete from SYSSERVERCONFIG where RecId not in (select min(recId) from SYSSERVERCONFIG)
update SYSSERVERCONFIG set serverid=@AOS, ENABLEBATCH=1 
	where serverid != @AOS -- Optional if you want to see the "affected row count" after execution.

---Update Batch Servers---
delete from BATCHSERVERGROUP where RecId not in (select min(recId) from BATCHSERVERGROUP group by GROUPID)
update BATCHSERVERGROUP set SERVERID=@AOS 
	where serverid != @AOS -- Optional to see "affected row count"
update batchjob set batchjob.status=4 where batchjob.CAPTION = '[BATCHJOBNAME]'
update batch set batch.STATUS=4 from batch inner join BATCHJOB on BATCHJOBID=BATCHJOB.RECID AND batchjob.CAPTION = '[BATCHJOBNAME]'

---Update Reporting Services---
delete from SRSSERVERS where RecId not in (select min(recId) from SRSSERVERS)
update SRSSERVERS set 
    SERVERID=@REPORTSERVERID, 
    SERVERURL=@REPORTSERVERURL, 
    AXAPTAREPORTFOLDER=@REPORTFOLDER,
    REPORTMANAGERURL=@REPORTMANAGERURL, 
    SERVERINSTANCE=@REPORTINSTANCE, 
    AOSID=@AOS,
    CONFIGURATIONID=@REPORTCONFIGURATIONID, 
    DESCRIPTION=@REPORTDESCRIPTION
where SERVERID != @REPORTSERVERID -- Optional if you want to see the "affected row count" after execution.

---Update SSAS Services---
delete from BIAnalysisServer where RecId not in (select min(recId) from BIAnalysisServer)
update BIAnalysisServer set 
    SERVERNAME=@SSASSERVERNAME, 
    DESCRIPTION=@SSASDESCRIPTION,
	ISDEFAULT = 1
WHERE SERVERNAME <> @SSASSERVERNAME -- Optional where clause if you want to see the "affected rows

---Set BCPRoxy Account---
update SYSBCPROXYUSERACCOUNT set SID=@BCPSID, NETWORKDOMAIN=@BCPDOMAIN, NETWORKALIAS= @BCPALIAS
where NETWORKALIAS != @BCPALIAS --optional to display affected rows.
---Set WF Execution Account---
update SYSWORKFLOWPARAMETERS set EXECUTIONUSERID=@WFEXECUTIONACCOUNT where EXECUTIONUSERID != @WFEXECUTIONACCOUNT
---Set Proj Sync Account---
update SYNCPARAMETERS set SYNCSERVICEUSER=@PROJSYNCACCOUNT where SyncServiceUser!= @PROJSYNCACCOUNT
---Set help server URL---
update SYSGLOBALCONFIGURATION set value=@helpserver where name='HelpServerLocation' and value != @helpserver

---Update Email Parameters---
Update SysEmailParameters set SMTPRELAYSERVERNAME = @SMTP_SERVER, @SMTP_PORT=@SMTP_PORT
---Update DMF Settings---
update DMFParameters set SHAREDFOLDERPATH = @DMFFolder 
where SHAREDFOLDERPATH != @DMFFolder --Optional to see affected rows

---Set BCPRoxy Account---
update SYSBCPROXYUSERACCOUNT set SID=@BCPSID, NETWORKDOMAIN=@BCPDOMAIN, NETWORKALIAS= @BCPALIAS
where NETWORKALIAS != @BCPALIAS --optional to display affected rows.
---Set WF Execution Account---
update SYSWORKFLOWPARAMETERS set EXECUTIONUSERID=@WFEXECUTIONACCOUNT where EXECUTIONUSERID != @WFEXECUTIONACCOUNT
---Set Proj Sync Account---
update SYNCPARAMETERS set SYNCSERVICEUSER=@PROJSYNCACCOUNT where SyncServiceUser!= @PROJSYNCACCOUNT
---Set help server URL---
update SYSGLOBALCONFIGURATION set value=@helpserver where name='HelpServerLocation' and value != @helpserver

---Update Email Templates---
update SYSEMAILTABLE set SENDERADDR = @EMAIL_TEMPLATE_ADDRESS, SENDERNAME = @EMAIL_TEMPLATE_NAME where SENDERADDR!=@EMAIL_TEMPLATE_ADDRESS OR  SENDERNAME!=@EMAIL_TEMPLATE_NAME;
update SYSEMAILSYSTEMTABLE set SENDERADDR = @EMAIL_TEMPLATE_ADDRESS, SENDERNAME = @EMAIL_TEMPLATE_NAME where SENDERADDR!=@EMAIL_TEMPLATE_ADDRESS OR  SENDERNAME!=@EMAIL_TEMPLATE_NAME;
---Update User Email Addresses---
update sysuserinfo set sysuserinfo.EMAIL = '' where sysuserInfo.ID not in (select id from @ExclUserTable)

---Disable all users except for a specific set---
update userinfo set userinfo.enable=0 where  CharIndex('|'+ cast(ID as varchar) + '|' , @ENABLE_USERS) = 0
---Disable specific users---
---update userinfo set userinfo.enable=0 where  CharIndex('|'+ cast(ID as varchar) + '|' , @DISABLE_USERS) > 0

--Clean up server sessions
delete from SYSSERVERSESSIONS
--Clean up client sessions.
delete from SYSCLIENTSESSIONS

View Previous – AX DB Restore Scripts #8 – Disable users

Back to List

AX DB Restore Scripts #7 – Setting Email Templates and User Email Addresses

In my series of posts on SQL scripts I use when restoring Dynamics AX databases from one environment to another, we have so far covered various configuration and setting changes to Dynamics AX. Today I will move on to some suggested data changes that you may wish to consider making when doing database restores. These changes won’t affect the functioning of AX, but may prevent some awkward or confusing situations like clients or user’s receiving emails etc..

These data cleanup scripts are really meant as guidelines to help you in creating your own scripts, rather than a comprehensive set of “must-do” scripts.

The two changes for today are as follows

  1. Setting your email templates to clearly indicate that they originate from a testing system. It may be perfectly legitimate for users to receive emails from your testing or development environments, e.g. for testing workflows, however it is very helpful for the users to clearly see that they are viewing test data rather than production data. So setting the senders name and address to indicate “Development” will assist.
    Email templates are found under: Organisation administration -> Setup -> Email templates
    Email_Templates
    2. Setting user email addresses. We obviously don’t want to send emails to users who have no interest in our development systems so one may, depending on your needs, want to do one of the following.

    1. Clear all user’s email addresses, except for a specific subset.
    2. Set some or all user’s email addresses to a single address (e.g. You want emails to still be generated to see that they are working, but you would like to see the contents of them yourself and not expose the user to the test data)

These settings can be found under System Administration -> Users -> Options (ribbon button) ->  Email
Email_Address

The following SQL code will accomplish the above. I have parameterised the SQL for easier reuse or adjustment.

---Email Template Settings---Declare @WFAPPROVER_ADD varchar(50) = 'workflowqa@norplats.co.za'
Declare @EMAIL_TEMPLATE_NAME varchar(50) = 'Dynamics AX - DEVELOPMENT'
Declare @EMAIL_TEMPLATE_ADDRESS varchar(50) = 'dev@mydomain.com'

---Email Address Clearing Settings---
DECLARE @ExclUserTable TABLE (id varchar(10))
insert into @ExclUserTable values ('userid1'), ('userid2')

---Update Email Templates---
update SYSEMAILTABLE set SENDERADDR = @EMAIL_TEMPLATE_ADDRESS, SENDERNAME = @EMAIL_TEMPLATE_NAME where SENDERADDR!=@EMAIL_TEMPLATE_ADDRESS OR  SENDERNAME!=@EMAIL_TEMPLATE_NAME;
update SYSEMAILSYSTEMTABLE set SENDERADDR = @EMAIL_TEMPLATE_ADDRESS, SENDERNAME = @EMAIL_TEMPLATE_NAME where SENDERADDR!=@EMAIL_TEMPLATE_ADDRESS OR  SENDERNAME!=@EMAIL_TEMPLATE_NAME;
---Update User Email Addresses---
update sysuserinfo set sysuserinfo.EMAIL = '' where sysuserInfo.ID not in (select id from @ExclUserTable)

Note, the above example clears all user email addresses except for a specific set, you could easily adjust this to set them all to a specific address as follows:

update sysuserinfo set sysuserinfo.EMAIL = 'youraddress@mydomain.com'

 

I hope this assists you and will be useful in your management of your AX environments. Keep an eye out for new posts in this series!

View Previous – AX DB Restore Scripts #6 – SMTP Server and DMF Folder

View Next – AX DB Restore Scripts #8 – Disable users

Back to List

AX DB Restore Scripts #6 – SMTP Server and DMF Folder

Recently I started creating a list and a series of posts on SQL scripts I use when restoring Dynamics AX databases from one environment to another, typically from production to QA or development.

We have so far discussed a number of configurations that must be changed for the new system to operate effectively and starting yesterday we moved onto exploring more optional configurations.

Today we will cover two of these items namely re-configuring the outgoing SMTP server in AX and configuring the settings for the DMF working folder.

Note: In my experience the SMTP server will most likely not change between a production and development server, but you may need to reconfigure it if moving to a new domain etc. Similarly it will work to use the same DMF shared folder between PROD and DEV, however I would recommend changing this to keep the data 100% separate.

These two configurations are located in the Dynamics AX 2012 client under the following paths.
1. SMTP Server, accessed via System Administration -> Setup -> System -> E-mail parameters.EmailServer

2. Data import, export framework shared working directory. Accessed via “Data import export framework -> Setup -> Data import export framework parameters”
DMF

To update these 2 settings above we need to do the following:

1. Update the SysEmailParameters table to update the SMTP server name (and additional login parameters)
2. Update the DMFParameters table to reflect the new shared folder path.

The following SQL code will accomplish the above. I have parameterised the SQL for easier reuse or adjustment.

---Outgoing Email Settings---
Declare @SMTP_SERVER varchar(100) = 'smtp.mydomain.com' --Your SMTP Server
Declare @SMTP_PORT int = 25

---DMF Folder Settings ---
Declare @DMFFolder varchar(100) = '\\[YOUR FILE SERVER]\AX import\'

---Update Email Parameters---
Update SysEmailParameters set SMTPRELAYSERVERNAME = @SMTP_SERVER, @SMTP_PORT=@SMTP_PORT
---Update DMF Settings---
update DMFParameters set SHAREDFOLDERPATH = @DMFFolder 
where SHAREDFOLDERPATH != @DMFFolder --Optional to see affected rows

I hope this assists you and will be useful in your management of your AX environments.

View next – AX DB Restore Scripts #7 – Setting Email Templates and User Email Addresses

View Previous – AX DB Restore Scripts #5 – Configure Service Accounts and Help Server

Back to List

SQL Database Restore History

From time to time it is useful to determine the history of restores that a database has gone through. For example if you maintain a local copy of a client database / environment and would like to determine the exact date it was last refreshed. The following script is useful for getting this restore history from SQL:

SELECT [rs].[destination_database_name],
[rs].[restore_date],
[bs].[backup_start_date],
[bs].[backup_finish_date],
[bs].[database_name] as [source_database_name],
[bmf].[physical_device_name] as [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf
ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [rs].[restore_date] DESC

Sample Output

Screen Shot 2015-01-20 at 12.03.02 PM