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

On the one hand, Microsoft only provides compatibility with one version of MSSQL and the previous one, so diagrams are probably not compatible between your instances of MSSQL.

On the other hand, they offer a feature to upgrade your diagrams to the next version: https://technet.microsoft.com/en-us/library/ms190628(v=sql.110).aspx

To upgrade legacy database diagrams

• From Object Explorer, expand the database.
• Expand the Database Diagram node under the database.
• Select Yes when prompted if you want to set up database diagramming.
• This will upgrade diagrams stored on the database.

Answer 2

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]