CODEWITHSUNDEEP
sql-serverdiagram
Ash Machine
Ash Machine

Reputation: 9901

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

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.

Upvotes: 30

Views: 32755

Answers (6)

Tim Abell
Tim Abell

Reputation: 11880

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.

Upvotes: 3

Zakaria
Zakaria

Reputation: 1085

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 enter image description here 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

Upvotes: 5

Eduardo Cuomo
Eduardo Cuomo

Reputation: 18937

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

Upvotes: 12

C Isaza
C Isaza

Reputation: 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

Upvotes: 4

WayneS
WayneS

Reputation: 81

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.

Upvotes: 8

Justin
Justin

Reputation: 10897

@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

Upvotes: 46

Related Questions