So you’ve created a diagram that you now need to transfer to a different server

or database. This can be done two ways. The easiest method to transfer your diagram

is to do a complete backup and restore onto the destination database. If you don’t

have the luxury of performing a complete backup and restore, then you’ll have to do

a very difficult task to transfer the diagram. After Service Pack 2 for SQL Server 7, you have the option to also DTS transfer the necessary table over, which includes everything you need to recreate your diagram.

Transferring a diagram through the other method is one of the most difficult

of tasks a DBA can do. It involves editing the system tables, selectively

inserting into another system table, and performing a lot of cleanup.

Since anytime you modify the system catalog is a risky procedure and has a learning curve, please only

practice this on a test database, such as Northwind. With that in mind, let's begin!

The first step is to create a duplicate of the Northwind database. Create a new database called NewNorthwind and then use

DTS to import tables from the Northwind to NewNorthwind. Do not perform a backup and restore since it would accomplish the same thing we're trying to accomplish.

Next click your right mouse button on the server and select properties. Check allow modifications to the system table.

In SQL Server 7.0, diagrams are stored in the dtproperties system table in each database. This system table has an

identity field in it, so to transfer over the information from the source database, we will need to turn IDENTITY_INSERT to ON.

This process can't be done to a SQL Server system table however. What we need to do next is "trick" SQL Server to thinking that

dtproperties is actually a user table. Connect to the database that you'd like to transfer the diagram to and make the dtproperties table

a user table. You can do this by running the following query:

UPDATE sysobjects
SET xtype = 'U'
WHERE name = 'dtproperties'

This is a great time to take a break and look at something interesting in SQL Server. If you go to Enterprise Manager, and look at the list of tables in the NewNorthwind database,

you will see that dtproperties is still flagged as a system table, even after the adjustment. The table's behaviour though is of a user table.

The reason for this is that dtproperties is actually hard-coded as a system table in Enterprise Manager.

Next, you will need to set the IDENTITY_INSERT property to true. This can be done with the following syntax:

SET IDENTITY_INSERT dtproperties ON

Setting the identity to true allows you to transfer into the table the necessary information and override the ID field. You

will now need to find out which diagrams you will want to transfer. Use the following query

to list all the fields in the dtproperties table.

SELECT id, objectid, property, value, lvalue, version
 FROM northwind.dbo.dtproperties

Each diagram has seven rows assigned to it. Abbreviated results are below:

id          objectid    property            value                                  
----------- ----------- ------------------- ---------------
6           6           DtgSchemaOBJECT     NULL                                   
7           6           DtgSchemaNAME       Relationships                          
8           6           DtgSchemaGUID       {EA3E6.....} 
9           6           DtgSchemaBYTES      14336                                  
10          6           DtgSchemaDATA       NULL                                   
11          6           DtgDSRefBYTES       762                                    
12          6           DtgDSRefDATA        NULL                                   
(7 row(s) affected)

To find which diagram you'd like to transfer, look in the property column for the DtgSchemaNAME value.

Once you find that value, look one more column over and see that the name of diagram is Relationships.

The objectid for relationships is 6 also. So, you will want to transfer over all records with the objectid of 6

to the destination database. Before doing this, make sure that the destination database does not have an object with the identifier of 6

and watch that id column as well. Once that is established, use the following syntax to transfer the diagram:

INSERT INTO northwindnew.dbo.dtproperties
 (id, objectid, property, value, lvalue, version)
  SELECT id, objectid, property, value, lvalue, version 
  FROM northwind.dbo.dtproperties

It's now time for cleanup. First check your results. You should now see the Relationships diagram in the

northwindnew database. Set the INDENTITY_INSERT property back to false now:

SET IDENTITY_INSERT dtproperties ON

Don't forget to also set the dtproperties table back to a system table.

UPDATE sysobjects
  SET xtype = 'S'
  WHERE name = 'dtproperties'

Finally, change the server settings to disallow any system catalog modifications. As always, before you try any of this backup your database and

never experiment on a production server. This example in the future will be obsolete once you upgrade to Service Pack 2 or SQL Server 2000.