We mentioned in one of many earlier articles known as “Learn how to 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 must 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 resolution works even in the event you didn’t set up diagram assist and also you’ll have the copy of diagrams in place instantly after putting in diagram assist. To put in database diagram assist:

1.       Increase 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 will not be hosted by the identical SQL Server occasion now we have the next choices:

1.       Utilizing  “Import and Export Information” software

2.       Making a easy SSIS bundle

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

Migrating database diagrams utilizing “Import and Export Information”

It’s very easy to make use of the “Import and Export Information” software. You should 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.       Kind “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 id insert” then click on OK, then click on subsequent and at last click on End.

7.       All executed! You may have 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, you could have write permission on the vacation spot server and word that SSIS is NOT out there in SQL Server Specific version. So, comply with the steps under:

1.       Run SSDT (SQL Server Information Instruments)

2.       Create a brand new integration companies undertaking

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

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 selected 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 “Information Movement 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 an information move process to regulate move

10.   In information move 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 title,principal_id,model,definition from sysdiagrams

12.   Add an OLEDB vacation spot into the info move 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.

13.   Choose OLEDB Vacation spot from the “Information Movement” and press F4 to navigate to OLEDB Vacation spot properties. Change “ValidateExternalMetadata” to False. It’s because on the first time of operating the bundle sysdiagrams desk won’t be created, so, we’ll 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. Title the brand new column as “title”, set its information kind as “Unicode string [DT_WSTR]” and Size as “128”. Do the identical factor for different three columns as under after which click on OK:

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

17.   Now press F5 to run the bundle. All executed.