How to export a SQL Server 2008 Database Diagram to another DB?

Question

I use the handy Database Diagramming tool in SQL Server 2008 for creating and managing relationships. I have exported the sourceDB to the destinationDB but the diagram doesn't come across.

I am looking around trying to figure out how to export just the diagram I have in one database to another... This online KB article fails since select * from dtproperties doesn't exist anymore.

Justin · Accepted Answer · 2010-12-18T20:53:29.987

@Ash I was having the same problem. Here's what we did to get around it...

It seems that System Diagrams are stored within the "sysdiagrams" table. So the first thing you need to do is determine the diagram_id of the Diagram you wish to copy. Run the following query to list them all. ** Note you need to replace "SourceDB" with the name of your database.

-- List all database diagrams
SELECT * FROM [SourceDB].[dbo].sysdiagrams

Then you can use INSERT to duplicate the diagram from one database to another as follows. ** Note again replace "SourceDB" with the name of the Database containing the existing diagram and "DestinationDB" with the name of the Database you wish to copy to. Also @SourceDiagramId should be set to the id retrieved above.

-- Insert a particular database diagram
DECLARE @SourceDiagramId int = 1

INSERT INTO [DestinationDB].[dbo].sysdiagrams
SELECT [name],diagram_id , version,definition from [SourceDB].[dbo].sysdiagrams
WHERE diagram_id = @SourceDiagramId

Then you need to set the "principal_id" to 1 manually.

-- Update the principal id (no idea why, but it set the owner as some asp_net user
UPDATE [DestinationDB].[dbo].sysdiagrams
SET principal_id = 1

This worked for us it seems pretty hacky especially since the Diagram is stored entirely in a single binary field "definition".

Answer comes from:
http://www.dotnetspider.com/resources/21180-Copy-or-move-database-digram-from-for.aspx

score 11 · Answer 2 · edited Jun 20 '20 at 09:12

This generates an import string:

SELECT
    'DECLARE @def AS VARBINARY(MAX) ; ' +
    'SELECT @def = CONVERT(VARBINARY(MAX), 0x' + CONVERT(NVARCHAR(MAX), [definition], 2) + ', 2) ;' +
    ' EXEC dbo.sp_creatediagram' +
        ' @diagramname=''' + [name] + ''',' +
        ' @version=' + CAST([version] AS NVARCHAR(MAX)) + ',' +
        ' @definition=@def'
    AS ExportQuery
FROM
    [dbo].[sysdiagrams]
WHERE
    [name] = '' -- Diagram Name

Next, you run the generated string in other DB.

As PROCEDURE:

-- =============================================
-- Author:      Eduardo Cuomo
-- Description: Export Database Diagrama to SQL Query
-- =============================================
CREATE PROCEDURE [dbo].[Sys_ExportDatabaseDiagram]
    @name SYSNAME -- Diagram Name
AS

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT
    'DECLARE @def AS VARBINARY(MAX) ; ' +
    'SELECT @def = CONVERT(VARBINARY(MAX), 0x' + CONVERT(NVARCHAR(MAX), [definition], 2) + ', 2) ; ' +
    ' EXEC dbo.sp_creatediagram' +
        ' @diagramname=''''' + [name] + ''''',' +
        ' @version=' + CAST([version] AS NVARCHAR(MAX)) + ',' +
        ' @definition=@def'
    AS ExportQuery
FROM
    [dbo].[sysdiagrams]
WHERE
    [name] = @name

this can be used when transferring diagrams between different servers. To avoid limit of SQL Management studio for result size add 'for xml auto' at the end of sql select. XML output is limited to 2mb and can be set to more. Then simply cut the sql from the xml and execute on other server (you will need to have at least one diagram created manually before) — Michal Minich, Oct 09 '14 at 12:04

score 7 · Answer 3 · edited Oct 26 '12 at 14:38

You can get rid of the UPDATE statement by fixing your INSERT statement - specifically the select portion. You are inserting the diagram_id column into the principal_id column (diagram_id is an identity).

Change it to:

DECLARE @SourceDiagramId int = 1
INSERT INTO [DestinationDB].[dbo].sysdiagrams
SELECT [name],principal_id,version,definition from [SourceDB].[dbo].sysdiagrams
WHERE diagram_id = @SourceDiagramId

And presto, it's all in there right the first time.

score 5 · Answer 4 · edited May 23 '17 at 12:10

As in C Isaze answer, there are three simple steps:

1- Create the same number of "dummy" diagrams in the target server where you want to copy the diagrams

2- Add the target server as a Linked Server in the source server

enter image description here

3- run this script on source server

update [LINKEDSERVER].TARGETDB.[dbo].sysdiagrams set [definition]=
    (SELECT [definition] from SOURCEDB.[dbo].sysdiagrams WHERE diagram_id = 1)
    where diagram_id=1

score 4 · Answer 5 · answered Apr 10 '13 at 21:41

If the databases are in different servers, there may be permission issues.

To copy the sysdiagrams, create the same number of "dummy" diagrams in the target server where you want to copy the diagrams, add the target server as a Linked Server in the source server and then run the script:

SELECT * from [LINKEDSERVER].TARGETDB.[dbo].sysdiagrams
SELECT * from SOURCEDB.[dbo].sysdiagrams

update [LINKEDSERVER].TARGETDB.[dbo].sysdiagrams set definition=
(SELECT definition from SOURCEDB.[dbo].sysdiagrams WHERE diagram_id = 1)
where diagram_id=1 
-- the first 2 select commands will confirm that you are able to connect to both databases
-- then change the id as required to copy all the diagrams

score 3 · Answer 6 · answered Nov 23 '15 at 10:45

There's a tool for exporting the diagrams to file and back into a database that you can find here: https://github.com/timabell/database-diagram-scm/

You'd be able to use this by pointing it at your original database and doing an export, and then pointing at your target database and doing an import.

How to export a SQL Server 2008 Database Diagram to another DB?

6 Answers6

As PROCEDURE:

Linked