How do you migrate SQL Server Database Diagrams to

问题:

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.

回答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

回答2:

An old but elegant answer I came across while looking to solve the same issue: bcp

To extract:

bcp YOUR_DATABASE..sysdiagrams out YOUR_DIAGRAM_NAME.bcp -c -T -S SERVERNAME

To import:

(cd to the directory the diagram is in)
bcp YOUR_DATABASE..sysdiagrams in YOUR_DIAGRAM_NAME.bcp -c -T -S SERVERNAME

回答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

回答4:

You can script the diagrams (archive link) to a .sql file...

回答5:

I think this is the better way :

USE DestinationDatabase

DELETE  sysDiagrams
WHERE   name IN ( SELECT    name
              FROM      SourceDatabase.dbo.sysDiagrams )

SET IDENTITY_INSERT sysDiagrams ON

INSERT  sysDiagrams
    ( name ,
      principal_id ,
      diagram_id ,
      version ,
      definition
    )
    SELECT  name ,
            principal_id ,
            diagram_id ,
            version ,
            definition
    FROM    SourceDatabase.dbo.sysDiagrams

SET IDENTITY_INSERT sysDiagrams OFF

回答6:

It's pretty easy, if you have a linked server or when you are still on the same machine:

USE newDB;

SET IDENTITY_INSERT sysdiagrams ON;

INSERT INTO sysdiagrams(name,principal_id,diagram_id,version,definition)
SELECT name,principal_id,diagram_id,version,definition
FROM oldDB..sysdiagrams;

SET IDENTITY_INSERT sysdiagrams OFF;

回答7:

My issue is that sometimes I need to drop my database and recreate it. I have a full generation script that creates everything except for the diagrams.

My workaround is to copy temporarily the diagram to another database, and after my database regeneration copy it back.

The funny thing is, one should NOT try to open the copy diagram, because the other database where it is located does not have the table structures of my database, so SQL server automatically upon opening clears all the tables in the diagram q-:

回答8:

I did this using MSSMS's Export Data feature.

• Right click on the database.
• Choose Tasks > Export Data, which starts a wizard.
• Set up both data sources. I used SQL Server Native Client 11, but others will probably work. Click next.
• Choose Copy data from one or more tables or views (the default)
• From the list, choose the sysdiagrams table.
• Choose Edit Mappings. Make sure 'Enable identity insert' is checked. Hit ok.
• Click next, then hit Finish to execute.

Worked like a charm.

回答9:

You can directly copy as answered before. If you want to 'backup' only the diagram you can do that, see my answer on other question.

https://stackoverflow.com/a/26884146/951001

回答10:

Safer way to do it:

merge into TcsDev1..sysdiagrams as Trg
using TcsDev2..sysdiagrams as Src
on Trg.name = Src.Name
when not matched by target then
insert (name, principal_id, [version], [definition]) 
values (src.name, src.[principal_id],  src.[version], src.[definition] );