{"id":3204,"date":"2012-01-05T11:59:12","date_gmt":"2012-01-05T09:59:12","guid":{"rendered":"https:\/\/ingmarverheij.com\/?p=3204"},"modified":"2019-12-24T10:08:58","modified_gmt":"2019-12-24T09:08:58","slug":"reverse-and-forward-engineering-databases-in-visio-2010","status":"publish","type":"post","link":"https:\/\/ingmarverheij.com\/en\/reverse-and-forward-engineering-databases-in-visio-2010\/","title":{"rendered":"Reverse and forward engineering databases in Visio 2010"},"content":{"rendered":"

\"\"<\/p>\n

A great built-in feature in Microsoft Office Visio 2010 is the \u2018Reverse Engineer\u2019 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.<\/p>\n

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.<\/p>\n

Fortunately Alberto Ferrari<\/a> 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<\/a> (CodePlex is DEAD, you can find alternatives here<\/a>).<\/p>\n

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.<\/p>\n

<\/p>\n

How to reverse engineer a database from Microsoft SQL to Microsoft Visio 2010<\/h2>\n

Start Microsoft Visio 2010 and create a new document. Select the template \u2018Database Model Diagram\u2019 from the template category \u2018Software and Database\u2019 and click \u2018Create\u2019.<\/p>\n

\"\"<\/a>\"\"<\/a>\"\"<\/a><\/p>\n

Select the \u2018Database\u2019 tab on the ribbon and click on \u2018Reverse Engineer\u2019.<\/p>\n

\"\"<\/a><\/p>\n

In the \u2018Reverse Engineering Wizard\u2019 select the \u2018Microsoft SQL server\u2019 driver and add a Data source with the \u2018New\u2019 button.<\/p>\n

\"\"<\/a><\/p>\n

In the \u2018Create new data source\u2019 wizard select the \u2018SQL Server Native Client x.x\u2019\u00a0 and click specify the \u2018User data source\u2019.<\/p>\n

\"\"<\/a>\"\"<\/a>\"\"<\/a><\/p>\n

In the \u2018Create a New Data Source to SQL Server\u2019 wizard you can create a connection to the SQL database you wish to reverse engineer.<\/p>\n

\"\"<\/a>\"\"<\/a>\"\"<\/a>\"\"<\/a><\/p>\n

\"\"<\/a>\"\"<\/a><\/p>\n

When you get back in the \u2018Reverse Engineer Wizard\u2019 you can select the new data source. After supplying the credentials you\u2019ll be able to specify the objects you wish to reverse engineer. (In the demo I\u2019ve chosen to reverse engineer all available objects). Next select the \u2018Yes, add the shapes to the current page\u2019 so the shapes are drawn on the page.<\/p>\n

\"\"<\/a>\"\"<\/a>\"\"<\/a>\"\"<\/a>\"\"<\/a>\"\"<\/a>\"\"<\/a><\/p>\n

You now have successfully reverse engineered a SQL database to a database model diagram in Microsoft Visio 2010.<\/p>\n

\"Drawing1<\/a><\/p>\n

<\/h4>\n

<\/h4>\n

How to forward engineer a database from Microsoft Visio 2010 to Microsoft SQL<\/h2>\n

Please first install the Visio Forward Engineer Addin for Visio 2010<\/a> before starting Microsoft Visio 20120.<\/em><\/p>\n

Open the database model diagram in Microsoft Visio 2010 select the \u2018Forward Engineer\u2019 tab on the ribbon. Click on \u2018Forward Engineer\u2019.<\/p>\n

\"\"<\/a><\/p>\n

In the \u2018Forward Engineer to SQL Server\u2019 wizard specify the name of the new database. In the demo I\u2019ve specified IngmarVerheijDB <\/em>as my new database name. Select the \u2018Generate DROP DATABASE and the \u2018Generate CREATE DATABASE\u2019 checkbox to make sure the database is always created, even if it already exists.<\/p>\n

\"\"<\/a><\/p>\n

If you checked the \u2018Open generated script when finished\u2019 the created script will we opened in the \u2018Microsoft SQL Server Management Studio\u2019 (if installed).<\/p>\n

\"\"<\/a><\/p>\n

REMARK<\/h4>\n

If the database does not yet<\/strong> exist<\/strong>, the following lines needs to be removed (where [IngmarVerheijDB]\u00a0 needs to be replaced with you database name).<\/p>\n

ALTER DATABASE [IngmarVerheijDB] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE\nGO<\/pre>\n","protected":false},"excerpt":{"rendered":"
A great built-in feature in Microsoft Office Visio 2010 is the \u2018Reverse Engineer\u2019 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 […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"site-container-style":"default","site-container-layout":"default","site-sidebar-layout":"default","disable-article-header":"default","disable-site-header":"default","disable-site-footer":"default","disable-content-area-spacing":"default","footnotes":""},"categories":[307],"tags":[391,389,173,157,388],"class_list":["post-3204","post","type-post","status-publish","format-standard","hentry","category-microsoft-sql","tag-forward-engineering","tag-reverse-engineering","tag-sql","tag-visio","tag-xsd"],"_links":{"self":[{"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/posts\/3204","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/comments?post=3204"}],"version-history":[{"count":3,"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/posts\/3204\/revisions"}],"predecessor-version":[{"id":7067,"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/posts\/3204\/revisions\/7067"}],"wp:attachment":[{"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/media?parent=3204"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/categories?post=3204"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/tags?post=3204"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}