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 (tables, views, stored procedures, etc.) and/or data. With dbForge Schema Compare, you can reverse-engineer a database using the following methods:

• Reverse-engineer a database using the Generate Scripts Wizard
• Reverse-engineer a database using the Create Scripts Folder or Snapshot Wizard
• Reverse-engineer a database using the Schema Synchronization Wizard

Reverse-engineer a database using the Generate Scripts Wizard

The Generate Scripts Wizard allows you to create a single SQL file containing objects and data.

Pros

• Export the generated .sql file to a lower SQL server version
• Generate a .sql script for a database schema with objects and/or data
• Save a .sql file as an encrypted zip archive

Cons

• Data cannot be encrypted
• The method is not suitable for large databases

To reverse-engineer a database using the Generate Scripts Wizard:

1. In the Database menu, select Tasks > Generate Scripts.

2. In the Generate Scripts Wizard that opens, on the General tab, do the following:

• Select a connection and a database
• Specify a path to the folder that will store the output file
• Enter the name of the output file
• Optional: Select the Append timestamp to the file name option to add the date and time to the name of the output file
• Optional: Select the Auto delete old files option to delete old files automatically and configure the parameters
• Optional: Select the Use compression option to create an archive and configure the parameters

Click Next.

3. On the Script content tab, select a database structure, data, and/or database objects for scripting, and click Next.

4. Optional: On the Options tab, select options that define how the script generation should be processed, and click Next.

5. Optional: On the Errors handling tab, set up processing behavior of errors and logging options, and click Generate.

After the script generation is completed, you can open the output file in the editor by selecting the Open script option. Then, click Finish to close the wizard.

Reverse-engineer a database using the Create Scripts Folder or Snapshot Wizard

The Create Scripts Folder or Snapshot Wizard allows you to import database objects and data to separate SQL files.

Create a database scripts folder using the Create Scripts Folder or Snapshot Wizard

A scripts folder is a set of scripts representing a database schema and data.

Peculiarities

• The scripts folder is supported only in the Professional edition of dbForge Schema Compare for SQL Server

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, a snapshot, or another scripts folder
• Decrypt encrypted objects

Cons

• The Decrypt encrypted objects option may cause poor performance

1. In the Database menu, select Tasks > Create Scripts Folder or Snapshot.

2. In the Create Scripts Folder or Snapshot wizard that opens, select the Database source type, specify a server connection, and select a database. If you want to decipher database encrypted objects, select the Decrypt encrypted objects option.

3. Select Scripts Folder as the destination type. Set a path to the empty folder that will contain SQL scripts for objects and data. To customize a structure for the script folder, click Scripts Folder Structure, select the types of objects you want to script, and then click OK. Optionally, select the check box to Include data.

4. Click Create.

Create a database snapshot file using the Create Scripts Folder or Snapshot Wizard

A snapshot is an .xml file containing information about the database structure without table data.

Pros

• Generate the database schema and data to separate files
• Build a snapshot from a database, scripts folder, or another snapshot
• Decrypt encrypted objects
• Save a snapshot as a zip archive

Cons

• The Decrypt encrypted objects option may cause poor performance
• A snapshot contains only the information about the database structure without table data
• Snapshots cannot be modified
• Only the dbForge tools can read snapshots to restore database structures

1. On the Database menu, select Tasks > Create Scripts Folder or Snapshot.

2. In the Create Scripts Folder or Snapshot wizard that opens, select the Database source type, specify a server connection, and select a database. If you want to decipher database encrypted objects, select the Decrypt encrypted objects option.

3. Select Snapshot as the destination type. Specify a name for the output file. Optionally, select the Compress check box to compress the output file.

4. Click Create.

Reverse-engineer a database using the Schema Synchronization Wizard

The Schema Synchronization Wizard allows you to import database objects, data, or objects by names either to the single or separate .sql files.

Pros

• Generate scripts for objects and/or data either to a single or separate .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
• A separate file or files in the scripts folder contain only the information about the database structure without table data; if you want to include data in the scripts folder, use dbForge Data Compare

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

1. On the standard toolbar, click New Schema Comparison.

2. In the New Schema Comparison wizard, select a source database and a connection, and an empty target scripts folder and a connection. Then, click Next.

3. Optional: On the Options tab, select schema comparison options and click Compare.

4. In a SQL document that opens, view the schema comparison results and click Synchronize objects to the target database.

5. In the Schema Synchronization Wizard that opens, on the Output tab, 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 create separate .sql files for each database object. Database 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.

6. To launch the synchronization, click Synchronize.