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.
