1

My objective is to get relationships from database A to database B. Please note I can not do a backup and restore, this is not an option.

These three sets of relationships are in three diagrams and having never copied a diagram from A to B before I was thinking that, if I did this the relationships may also come over. The copy went great, simply moving the records from a.sysDiagrams to b.sysDiagrams was easy.

But no relationships came across, damn. What is the best method to copy diagrams including the relationships from A to B?

A fair amount of time was put into creating these. We often want to move a set of tables from Database to database. Most of the time these are on different servers.

Ajay2707
  • 5,690
  • 6
  • 40
  • 58
Mat41
  • 1,287
  • 4
  • 15
  • 29
  • 1
    The diagram displays relationships - it doesn't contain them. You'll need to script that. Look into software like RedGates Compare programs and similar if you do not want to do it yourself. – Allan S. Hansen May 18 '15 at 05:55
  • Hi Allan thank you for clarifying that. This is very unfortunate, it would be a great feature. I don't even see the point of copying them if all that comes across is a bunch of tables to be honest. So except for a backup/restore does sql2008 really have no way to move a bunch of tables and relationships? Thanks again – Mat41 May 18 '15 at 07:29
  • I don't know where diagrams get these things from but I suspect it is foreign keys (which in turn require primary keys). If you script those across do they appear in your diagram? – Nick.Mc May 19 '15 at 03:06

2 Answers2

0

for completeness the comment made by Allan S is the answer to this post. I don't believe I can accept his comment since it was only a comment and not an answer?

I would be very interested to know if there is a way to copy a 2008 SQLServer diagram and include the relationships. I assume there is not.

miken32
  • 42,008
  • 16
  • 111
  • 154
Mat41
  • 1,287
  • 4
  • 15
  • 29
-1

There are so many option without backup-restore, you can script the diagrams to a .sql file...first check that diagrams list

-- List all database diagrams
SELECT * FROM [SourceDB].[dbo].sysdiagrams

Check similar question : How to export a SQL Server 2008 Database Diagram to another DB?

Even via visual-Data tool you can :

https://technet.microsoft.com/en-us/library/ms175868%28v=sql.100%29.aspx

Updated Check this link :

http://forums.asp.net/t/1790223.aspx?Making+ER+Diagram+from+SQL+Server+2008+Database How do you migrate SQL Server Database Diagrams to another Database?

https://superuser.com/questions/578090/how-to-database-model-in-visio-2013

http://www.codeproject.com/Articles/15080/Script-SQL-Server-diagrams-to-a-file

Community
  • 1
  • 1
Ajay2707
  • 5,690
  • 6
  • 40
  • 58
  • Hi Ajay sorry if I wasnt clear. copying diagrams is not my issue. I did this but the relationships did not come across. My question was how to do this and retain the relationships? Is this even possible? – Mat41 May 18 '15 at 05:28
  • Ok, is the script not help you? and what about last link? – Ajay2707 May 18 '15 at 05:36
  • Let me try the last option to check. But I suspect me copying them from database A.sysDiagrams to database B.sysDiagrams fails to bring relationships I doubt copying them using management studio UI will. In your opinion should primaryKeys and foriegn keys come across when copying diagram from database A to B?? – Mat41 May 18 '15 at 05:39
  • No the outcome is the same. I am starting to think copying a database diagram never carries PK's and FK's (although I am not finding anything that states this ) Does anyone know if this is possible or am I looking at wrong method? – Mat41 May 18 '15 at 05:49
  • BTW the last link was no good. its all about copying tables between diagrams. If you read my original post again this is not what I am trying to do – Mat41 May 18 '15 at 05:50
  • ok, I will check again... I fill the script will help you.no issue. – Ajay2707 May 18 '15 at 05:57