SQL Server 2005，如何将数据库关系图复制到另一个服务器

Is there a way to copy a SQL Server Database Diagram to another server?

是否有方法将SQL Server数据库关系图复制到另一个服务器?

I found this and modified it sightly to copy only one diagram:

我找到了这个并且修改的很好看，只复制了一个图:

INSERT INTO dbB.dbo.sysdiagrams 
SELECT [name],[principal_id],[version],[definition]
FROM dbA.dbo.sysdiagrams
Where name = 'MyDiagramName'

But I need to copy it to another Server (Development to Production).

但是我需要将它复制到另一个服务器(开发到生产)。

I don't want to create a linked server to do this. (Updated explanation) The reason behind that is that I want to include the diagram in a upgrade script. I made changes to the database to support a new version (new tables, etc) and I want the diagram be be part of the upgrade script. so it's best if i could put that in a SQL script. If a got a separated file to manually import afterward, it could do the job, but it not the best.

我不想创建一个链接服务器来做这个。(更新的解释)背后的原因是我想在升级脚本中包含这个图。我对数据库进行了修改，以支持一个新版本(新表等)，我希望这个图表成为升级脚本的一部分。所以我最好把它放到SQL脚本中。如果a在之后得到一个单独的文件来手动导入，它可以做这个工作，但不是最好的。

So i need to 'save' the diagram definition to a file somehow to restore it on the other server.

因此，我需要将图定义“保存”到一个文件中，以便在另一个服务器上恢复它。

3 个解决方案

#1

Just found this solution.

刚刚发现这个解决方案。

In this article, There's the code to create the Stored Procedure that generate a SQL Server Script to recreate the diagrams. So you just save the output of the Stored Procedure in a .SQL file and run it on the other server.

在本文中，有创建生成SQL Server脚本以重新创建图的存储过程的代码。因此，只需将存储过程的输出保存到. sql文件中，并在另一台服务器上运行它。

The problem is to convert Varbinary To a String (Varchar) in Hex in order to be able use it in a insert/update query. But it's well explained in the link...

问题是在十六进制中将Varbinary转换为String (Varchar)，以便能够在insert/update查询中使用它。但它在链接中有很好的解释…

#2

First : Create one Link Server From Source Server inside Destination Server.

首先:从目标服务器内部的源服务器创建一个链接服务器。

For create Link Server use this Link

用于创建链接服务器，请使用此链接

Second : Use This

第二:使用这个

USE DestinationDatabase

DELETE  sysDiagrams
WHERE   name IN ( SELECT    name
              FROM      <LinkServerName>.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    <LinkServerName>.SourceDatabase.dbo.sysDiagrams

SET IDENTITY_INSERT sysDiagrams OFF

#3

-1

This tutorial/example will explain it

本教程/示例将对此进行解释

http://www.sharpdeveloper.net/content/archive/2008/04/19/copy-a-database-diagram-to-another-database.aspx

#1