How do I move diagrams between SQL Server databases?

Question

I'm trying to move a database from a hosted server to my own SQLExpress instance. I used the Database Publishing Wizard to create the script... but the diagrams were not transferred.

Then, I found and followed the advice of another answer on this site that had me import/export the data to the sysdiagrams table.

When I do that, I see all of the diagrams in the Diagrams folder. However, when I try to open one, I see an outline of each table, then get a popup error message that says:

"Table(s) were removed from the diagram because privileges were removed to these table(s) or the table(s) were dropped."

When I exit from the popup, all of the tables are removed from the diagram (except for a couple of aspnet_xyz membership tables that were part of one diagram).

Do you know how I can transfer the diagrams without receiving this message?

Answer 1

The simplest way is to create a backup of the whole database and restore it to your Express database. You are getting the error because the diagrams are no simple drawings - they are just some metadata and they rely on the tables they show - you need the tables shown by the diagram. If you don't want to modify the diagram, just export it as an image.

Answer 2

I have seen something similar to this before, it wasn't quite the same but perhaps it's close enough to apply to you.

It is likely that the trouble that you were having initially was to do with user privileges being tied to the old server in some way.

Refer to http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=120849&SiteID=1 and this: http://msdn2.microsoft.com/en-us/library/ms143684.aspx.

• You will need to stop your "SQL Server (SQLEXPRESS)" service.

• Then delete the "\Documents and Settings\Username\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS" folder.

• Restart SQL Server.

• Restart Visual Studio.

I hope that helps.

Answer 3

This problem stopped when I re-scripted the entire database using the Database Publishing Wizard... This time I specified "Schema Only" instead of "Schema and Data." After I did this, I was able to successfully import and open the diagrams using the Import/Export wizard.