How to reverse engineer databases

Reverse-engineering a database is to convert an existing database into a model that can be represented either by an ERD diagram or a schema containing database objects, such as tables, views, stored procedures, etc. along with their associated data. dbForge Schema Compare allows users to reverse-engineer a database using the following methods:

  • Create Scripts Folder wizard
  • Schema Synchronization Wizard

Reverse-engineer a database using the Create Scripts Folder wizard

The Create Scripts Folder wizard allows you to import database objects to separate SQL files. A scripts folder is a set of scripts representing a database schema and data.

Pros

  • Generate the database schema and data to separate files
  • Create a scripts folder from the command line
  • Generate a script file with all objects grouped into subfolders
  • Customize the default scripts folder structure and file name templates
  • Generate a scripts folder from a database or another scripts folder

To create a database scripts folder using the Create Scripts Folder wizard

1. On the ribbon, select Database > Create Scripts Folder to open the Create Scripts Folder wizard.

2. Select Database as a source type, a server connection, and a database from which the scripts folder will be created.

3. Select Scripts Folder as a destination type and set a path to the empty folder that will contain SQL scripts for objects.

To customize a structure for the scripts folder, click Scripts Folder Structure. In the window that opens, select the types of objects you want to script and click OK.

4. Click Create to create the scripts folder.

Create a scripts folder

Reverse-engineer a database using the Schema Synchronization Wizard

The Schema Synchronization Wizard allows you to import database objects to the single or individual .sql files.

Pros

  • Generate scripts for objects to the single or individual .sql files
  • Generate scripts for specific objects
  • Visualize data differences
  • Update the scripts folder after synchronization
  • Save a synchronization script to a file

Cons

  • Storage capacity of the local disk should cover the total size of the source and target databases and the generated script
  • Files in the scripts folder contain only the information about the database structure without table data. To include data in the scripts folder, use dbForge Data Compare

To reverse-engineer a database using the Schema Synchronization Wizard:

1. On the toolbar, click New Schema Comparison to open the New Schema Comparison wizard.

2. On the Source and Target page of the wizard, select the source database and server connection and a target scripts folder.

Set a source and target databases for comparison

3. Optional: On the Options page, set up schema comparison options.

4. To run schema comparison, click Compare.

5. In the comparison results grid that opens, view the schema comparison results and Sync icon Synchronize objects to the target database to open the Schema Synchronization Wizard.

View the schema comparison result

6. On the Output page of the wizard, select either of the following:

  • Save the script to a file to create a single .sql file with all database objects.
  • Update the scripts folder to generate individual .sql files for each database object. These objects will be grouped into categories. To customize a structure for the scripts folder, click Scripts Folder Structure, select the types of objects you want to script, and then click OK.

Select output options

7. To run the synchronization, click Synchronize.