How you can copy or migrate database diagrams into one other database

October 21, 2022

1

We mentioned in one of many earlier articles referred to as “How you can retailer a SQL Server database diagram right into a file and share it with others?” we will retailer database diagrams in information and share the information with others. On this article I’m describing very quick and straightforward methods to make a duplicate of current database diagrams into one other database. The attainable eventualities are:

1. We wish to create a duplicate of database diagrams into one other database in the identical SQL Server occasion

2. We wish to make a duplicate of database diagrams in one other occasion of SQL server

In each circumstances we have to have write entry permission on the vacation spot database.

We simply have to run the next T-SQL script:

use DESTINATIONDB

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

start

CREATE TABLE dbo.sysdiagrams

(

title sysname NOT NULL,

principal_id int NOT NULL,

diagram_id int PRIMARY KEY IDENTITY,

model int,

definition varbinary(max)

CONSTRAINT UK_principal_name UNIQUE

(

principal_id,

title

)

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

Finish

Insert into sysdiagrams (title,principal_id,model,definition)

choose title,principal_id,model,definition from SOURCEDB.dbo.sysdiagrams

The above answer works even in case you didn’t set up diagram help and also you’ll have the copy of diagrams in place instantly after putting in diagram help. To put in database diagram help:

1. Broaden the vacation spot database

2. Proper click on on “Database diagrams”

3. Click on “Set up Diagram Help”

For the second situation that the databases are usually not hosted by the identical SQL Server occasion we have now the next choices:

1. Utilizing “Import and Export Knowledge” software

2. Making a easy SSIS bundle

Notice: SQL Server Integration Providers is NOT out there in SQL Server Specific version.

Migrating database diagrams utilizing “Import and Export Knowledge”

It’s very easy to make use of the “Import and Export Knowledge” software. It is advisable to simply open the software and:

1. Choose the supply server and database and click on subsequent

2. Choose the vacation spot server and database and click on subsequent

3. Choose “Write a question to specify the info to switch” and click on subsequent

4. Sort “choose * from sysdiagrams” in SQL assertion half and click on subsequent

5. Change the vacation spot desk title to “sysdiagram” after which click on “Edit Mappings”

6. Tick “Allow identification insert” then click on OK, then click on subsequent and at last click on End.

7. All finished! You’ve efficiently migrated database diagrams.

It’s very easy to create a bundle emigrate database diagram. It takes simply minutes to create the bundle, however, once more, it’s worthwhile to have write permission on the vacation spot server and observe that SSIS is NOT out there in SQL Server Specific version. So, observe the steps under:

1. Run SSDT (SQL Server Knowledge Instruments)

2. Create a brand new integration providers challenge

3. Add a “Execute SQL Process” to the management movement

4. Double click on on the duty to go to the “Execute SQL Process Editor”

5. Choose <New Connection…> from Connection

6. In “Configure OLEDB Connection Supervisor” choose a specific connection supervisor or click on New and create a brand new connection supervisor and click on OK and return to “Execute SQL Process Editor” window

7. Join the “Execute SQL Process” to the “Knowledge Circulate Process”

8. Put the next SQL assertion in “SQLStatement” after which click on OK:

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

start

CREATE TABLE dbo.sysdiagrams

(

title sysname NOT NULL,

principal_id int NOT NULL,

diagram_id int PRIMARY KEY IDENTITY,

model int,

definition varbinary(max)

CONSTRAINT UK_principal_name UNIQUE

(

principal_id,

title

)

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

finish

9. Add a knowledge movement process to manage movement

10. In information movement add an OLEDB supply

11. Double click on on OLEDB supply and in OLEDB supply editor do the next settings:

a. Choose the actual connection supervisor from the OLEDB connection supervisor checklist

b. In “Knowledge entry mode” choose “SQL command” and add this code “SQL command textual content” part and click on OK:

choose title,principal_id,model,definition from sysdiagrams

12. Add an OLEDB vacation spot into the info movement and double click on on that to go to OLEDB Vacation spot Editor and do the settings similar to what we did for OLEDB Supply Editor (in quantity 10) and click on OK.

13. Choose OLEDB Vacation spot from the “Knowledge Circulate” and press F4 to navigate to OLEDB Vacation spot properties. Change “ValidateExternalMetadata” to False. It’s because on the first time of working the bundle sysdiagrams desk won’t be created, so, we are going to face an error.

14. Proper click on on the OLEDB Vacation spot and click on “Present Superior Editor”

15. Go to “Enter and Output Properties” tab and click on on “Exterior Columns”. Then click on on “Add Column” button. Identify the brand new column as “title”, set its information sort as “Unicode string [DT_WSTR]” and Size as “128”. Do the identical factor for different three columns as under after which click on OK: