List of table in AX 2012 should be backuped while restoring the database

List of the table in AX 2012 should be taken backup while restoring the database
Like from Production DB to UAT DB.
Or might be Production DB to SIT DB.
Question:
One fine day I got a call from my friend asking to have an AX Production Copy to TEST systems.
I guided him, and then he asked me to create a document or some kind of blog, Solution which I gave was a below:
Solution:
Bo before the solution, I would like to tell you that, normally PROD is different than the TEST, since the objective was to have PROD data in TEST to troubleshoot PROD issues.
Normally people take backup of PROD DB's and restore this PROD DB's to TEST DB's and rebuild the SSRS and EP and other settings.
I normally follow:

Steps:

1. Backup DB's PROD
2. Save the .bak files in some location or local drive if you have space.
3. Copy PROD DB's .bak files to TEST server or its local Drive.
4. Export the list of tables (Important Table Names:) as below in the blog!
5. Now once you have the backup's of the tables, please go for 6 steps.
6. Restore the PROD DB's backup on TEST DB's.
7. On top of this import the list of tables exported in step 4.

Important Table Names:

You may add more table name or drop some which are not needed.

Might be cases like ISV bottom-line or Cincom.

For DMF setting export DMFParameters this table as well.

How to restore the DB Form Source to 

Destination in Microsoft Dynamics AX 2012 R3

Objective: Main Objective is to save the system tables/settings

Like EP, BI, SSRS, User Info and Etc…

Step 1: Open Source Environment Database via SSMS

Step 2: Create a Local folder to store the DB files

Step 3: Backup Source Environment Database

• ·         Stop AOS (Backup can also be taken without stopping the AOS)
• ·         Perform full database backup via SQL

Step 4: Backup Destination Environment Database

• ·         Stop AOS (Backup can also be taken without stopping the AOS)
• ·         Perform full database backup via SQL

Step 5: Backup System Configuration Tables in the destination environment

Select the database MicrosoftDynamicsAX

Select > Tasks > Export data

Follow this:

Select the Server Name here in our case LocalServer

 Choose Destination Database (create the new database) click New>

Type database name and click OK.

This database will contain the backup of the tables which are needed to be backed up for the destination environment.

Click OK>

Click Next>

Click Next>

Select all the tables needed> Use Page down Key from your keyboard.

Select all the system tables from the list below and then click Next>

Full list of system tables for AX2012 R3 (include or exclude user-related tables – marked as Optional – depending on whether you want to keep the user setup or not):

Click Next and Finish

Note: 11 Total may not be as per your need.

Step 6: Restore source database backup to the destination database

Database restore from a backup (WITH REPLACE)

Note: ensure data and log file names are correct.

Step 7: Import data from the new database created in Step 5

Select the database MicrosoftDynamicsAX and click Tasks > Import data

Select the Server Name as LocalServer.

Select System Parameter form the Database Drop down and click Next>

Choose destination (select destination environment database) and click Next>

Click Next>

Just Check the Database Name is correct or not.

Next>

Select the Tables from top> all will be selected automatically>

Select Edit Mappings> Check Delete option> Click Ok.

Click Next> Next> Finish!

Click Close

Step 8: Start and synchronise

·         Start the AOS

·         Open the AOT Synchronise dictionary in the destination environment.

Happy DAX Infra!

Thanks & Regards,

Umesh Pandit

B.Sc.,MCA,MCP

Microsoft Dynamics AX Consultant

http://msdynamicsaxtips.blogspot.in/

Skype ID: pandit.umesh