We discussed in one of the previous articles called “How to store a SQL Server database diagram into a file and share it with others?” we can store database diagrams in files and share the files with others. In this article I’m describing very fast and easy ways to make a copy of existing database diagrams into another database. The possible scenarios are:

1. We want to create a copy of database diagrams into another database in the same SQL Server instance

2. We want to make a copy of database diagrams in another instance of SQL server

In both cases we need to have write access permission on the destination database.

Migrating database diagrams between two databases in the same instance

We just need to run the following T-SQL script:

use DESTINATIONDB

IF OBJECT_ID(N’dbo.sysdiagrams’) IS NULL

begin

CREATE TABLE dbo.sysdiagrams

(

name sysname NOT NULL,

principal_id int NOT NULL,

diagram_id int PRIMARY KEY IDENTITY,

version int,

definition varbinary(max)

CONSTRAINT UK_principal_name UNIQUE

(

principal_id,

name

)

EXEC SYS.SP_MS_MARKSYSTEMOBJECT ‘sysdiagrams’ —Creating a system object

End

Insert into sysdiagrams (name,principal_id,version,definition)

select name,principal_id,version,definition from SOURCEDB.dbo.sysdiagrams

The above solution works even if you did not install diagram support and you’ll have the copy of diagrams in place immediately after installing diagram support. To install database diagram support:

1. Expand the destination database

2. Right click on “Database diagrams”

3. Click “Install Diagram Support”

Migrating database diagrams across different SQL Servers

For the second scenario that the databases are not hosted by the same SQL Server instance we have the following options:

1. Using “Import and Export Data” tool

2. Creating a simple SSIS package

Note: SQL Server Integration Services is NOT available in SQL Server Express edition.

Migrating database diagrams using “Import and Export Data”

It is really easy to use the “Import and Export Data” tool. You need to just open the tool and:

1. Select the source server and database and click next

2. Select the destination server and database and click next

3. Select “Write a query to specify the data to transfer” and click next

4. Type “select * from sysdiagrams” in SQL statement part and click next

5. Change the destination table name to “sysdiagram” and then click “Edit Mappings”

6. Tick “Enable identity insert” then click OK, then click next and finally click Finish.

7. All done! You have successfully migrated database diagrams.

Migrating database diagram by creating a simple SSIS package

It is really easy to create a package to migrate database diagram. It takes just minutes to create the package, but, again, you need to have write permission on the destination server and note that SSIS is NOT available in SQL Server Express edition. So, follow the steps below:

1. Run SSDT (SQL Server Data Tools)

2. Create a new integration services project

3. Add a “Execute SQL Task” to the control flow

4. Double click on the task to go to the “Execute SQL Task Editor”

5. Select <New Connection…> from Connection

6. In “Configure OLEDB Connection Manager” select a particular connection manager or click New and create a new connection manager and click OK and go back to “Execute SQL Task Editor” window

7. Connect the “Execute SQL Task” to the “Data Flow Task”

8. Put the following SQL statement in “SQLStatement” and then click OK:

IF OBJECT_ID(N’dbo.sysdiagrams’) IS NULL

begin

CREATE TABLE dbo.sysdiagrams

(

name sysname NOT NULL,

principal_id int NOT NULL,

diagram_id int PRIMARY KEY IDENTITY,

version int,

definition varbinary(max)

CONSTRAINT UK_principal_name UNIQUE

(

principal_id,

name

)

EXEC SYS.SP_MS_MARKSYSTEMOBJECT ‘sysdiagrams’ —Creating a system object

end

9. Add a data flow task to control flow

10. In data flow add an OLEDB source

11. Double click on OLEDB source and in OLEDB source editor do the following settings:

a. Select the particular connection manager from the OLEDB connection manager list

b. In “Data access mode” select “SQL command” and add this code “SQL command text” section and click OK:

select name,principal_id,version,definition from sysdiagrams

12. Add an OLEDB destination into the data flow and double click on that to go to OLEDB Destination Editor and do the settings just like what we did for OLEDB Source Editor (in number 10) and click OK.

13. Select OLEDB Destination from the “Data Flow” and press F4 to navigate to OLEDB Destination properties. Change “ValidateExternalMetadata” to False. This is because at the first time of running the package sysdiagrams table might not be created, so, we will face an error.

14. Right click on the OLEDB Destination and click “Show Advanced Editor”

15. Go to “Input and Output Properties” tab and click on “External Columns”. Then click on “Add Column” button. Name the new column as “name”, set its data type as “Unicode string [DT_WSTR]” and Length as “128”. Do the same thing for other three columns as below and then click OK: