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


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 are able to 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 replica of current database diagrams into one other database. The attainable eventualities are:

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

2.       We need to make a replica 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

              (

                     identify 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,

                           identify

                     )

              )

EXEC SYS.SP_MS_MARKSYSTEMOBJECT ‘sysdiagrams’ Making a system object

Finish

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

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

 

 

The above resolution works even for those who 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.       Develop 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 usually are not hosted by the identical SQL Server occasion we now have the next choices:

1.       Utilizing  “Import and Export Information” software

2.       Making a easy SSIS package deal

Observe: SQL Server Integration Companies is NOT obtainable in SQL Server Categorical version.

Migrating database diagrams utilizing “Import and Export Information”

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

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

clip_image001[4]

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

clip_image002[4]

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

clip_image003[4]

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

clip_image004[4]

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

clip_image005[4]

6.       Tick “Allow id insert” then click on OK, then click on subsequent and eventually click on End.

clip_image006[4]

7.       All achieved! You’ve gotten efficiently migrated database diagrams.

clip_image007[4]

 

It’s very easy to create a package deal emigrate database diagram. It takes simply minutes to create the package deal, however, once more, it’s essential to have write permission on the vacation spot server and word that SSIS is NOT obtainable in SQL Server Categorical version. So, comply with the steps under:

1.       Run SSDT (SQL Server Information Instruments)

2.       Create a brand new integration companies venture

3.       Add a “Execute SQL Job” to the management circulation

4.       Double click on on the duty to go to the “Execute SQL Job 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 Job Editor” window

7.       Join the “Execute SQL Job” to the “Information Circulation Job”

clip_image008[4]

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

              (

                     identify 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,

                           identify

                     )

              )

EXEC SYS.SP_MS_MARKSYSTEMOBJECT ‘sysdiagrams’ Making a system object

finish

9.       Add an information circulation job to manage circulation

10.   In information circulation 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 record

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

 

choose identify,principal_id,model,definition from sysdiagrams

 

clip_image010[4]

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

clip_image012[4]

13.   Choose OLEDB Vacation spot from the “Information Circulation” and press F4 to navigate to OLEDB Vacation spot properties. Change “ValidateExternalMetadata” to False. It’s because on the first time of operating the package deal 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”

clip_image013[4]

15.   Go to “Enter and Output Properties” tab and click on on “Exterior Columns”. Then click on on “Add Column” button. Title the brand new column as “identify”, 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:

Column Title

Information Sort

principal_id

four-byte signed integer [DT_I4]

model

four-byte signed integer [DT_I4]

definition

picture [DT_IMAGE]

clip_image015[4]

16.   Click on on “Column Mappings” tab to ensure are columns are mapped efficiently.

clip_image017[4]

17.   Now press F5 to run the package deal. All achieved.

clip_image018[4]



Supply hyperlink

Leave a Reply

Your email address will not be published. Required fields are marked *