How to successfully migrate/copy a database diagram from different servers

Question

Background: Our database was originally being hosted by a third party hosting company on a shared server. We did not have the permissions to create database diagrams, so the quick solution was to create a diagram on .\sqlexpress just for ease. Now our database is on our own dedicated server so we have the permissions to create diagrams (not the only reason).

Now i would like to migrate the diagram to the new server from my sqlexpress without having to recreate it. So going off this question i did a SELECT * FROM localdb.dbo.sysdiagrams found the diagram i need, then performed an INSERT INTO newdb.dbo.sysdiagrams VALUES (diagramValuesHere) by copying and pasting all the values. This appears to have created the diagram except when i go to view it i get this message;

The docfile has been corrupted. (MS Visual Database Tools)

The only thing i can think of that is causing the issue is that the sql server versions are different.

SQLEXPRESS - Microsoft SQL Server 2012 (SP3-GDR) (KB4019092) - 11.0.6251.0 (X64) Jul 7 2017 07:14:24 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.3 (Build 15063: )

. .

NEWSERVER - Microsoft SQL Server 2014 (SP2-CU7) (KB4032541) - 12.0.5556.0 (X64) Aug 17 2017 12:07:38 Copyright (c) Microsoft Corporation Web Edition (64-bit) on Windows NT 6.3 (Build 9600: )

Answer 1

Enable Diagrams in the new database:

In the new database click on the "Database Diagrams" folder. Sql Server Management Studio will prompt you to enble diagrams. If you Ok this step, you will have a sysdiagrams table in the database.

Then execute the following:

IF EXISTS (SELECT name FROM sys.objects WHERE object_id = OBJECT_ID(N'sysdiagrams') AND type in (N'U',N'PC'))
    DROP TABLE sysdiagrams
GO


CREATE TABLE [dbo].[sysdiagrams](
    [name] [sysname] NOT NULL,
    [principal_id] [int] NOT NULL,
    [diagram_id] [int] IDENTITY(1,1) NOT NULL,
    [version] [int] NULL,
    [definition] [varbinary](max) NULL,
PRIMARY KEY CLUSTERED 
(
    [diagram_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UK_principal_name] UNIQUE NONCLUSTERED 
(
    [principal_id] ASC,
    [name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

EXEC sys.sp_addextendedproperty @name=N'microsoft_database_tools_support', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sysdiagrams'
GO


SELECT name, principal_id,[version], [definition]
       FROM olddb.dbo.sysdiagrams -- OLD Server Database Diagram

INSERT INTO newdb.dbo.sysdiagrams 
     SELECT name, principal_id,[version], [definition]