CODEWITHSUNDEEP
sql-serveraxaptamicrosoft-dynamics
Philip
Philip

Reputation: 2628

AX 2012 - Restore Production over Non Production

I have an AX 2012 database in Production that I need to restore over UAT. I've never done this before and am wondering on the steps involved.

I have found many blog articles but they all seems to differ on the steps and I want to avoid any accidents being made.

http://ajit-dynamicsax.blogspot.com/2012/08/ax-2012-database-restore-without.html

https://dynamicsuser.net/ax/f/developers/49023/taking-a-copy-of-live-production-into-test

http://theaxexperience.blogspot.com/2013/06/copying-production-dynamics-ax-2012-or.html

Does anyone manage a AX 2012 environment and know how to do this safely? I know how to do a backup/restore, but am more asking as to the application specific steps needed after the restore process.

Upvotes: 0

Views: 2102

Answers (2)

Alex Kwitny
Alex Kwitny

Reputation: 11544

To add to what FH-Inway has said, if you do a SQL restore, first take a UAT backup and then the bare minimum you'll need to do is:

UPDATE SYSSQMSETTINGS
SET GLOBALGUID = '00000000-0000-0000-0000-000000000000'

DELETE
FROM SYSCLIENTSESSIONS

DELETE
FROM SYSSERVERSESSIONS

DELETE
FROM SYSSERVERCONFIG

You'll also need to decide what to do with BatchJob and Batch tables...meaning you probably want to update everything to a hold status.

Then there are environment specific parameters (Prod vs UAT) that you'll need to update, such as reporting server URL's, email (SMTP) parameters perhaps, if you have any 3rd party ISVs that connect to external services (prod URL vs non-prod URL).

You may need to change DB permissions too, as your SQL service account may be different.

It's a very iterative process. What's good to know is that if you screw it up, you can just re-try and fix parameters. You're essentially completely replacing the UAT environment each time, so most anything you could do wrong is of little concern.

The main concern is services that are external to AX! The absolute last thing you want to do is have users in your UAT system processing fake orders that are accidentally connected to a 3rd party shopping cart (for example) and they're charging real customer credit cards.

Or if you have a data warehouse or some internal database that UAT is now pointing to Production, that is inserting records into.

Upvotes: 2

FH-Inway
FH-Inway

Reputation: 5107

This question is a bit broad for Stack Overflow, so I'm gonna limit my answer to the scope of what data needs to be considered after a data transfer. Note that there can be a lot of other steps involved. Although a bit outdated, Moving between Microsoft Dynamics AX 2012 environments should be a good starting point. But if you have never done this before, I strongly suggest doing this with someone who has.

Although it is (and probably will be forever) in beta and has not been updated in quite some time, take a look at the Test Data Transfer Tool. It comes with several Exclude*.txt files that can give you a good idea which data in the standard AX 2012 database Microsoft considers environment specific (and therefore excludes it from the data transfer). Even if you don't end up using the tool, these files provide a good starting point on which data should be considered after a backup restore.

If you don't use the tool, SQL scripts are the way to go. The Data exports/imports functionality mentioned in some of the links you listed should not be used. It does not handle surrogate key relationships between tables and there may be also issues with container fields.

In my experience, data restores are also always very project specific and it usually takes a few iterations until it can be done without issues. I highly recommend a checklist and/or thorough documentation of the process.

Upvotes: 2

Related Questions