Home > General Administration > Export / Import Database Diagrams

Export / Import Database Diagrams

One of the developers in my IT dept asked how to export Database Diagrams. I never thought of it before, neither done or was asked to do it. So, Google as usual. Found several posts with the similar question – how do you export database diagrams in SQL Server 2000, 2005 and 2008. In SQL 2000, the diagram data was stored in a bunch of related tables, so the script which created the same tables on the “source” side and populated them using the right order of INSERT-AS-SELECTs did the job.

In SQL Server 2K5/2K8, a database diagram data is stored in a single user database table – sysdiagrams as binary(max) string.

However, it’s not that straight forward process. If you simply “copy” sysdiagrams table from “source” to “destination”, after you try to modify it, all the tables will be removed from this newly imported diagram.

This kind of behaviour is claimed by Microsoft to be designated – to preserve and follow the schema names notation.

First, the same table has to be created in “destination db”. That, actually, is taken care of SSMS. When you click at Database Diagrams, the following message pops-up: “This database does not have one or more of the support objects required to use database diagramming. Do you wish to create them?” – “Yes”, and the sisdiagrams table is created. The one and the only one – unlike SQL Server 2000, which, apparently had those multiple tables to overcome the limitation of binary(max) datatype. Second, Database Publishing Wizard has to be installed and ran with -schemaonly option to produce DDL file, and finally, the file has to be run in “destination” database.

Here are the detailed steps:

1. Download and Install MS Database Publishing Wizard to the default location (C:\Program Files\Microsoft SQL Server\90\Tools\Publishing)

 2. Script the “source” database: 

“C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\sqlpubwiz” script -d “C:\xyz.sql” -schemaonly

(NOTE: use -schemaonly option in order to produce DDL file only, – leaving out all the source db data)

 3. Create a blank, “DESTINATION” database

4. Go to Database Diagrams node under the new, click “Yes” to create the necessary objects (actually it’s the only one table called sysdiagrams)

5. use <DESTINATION>

go

6. Run c:\xyz.sql script

7. Finally, copy the “source” diagram data, – this time it will not through any message upon modification attempt:

USE <DESTINATION DB>;

SET IDENTITY_INSERT sysdiagrams ON

INSERT INTO sysdiagrams (name, principal_id, diagram_id, version, definition);

SELECT name, principal_id, diagram_id, version, definition
FROM ecometry..sysdiagrams;

SET IDENTITY_INSERT sysdiagrams OFF

Advertisements
  1. August 4, 2012 at 06:06

    I totally adore this blog!!! going to need to add this to the list.

    • August 4, 2012 at 13:57

      Hi Varija,
      Thank you for your kind words!

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: