How to move Database Diagram from One Server to Another Server

I have created a new Database Diagram in Test Database and it is in sitde01 Server. Now I 开发者_StackOverflow社区want to move it to another server. How do I migrate it to another server.

It can be done, but it's a royal pain. Here's an outline of the process and some scripts.

Diagrams are stored in a "system" table named sysDiagrams. This table (only?) gets created when you click on the diagrams node in SSMS, it asks you if you want to create the objects that support diagrams, and you click "Yes". Do so on both your source and target databases.

Create the diagram or diagrams in the "source" database.

Review the structure and contents of sysDiagrams. Note that column diagram_id is an identity column. 1 row gets stored for every diagram. (You don't care, but it used to be 4 or 5 rows in SQL 2000.)

To copy to another database on the same SQL instance, the simplest way is to do INSERT... SELECT... between tables. With that identity column in the way, you'll have to fuss with SET IDENTITY_INSERT, and perhaps assign a new identity value on the target computer. Irritating, but not critically hard.

The following script will copy all diagrams from one database to another that's on the same server (this is how I archive complex diagrams that took waaaay too long to create, from databases that are prone to get dropped and recreated):

USE TargetDatabase

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

To copy to another database on a different SQL instance (or server), well, it gets even harder. I use temporarily created Linked Server definitions, using scripts I sweated bullets over years ago and never want to have to modify again (i.e. post a different question so someone who knows can tell you how they work), and modify the scripts with appropriate four-part naming conventions. Other options (OPENROWSET and the like) are possible, but I'm even less familiar with those.

If you want to move your diagrams from one instance or server to a different one, and you don't want to restore the whole database, you can do the following.

1. If it doesn't exist, create the database on your target server. You also have to click on the "Database Diagrams" node in SSMS to have it create a dbo.sysDiagrams table.
2. Then make sure to import all the schema information you need in your diagram. Because your diagram will point to these. I.e. tables, PK, FK etc. must be present.
3. Backup your database on the source server.
4. Restore it into a temporary database on the target server. In this way you get all your diagram information into the target server.

5. Copy the information from the dbo.sysDiagrams table in the temporary database into the dbo.sysDiagram table of your target database. You could do something like this (adapted the code from Philip Kelley):

   USE TargetDatabase 
   
   SET identity_insert sysDiagrams on 
   
   INSERT sysDiagrams (name, principal_id, diagram_id, version, definition) 
    select name, principal_id, diagram_id, version, definition 
     from TempDatabase.dbo.sysDiagrams 
   
   SET identity_insert sysDiagrams off 
   

This solution worked excelent for me. Of course if you don't want all the diagrams or if other diagrams are existing in the target database, you have to filter the select statement and do some identity_insert manipulation, but this shouldn't be too difficult.

In order to move the database-diagram you will need to migrate all the tables and triggers included in that diagram. The easiest way to do this is backup the db and restore on the other server.