import database diagram

[AccessSQLUser]

I am importing objects from one database to another database on a different server using DTS. Basically I want an exact copy of the database without the data. This works okay except that the database diagram didn't import. Is there a reason for this?

 

[ColinM]

You could try scripting all objects concerned then run the script against the other database. This will ensure you get the foreign key constraints, which the diagram shows.

 

[AccessSQLUser]

I'm not sure I completely understand what you mean by scripting all the objects. All my relationships on the tables were imported and everything looks fine but just the diagram itself seems to have not been imported. I guess I could recreate the diagram but I would really rather not do that.

 

[tlbroadbent]

Diagram data is stored in the dtproperties table in each database. After transferring other database objects, transfer dtproperties. It should not exist in the destination database. If it does, drop it or truncate the data. Then perform the appropriate DTS transfer.

---------------------------
The following info was posted by Bob Pfeiff (MCSD, MCT, SQL Server MVP) in the microsoft.public.sqlserver.server forum.

Diagram data is stored in a system table called dtproperties which doesn't exist until you click on Diagrams in the Console Tree under that database. You need to move the data in the dtproperties table in your source database to the destination separately from the DTS transfer objects task. You can use bcp to export from the source and bcp or BULK INSERT to import the dtproperties data to the destination, or if you're using the same server for both databases this will work:

set identity_insert pubs1..dtproperties on

insert into
pubs1..dtproperties
(
id,
objectid,
property,
value,
lvalue,
version
)
select
*
from
pubs..dtproperties

Either approach can be built into a DTS package. If you move diagram data to a database that doesn't actually have the objects depicted in the diagram, you won't be able to open the diagram (it will attempt to draw the picture, but won't work).[/i]

---------------------------
Umachandar Jayachandran provides a another transfer script here.

http://www.umachandar.com/technical/SQL70Scripts/Main18.htm

Terry

X-) "Life would be easier if I had the source code." -Anonymous