SQL Server 2005, how to copy a Database Diagram to another server

Question

Is there a way to copy a SQL Server Database Diagram to another 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.

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

DavRob60 · Accepted Answer · 2011-05-26T15:59:50.603

5

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.

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...

edited May 26 '11 at 15:59

answered Jul 23 '10 at 19:50

DavRob60

3,517
7
34
56

score 2 · Answer 2 · edited May 23 '17 at 11:53

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

edited May 23 '17 at 11:53

Community

1
1

answered Oct 31 '13 at 05:11

Ardalan Shahgholi

11,967
21
108
144

I specified in the question I don't want to use a link server because the diagram need to be included in an upgrade script. Thanks anyway. – DavRob60 Oct 31 '13 at 10:44
use Open DataSource against Link Server . – Ardalan Shahgholi Oct 31 '13 at 11:07
1

You just don't get it. What if the destination server don't "see" the source server? – DavRob60 Oct 31 '13 at 11:57
ok. this link maybe help you : http://www.codeproject.com/Articles/43076/Backup-SQL-Diagrams-In-Less-Than-5-Minutes – Ardalan Shahgholi Oct 31 '13 at 12:09
I thinks you need backup scripts for all diagram from source database and then you can run this back up without consider existence source database on destination database. – Ardalan Shahgholi Oct 31 '13 at 12:11

AjV Jsy · Answer 3 · 2022-11-09T17:33:43.473

I have used the following with success.

Create a temporary table in the source base to copy the binary diagram definition(s) to a table that you can generate a script from (because SysDiagrams isn't an option in Generate Scripts wizard) :

    CREATE TABLE CopySysDiagrams (
        name NvarChar(200), principal_id INT, diagram_id INT PRIMARY KEY,
        version INT, [definition] varBinary(MAX) )
    
    INSERT INTO CopySysDiagrams SELECT * from sysdiagrams   
        -- optional WHERE clause if you only need one diagram

Use Tasks... Generate Scripts... and the script it produces will contain the binary definitions of the diagram(s) in 0x format
Use a script like the following in the target base (where 19 in this case is first free ID because there were 18 before), copying the binary data 0x string into here:

    SET identity_insert sysDiagrams ON

    INSERT sysDiagrams (name, principal_id, diagram_id, version, definition) 
        VALUES  ('Your Diagram Name', 1, 19, 1, 
                    0xTHEBINARYDATA
                 )

    SET identity_insert sysDiagrams OFF

score -1 · Answer 4 · answered Jul 22 '10 at 17:23

-1

This tutorial/example will explain it

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

answered Jul 22 '10 at 17:23

greektreat

2,329
3
30
53

This use a 'temporary table'. that don't work for the another Server scenario. – DavRob60 Jul 22 '10 at 17:45
Have you ever used a link server? – greektreat Jul 22 '10 at 20:14
Yes, but I want to do it without, that's part of the question. – DavRob60 Jul 23 '10 at 12:03

SQL Server 2005, how to copy a Database Diagram to another server

4 Answers4