There’s a few things you can do when undertaking your data refresh into a non-production environment one of them is modifying your batch jobs to ‘Withhold’ status ensuring they won’t start to run when you after you start up your AOS’s and then configure your batch server settings. After the script has been ran you can set the status of jobs you want to run back to ‘Waiting’ status at a later point during the data refresh process, happy in the knowledge that you know no batch jobs are going to start emailing customers or update/talk to other external systems.

SQL Script:

-- Change to correct DB
 USE <Your Ax Database>
-- Set all Batch Jobs to Withhold status
 UPDATE BatchJob
 SET status = 0

You could also have a scenario where only certain batch jobs should be set to Withhold status, you could do this by adding the batch jobs you want to continue to run into a table and then only update the ones not in that table to Withhold. An example script to do this can be found below:

Pre-Requisites: Create a table with one data field called ‘Caption’, populate table with batch job names that you want to continue to run in your non-production environment (e.g. not be at withhold status)

SQL Script:

-- Change to correct DB
USE <Your Ax Database>

UPDATE BatchJob
SET status = 0
WHERE
Caption NOT IN (SELECT Caption FROM <the table name created in pre-requisites>)

Disclaimer: Ensure any batch jobs you add to this table don’t have other parameters that need to be changed prior to them running again in the non-production environment.

A few simple scripts to run when building a 2009 non-production environment from your 2009 production database to remove email addresses from the ‘CustTable’, ‘VendTable’ and ‘Address’ table this will ensure no emails can be sent in error to your customers or vendors from your non-production environment.

-- Change to correct DB
USE <Your Ax Database>

UPDATE Address
SET EMAIL = ''
WHERE
EMAIL != ''

UPDATE VendTable
SET EMAIL = ''
WHERE
EMAIL != ''

UPDATE CustTable
SET EMAIL = ''
WHERE
EMAIL != ''

DISCLAIMER: You may need to add additional tables/fields for any customizations you may be using.

As part of updating your AX 2009 non-production environments I recommend running a script to remove user login data so you can see who is using your environment and it doesn’t cloud things with it having LIVE login data in there.

This script will delete user log data prior to the database creation date (which should be the restored date of the database if a RESTORE DATABASE WITH REPLACE was used)

The user log data can be viewed in AX2009 via ‘Administration > Inquiries > User Log’

Initially run this query to determine the AX DB creation date:

SELECT create_date
FROM
sys.databases
WHERE
name =  '<Your AX Database Name>'

Once you’ve verified the date is okay run the below script:

/* Delete User Login Data based upon Database Creation Date */
DECLARE @dbcreatedate DATETIME

/* Get Database Creation Date */
SELECT @dbcreatedate = create_date
from
sys.databases
WHERE name = '<Your AX Database Name>'

SELECT @dbcreatedate AS Database_Creation_Date

/* Delete User Login Data */
DELETE FROM SysUserLog
WHERE
CREATEDDATETIME < @dbcreatedate

Download  Delete-User-Login-Data-using-Database-Creation-Date (752 downloads)