How to export a SQL Server 2008 Database Diagram t

问题:

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.

回答1:

@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

回答2:

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

回答3:

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.

回答4:

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

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 

回答5:

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

回答6:

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.