Reverse and forward engineering databases in Visio 2010

A great built-in feature in Microsoft Office Visio 2010 is the ‘Reverse Engineer’ feature. With this feature you can create a database model from an existing database or a Microsoft Excel worksheet. This makes it very easy to document the structure of a database, redistribute or publish It on Microsoft SharePoint.

Unfortunately Microsoft has removed the ability to forward engineer the database model to a database, which did exist in Microsoft Visio 2003 Enterprise Architect-edition.

Fortunately Alberto Ferrari created an Visio 2010 plugin that can forward engineer an data model to a T-SQL script. The Visio Forward Engineer project is on CodePlex and can be found here (CodePlex is DEAD, you can find alternatives here).

In this blog post I will demonstrate how to reverse engineer a database from Microsoft SQL server, and how to forward engineer a database model diagram back to a Microsoft SQL server.

How to reverse engineer a database from Microsoft SQL to Microsoft Visio 2010

Start Microsoft Visio 2010 and create a new document. Select the template ‘Database Model Diagram’ from the template category ‘Software and Database’ and click ‘Create’.

Select the ‘Database’ tab on the ribbon and click on ‘Reverse Engineer’.

In the ‘Reverse Engineering Wizard’ select the ‘Microsoft SQL server’ driver and add a Data source with the ‘New’ button.

In the ‘Create new data source’ wizard select the ‘SQL Server Native Client x.x’  and click specify the ‘User data source’.

In the ‘Create a New Data Source to SQL Server’ wizard you can create a connection to the SQL database you wish to reverse engineer.

When you get back in the ‘Reverse Engineer Wizard’ you can select the new data source. After supplying the credentials you’ll be able to specify the objects you wish to reverse engineer. (In the demo I’ve chosen to reverse engineer all available objects). Next select the ‘Yes, add the shapes to the current page’ so the shapes are drawn on the page.

You now have successfully reverse engineered a SQL database to a database model diagram in Microsoft Visio 2010.

How to forward engineer a database from Microsoft Visio 2010 to Microsoft SQL

Please first install the Visio Forward Engineer Addin for Visio 2010 before starting Microsoft Visio 20120.

Open the database model diagram in Microsoft Visio 2010 select the ‘Forward Engineer’ tab on the ribbon. Click on ‘Forward Engineer’.

In the ‘Forward Engineer to SQL Server’ wizard specify the name of the new database. In the demo I’ve specified IngmarVerheijDB as my new database name. Select the ‘Generate DROP DATABASE and the ‘Generate CREATE DATABASE’ checkbox to make sure the database is always created, even if it already exists.

If you checked the ‘Open generated script when finished’ the created script will we opened in the ‘Microsoft SQL Server Management Studio’ (if installed).

REMARK

If the database does not yet exist, the following lines needs to be removed (where [IngmarVerheijDB]  needs to be replaced with you database name).

ALTER DATABASE [IngmarVerheijDB] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO