How to schedule an automatic reverse-engineer from a database into a data model?

by Oct 25, 2022

This blog post describes how you can run a macro to reverse engineer a database and call it from the Windows Task Scheduler.

When you use ER/Studio Data Architect, you can easily create a new data model based on an existing database by using the Reverse Engineer Wizard from the User Interface.

Reverse Engineer Wizard

In the last step of the wizard, you can save the settings to a file for later reuse.

Reverse Engineer Wizard

If you have previously saved such a file, you can load it with the Wizard Quick Launch to get back all your previous settings.

Wizard Quick Launch

So, it’s very easy to configure the reverse engineer once, and to manually replay it when we need it.

Would it be possible to automatize this reverse engineer? Not directly, but we can run an ER/Studio Data Architect macro from command line, and this macro could reverse engineer our database. So here is the macro I coded to achieve it:

'TITLE:  wReverseEng.BAS
'DESCRIPTION:  This macro reverse engineers a DB into a timestamped data model file

Const PROJECT_FILENAME = "<YOUR_PATH><FILENAME>"
Const QUICK_LAUNCH_PATH = "%appdata%\Idera\ERStudio\XML\<QUICK_LAUNCH_FILENAME>.rvo"
'ER/Studio Variables
Dim rvo As ReverseEngineer
Dim diag As Diagram

Dim sProjectPath$

Option Explicit

Sub Main
	Debug.Clear

	' Timestamp & extension added
	sProjectPath = PROJECT_FILENAME & Format(Now, "_yyyy-mm-dd_HH-mm-ss") & ".dm1"

	' Create the Reverse Engineer Object
	Set rvo = DiagramManager.ReverseEngineerObject

	' Load the settings from the Quick Launch save
	rvo.QuickLaunchFile = QUICK_LAUNCH_PATH

	' Reverse engineer the database 
	Set diag = rvo.DoReverseEngineer

	' Save the project
	diag.SaveFile(sProjectPath)

	' Close the newly created project
	DiagramManager.CloseDiagram(diag.FileName)
End Sub

To use the above macro, you need to update the constants to match the path to your DM1 file and to your Quick Launch settings.

Then to automatize it, we can schedule a call to this macro using the Windows Task Scheduler.

Launch the Task Scheduler and Create a Basic Task…

Create Basic Task

Create a basic task

Define when you want the task to start:

Trigger

Choose the Action, Start a program:

Start a program

Click the Browse… button and select the ER/Studio Data Architect application. By default: “C:\Program Files\IDERA\ERStudio Data Architect <VERSION>\ ERSTUDIO.exe”

Add arguments: -m “<PATH TO THE ABOVE EDITED MACRO FILE SAVED IN YOUR ENVIRONMENT>”

Add arguments

Review the summary and click the Finish button to create your scheduled task.

Review task

Finally, the task will run when it’s scheduled and your project files will be generated.

Task is running

File created

Summary

In this blog post, we used: