Reverse Engineering Database Diagrams in Visio

This little one fools me every now and then, and it's one of those cases where I always say... crap, I know how to resolve this, how did I do it last time??

I find reverse engineering the database to Visio quite useful, particularly if you're unfamiliar with a new schema, as it's a great way to easily see the relationships between tables. And it's a whole lot better than the abomination that is the generated database diagram in SQL Server.

To get started, create a new Database Model Diagram in Visio. You'll see on the menu bar that you get a Database option, and the first item is Reverse Engineer.

In the wizard that pops up, one of the installed Visio drivers should be Microsoft SQL Server. Select this from the drop down, then click on New... to create a new datasource. You'll need to do this for each database that you want to connect to. For SQL Server database, generally you'll select "User Data Source" and the "SQL Server" or "SQL Native Client" driver over the next two screens.

On the next screen, give your data source a name and choose the server. It's important to get this bit right, and if you're connecting to a named instance, to select the name. Otherwise when you try to connect on the next screen (using your prefered choice of authentication), you'll get an error similar to the one below.



Now that one at least gives you a clue. Sometimes you'll be given an error like "SQL Server does not exist or access denied", so look out for that.