How do you migrate SQL Server Database Diagrams to another Database?

Question

My team recently rebuilt their SQL Server 2005 development database from scratch using the TFS source files.

Afterwards, we had a nice clean database that more closely matched our test and production environments. However, we also lost the database diagrams that had been created over the months in the old development database.

Does anyone know of a way to export a SQL Server 2005 database diagram from one database to another that contains the same tables and schema objects?

UPDATE:

Both Valentin and CMS's answers below should work just fine. I accepted Valentin's because it was first and I had already started using it.

I ended up creating a temp table called 'robsysdiagrams' and copied all the data into that and used the Microsoft SQL Server Database Publishing Wizard to script the data inserts out to disk. I then renamed robsysdiagrams to sysdiagrams in the script and ran it on the destination database. Everything worked fine.

Answer 1

Rob, there is a table 'sysdiagrams' which holds the diagram definitions. Try to create the table and insert the data from old database. It should work.

HTH

Answer 2

You can script the diagrams to a .sql file...

Answer 3

Assuming you have access to both databases within a SQL Server instance.

Enable Diagrams in the new database:

In the new database click on the "Database Diagrams" folder. Sql Server Management Studio will prompt you to enble diagrams. If you Ok this step, you will have a sysdiagrams table in the database.

Then execute the following:

insert into newdb.dbo.sysdiagrams select name, principal_id,[version], [definition] from olddb.dbo.sysdiagrams

Answer 4

I am able to export/import the records to the sysdiagrams table. And I see all of the diagrams in the Diagrams folder. However, when I try to open one, I see an outline of each table, then get a popup error message that says:

"Table(s) were removed from the diagram because privileges were removed to these table(s) or the table(s) were dropped."

When I exit from the popup, all of the tables are removed from the diagram.

Any ideas? Thanks