Scenario 4: Data Backup

In this scenario, we're going to use DMT to backup data from a database.

Let's say you have an ecommerce system from which you want to backup data every day. For this example we'll limit the backup to 3 tables, but we could easily backup all the tables or even tables from several database databases in one go.

The backup we need is a compressed file containing all the tables' data.

The ecommerce system is a MySQL database and the tables we want to backup are:

  • Customer
  • Product
  • Order

Note: For our example, we chose a MySQL database but this would work the exact same way with any other database.

You can easily implement this scenario with DMT:

Scenario 4: Data Backup

Step 1: Create the Export Group and Jobs

  • Create an Export Group scheduled to run every day
    • Name: Backup Sales
    • Group Type: Schedule Group
    • Scheduling Frequency: Daily
  • In the group create a Job to export data from the Customer table
    • Name: Export Customer
    • Database Connection: Connection to the ecommerce database
    • Data File: Customers.txt
    • SQL Statement: Enter the SQL statement to export the required data

    • e.g.
      SELECT * FROM CUSTOMER WHERE ACTIVE = 'Y'
  • In the group create a Job to export data from the Product table
    • Name: Export Product
    • Database Connection: Connection to the ecommerce database
    • Data File: Products.txt
    • SQL Statement: Enter the SQL statement to export the required data

    • e.g.
      SELECT P.* FROM PRODUCT P, ORDER O WHERE O.PROD_ID = P.ID
  • In the group create a Job to export data from the Order table
    • Name: Export Order
    • Database Connection: Connection to the ecommerce database
    • Data File: Orders.txt
    • SQL Statement: Enter the SQL statement to export the required data

    • e.g.
      SELECT * FROM ORDER

Note: Scheduling parameters are not required for the jobs because they belong to a Schedule Group (i.e. they will run automatically when the group is run).

Once the export group and jobs are created, you can validate them to make sure they are error free and ready to run (right click on the Group then "Validate Group").

Step 2: Create the Group Post Process

Now that the group is created, we need to place all the generated data files into one compressed file. To achieve this we're going to use a Post Process on the group to call a winzip command line.

  • Add a Post Process to the group
    • Type: Run Executable
    • Command Line: winzip32.exe
    • Arguments: -a "C:\DMT\Data Files\Backup.zip" "C:\DMT\Data Files\Customers.txt" "C:\DMT\Data Files\Products.txt" "C:\DMT\Data Files\Orders.txt"
    • Wait until process terminates: Yes

Note: We could equally have chosen to compress each job's data file individually using a post process for each job.

That's it! When you run the group, the 3 jobs will run sequentially each producing a data file. Then the group post process will zip these 3 files into the backup file.