Transferring Database Diagrams
-
Hello Everyone
I would like to transfer the database diagram(s) from one database server to another. Has anyone tried this? My server at home has a much larger monitor that in the office. I would like to create the diagram at home and move it to my workstation in the office. Yes, I have requested a larger monitor for my office.
Thanks
Andrew SQLDBA
-
If its a small db then backup and restore it
-
I didn't think that diagram went with.
Thanks, I will do that.
It has been a long day already, and I need more coffee. My mind is not good.
Thanks
Andrew SQLDBA
-
andrew in my sql 2008 db, if i create a diagram, then a single row is created in the table dbo.sysdiagrams.
select * from dbo.sysdiagrams
name principal_id diagram_id version definition
Diagram_0 1 1 1 0xD0CF11E0A1B11AE10000...
the column definition is a varbinary(max), which contains everything, but i'm pretty sure it's not xml, since a convert to nvarchar(max) returns non-readable values:
select name,convert(nvarchar(max),[definition]) from dbo.sysdiagrams
--results:
name(No column name)
Diagram_0????
after that i tried to migrate the row to a different database...but it's clear that the data in the definition is tied to specific object_id's, and it fails if the id's are not the same.
so you could migrate to a backup of the same db, but not a clean/fresh db instance.
insert into Sandbox.dbo.sysdiagrams
select
principal_id,
2,--had to change it manually, no identity() on sysdiagrams
version,
definition
from Whatever.dbo.sysdiagrams
see it migrated, but then when it ties the diagram to objects, it raises an error.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Have you try this code???
-
Yes, I've transferred one of my diagrams to another SQL Instance.
-
Twinsoft SME (7/8/2010)
Have you try this code???Twinsoft is right...if you import the diagrams into a database that does not have the exact same object_id's, it fails with the same error i posted above....all the code example is is a way to script the table instead of a simple INSERT INTO like my example. it doesn't work if you created a table with the same name(s)...it's ID related.
not much more than a longhand version of INSERT INTO....
you can see the same issue is identified in the comments of the article.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I have just did the following:
1/ scripted all tables (by SSMS Tasks->Script) from my orgin DB
2/ execute Tool_ScriptDiagram2005 in my orgin DB
3/ created new test DB
4/ in test DB: refresh "Database Diagrams" in SSMS (so sysdiagrams is created)
5/ in test DB: execute script with create-code of my tables
6/ in test DB: execute Tool_ScriptDiagram2005 code (from 2)
My diagrams in test DB works fine! 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply