How to Export an SQL DDL File From SQL Server Management Studio for Reverse Engineering

Keep your database secure and up to date by deconstructing your database to extract and update its design.

In this article, we show you how to export an SQL DDL file from SQL Server Management Studio, that is, export your SQL Server database in the form of an SQL script, and import it into Vertabelo. Also, you'll learn about the Vertabelo Reverse Engineering tool to export your entire database into an XML file and import it into Vertabelo for further processing.

Reverse engineering deconstructs your database for extracting and updating its design. Think about how often your phone or laptop asks to update its operating system. New features or fixes are developed every day, so it needs to be updated regularly. Your database also requires regular remodels or updates to keep it secure and current.

Let’s get started.

Exporting an SQL DDL File From SQL Server Management Studio

We are going to use an SQL Server 2019 database. Our Zoo database is connected to an SQL Server Management Studio as shown below.

It contains the following data:

• 6 tables: Animal_Species, Food_Provider, Food_Type, Food_Type_Provider, Zoo_Animals, and Zoo_Employees.
• 2 views: Animals_Info and Food_Provider_Info.

Now, to export an SQL DDL file from SQL Server Management Studio for this entire database, right-click the database name and select Tasks followed by Generate Scripts.

A pop-up window guides you through the export process.

After clicking the Next button, you decide on the database objects to include in the SQL script.

You can choose either the entire database…

…or specific database objects (for example, some of the tables, views, schemas, stored procedures, etc.).

Here, we are going with the first option: export the entire Zoo database.

Next, we are presented with a range of options on how to export our script.

You have the following options:

• Save as notebook: This option exports your database in the .ipynb file format that includes metadata.
• Save as script file: This option exports your database in the standard .sql file format that can be imported into Vertabelo.
• Save to clipboard: This option exports your database to your clipboard for you to paste it anywhere using the Ctrl-V command.
• Open in new query window: This option opens the script in a new query window within SQL Server Management Studio.

Since we plan to import it into Vertabelo soon after, we choose the second option and export it as a .sql file.

And here is a summary of our selected options.

Now, we are ready to export.

And it’s done! The file script.sql is in your chosen location.

Importing an Existing Database Schema Into Vertabelo

Now that we have exported our Zoo database to the file script.sql, we can import it into Vertabelo.

While Creating a Physical Data Model

One way is to create a database model from the SQL script. To do so, we import the SQL script while generating a physical data model in Vertabelo. We start by creating a new document as below.

Next, we choose Physical data model.

Then, give a name to your physical data model, choose Microsoft SQL Server as the database engine, and specify the appropriate version.

And the most important part: the content. We are importing an SQL script, so choose the option From SQL and upload the file.

Note that you have to click the button Import SQL before you can proceed.

The model has been imported; however, some SQL parsing problems have been encountered. When generating an SQL script from a Microsoft SQL Server database, the database engine uses some database-specific keywords Vertabelo doesn’t recognize.

But don’t worry! Your database has been imported into Vertabelo. You see it once you click the Start modeling button.

You may encounter problems when importing views. Here, the views have been imported without the columns specified. In this case, you may add the columns manually as shown below.

Add the columns manually by selecting the view and navigating to the Columns section in the right-hand pane.

Into an Existing Physical Data Model

It is also possible to import an SQL script file into an already existing physical data model. You can do so by clicking on the name of your model and choosing the option Import from SQL.

This article has the details of such an import process.

Working With an XML File in Vertabelo

Instead of an SQL script, we can work with the XML file format. To demonstrate, we export our Zoo database as an XML file and then import it into Vertabelo.

Using the Vertabelo Reverse Engineering tool, you export your database in the XML file format. Then, importing an XML file into Vertabelo is analogous to importing an SQL file.

This article has the details on how to work with the Vertabelo Reverse Engineering tool. Let’s go through the steps using our Zoo database.

Exporting an XML File Using the Vertabelo Reverse Engineering Tool

First things first! Create and test a TCP/IP connection from the Vertabelo Reverse Engineering tool to your database.

Once the connection is successful, you can generate the XML file.

Click the Generate button and wait for the pop-up window to tell you that an XML file of your database has been generated successfully.

Importing an XML File Into Vertabelo

We now upload our XML file into Vertabelo.

Again, we create a physical data model, give it a name, and choose the database engine and the content type. Then we upload and import the XML file. And we're ready to start modeling!

Everything is imported correctly without any errors or warnings – even the views! You can start working with the model right away.

Working With the Imported Database in Vertabelo

You can customize the look of your database diagram to make it more readable and understandable for anyone who works with it. Since our database has been successfully imported into Vertabelo, let’s see how to improve the readability of our database diagram.

Rearranging and Resizing Database Objects

Rearrange the tables and views and adjust their sizes to see them more clearly.

Adding Colors

To help distinguish between different table groups, color the tables as shown below.

This article has more about how to color the tables.

Adding Subject Areas and Notes

You can add subject areas and notes to ensure your database design is well understood.

Subject areas and notes can be added on the left-hand pane. Right-click Text notes or Subject areas followed by Add note or Add area.

Shortcut Tables

Our Zoo database diagram looks much better with the colors, subject areas, and notes. For a larger database, make use of the shortcut tables in Vertabelo. This feature lets you create a copy of any table and place it wherever it helps improve the readability of your database. Read more here on shortcut tables.

If you have a very large database that results in an enormous database diagram, check out this guide on how to organize it efficiently.

I've Imported an Existing Database Schema Into Vertabelo; What’s Next?

You now know all you need to reverse engineer your database:

1. Get the database files, either as an SQL script or as an XML file.
2. Upload it to Vertabelo.

The next step is to update or remodel the database. Make the changes to your database diagram in Vertabelo. Then, generate a new SQL script or a new XML file to update your database.

Go ahead and try out the Vertabelo Reverse Engineering tool to keep your databases current!