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: )

Alfaiz Ahmed · Answer 1 · 2017-11-30T09:29:16.380

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]

Run This ON OLD Server Database SELECT name, principal_id,[version], [definition] FROM olddb.dbo.sysdiagrams.And INsert the Output Data INTO New Server DB — Alfaiz Ahmed, Nov 30 '17 at 09:19
where you got the Error ON New Server.if So Check Whether The Table dbo.sysdiagrams is available If NOT Then You Have to Insert it. — Alfaiz Ahmed, Nov 30 '17 at 09:23
Can You Drop The Table Create Again.you Can View in Edited Ans — Alfaiz Ahmed, Nov 30 '17 at 09:32

score 0 · Answer 2 · answered Nov 30 '17 at 08:57

0

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.

answered Nov 30 '17 at 08:57

Héctor Álvarez

494
3
18

would the diagram not need to be downgraded? as it was created in 2014 and im trying to view it in 2012? – WhatsThePoint Nov 30 '17 at 08:59
I doubt you can *downgrade* a diagram, in any case, you specified the "NEWSERVER" (which I assumed it was the new one) used SQL Server 2014. Also the Express server where you had your diagram was a 2012 instance. – Héctor Álvarez Nov 30 '17 at 10:12
youre right im getting mixed up with which versions each are running – WhatsThePoint Nov 30 '17 at 10:14

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

2 Answers2