Skip to main content

Copying Database Diagram from DB server to another ...

For some, working with Databases is fun and for others, it could possibly be a nightmare. Understanding the structure of a Database schema from the available documentation could spell doom for many developers.

A picture is worth a thousand words. This golden saying applies aptly to Databases! Yes, often, a pictorial representation of a Database conveys more meaning about the Database Schema than the SQL scripts generated or other documentation that may be available. One of the first thing developers wants to do when starting work on a new project is to understand the Database schema. Sometimes, understanding a complex database schema can be simplified largely by a relationship diagram.

The Database diagram tools in the SQL Server enable administrators and developers to create Database diagrams very easily. This article demonstrates an efficient way to move DB diagrams from one Database to another.

We are going to use two sample Databases for this purpose. They are named PlayDB and PlayDBMirror for sake of simplicity and clarity. Let us create some sample tables and one sample diagram called “sample1” in the Database PlayDB. The objective is to copy the diagrams from this database to PlayDBMirror. Let us see how this can be accomplished.

Note: It is not necessarily that you need to create a DB called PlayDB in your server. You can also work with Northwind or Pubs database. The bottom line is don’t use the production server to test these out.

Run through the steps from 1 to 8:

1. Create a new Database by right clicking on "Databases" in Enterprise Manager and name it as "PlayDBMirror".

2. Now right click on your source Database (for me it is "PlayDB")

3. Choose "All Task" >> "Export Data" and choose the source and destination DB.

4. In "Specify Table Copy or Query screen" choose the second option "Use a query to specify the data to transfer" and click next.

5. In the next screen type the following query "select * from dtproperties" (Note: this would try and push ALL records from source dtproperties to destination dtproperties)

6. In "Select Source Tables and Views" click on the "Results" under heading destination, and then manually change it to dtproperties.

7. In the next screen choose "Run Immediately" check box and click on Next.

8. That’s it click on "Finish" on the last screen.

Now, we have successfully copied all the database diagrams from PlayDB to PlayDBMirror. Let us suppose we have somehow modified the database diagrams and want to move them again. Running through the steps from 1 to 8 will accomplish the task and lo here is the catch. If you check the diagrams section in the destination database (PlayDBMirror), we see the diagram sample1 copied successfully.

The internals

What happens internally is that the diagrams are stored in a table called dtproperties.

Each diagram internally has 7 rows in “dtproperties” with unique “objectid”. They are listed below for the purpose of completion:

1. DtgSchemaOBJECT
2. DtgSchemaGUID
3. DtgSchemaNAME
4. DtgDSRefBYTES
5. DtgDSRefDATA
6. DtgSchemaBYTES
7. DtgSchemaDATA

Excuse me, did I say unique objected?! Now here is where you need to pay attention. If this Database is queried, you will find that there are 14 rows!! Which means there are two diagrams now! How strange? The records were appended to the table during the second copy operation and were not overwritten! Why? And guess what, all of them have the same object id. Which means, for one diagram, there are 14 rows in the table dtproperties. In other words, there are two unique object Ids.

Let us analyze and figure what would happen if two diagrams have the same ID.

1. Double click on any one of the diagrams
2. Make some changes to it, save and close it.
3. Open the other diagram the same changes would reflect there also. Similarly if you drop one diagram the other one would also get delete. Believe me!! Rarely it has thrown some errors also for me in this step. And I have been left with no other alternative than to delete both diagrams.

The solution using Query analyzer

To move all the existing diagrams, we do this:

Insert into dtproperties
(
objectid,
property,
value,
uvalue,
lvalue,
version
)
Select
objectid,
property,
value,
uvalue,
lvalue,
version
From
PlayDB..dtproperties
Where
value not in (select value from dtproperties)


This query when run from the Query analyzer of the target Database ensures that all the diagrams are copied to the target Database but ensures that if the target Database already has a diagram with the same name, it doesn’t get copied.

Let us try to copy selected diagrams from the source to the target Database.

First, to decide on the diagrams that you want to copy, run the query

Select objectid, property, value from playdb..dtproperties

In the output of this query look in the "property" column for "DtgSchemaNAME" value. The DtgSchemaNAME property would have the name of the diagram; use this in conjunction with the objectid column to locate the diagram you would like to transfer.

We should also check the dtproperties table of destination Database before transfer. Using the above query, check that the destination dtproperties does not already have the objectid, which you are going to transfer now. If it does, get the maximum id from the destination table and use it within your select statement. Use that max value in the below query to be 100% sure its unique within the dtproperties table in the destination Database. For the sake of discussion let us use an arbitrary number 100 as my objectid.

Insert into dtproperties --- this is the table in the destination db.
(
objectid,
property,
value,
uvalue,
lvalue,
version
)
Select
100, -- Object ID
property,
value,
uvalue,
lvalue,
version
From
PlayDB..dtproperties
Where
objectid = 1
--- Replace this number with the object ID of the diagram which you want to transfer

Conclusion

Backing up the source database and restoring it in the destination database can accomplish the same task. But the crux of the matter is copying specific diagrams from one Database to another. Happy programming!

Technorati tags: ,

Comments

Popular posts from this blog

Script table as - ALTER TO is greyed out - SQL SERVER

One of my office colleague recently asked me why we are not able to generate ALTER Table script from SSMS. If we right click on the table and choose "Script Table As"  ALTER To option would be disabled or Greyed out. Is it a bug? No it isn't a bug. ALTER To is there to be used for generating modified script of Stored Procedure, Functions, Views, Triggers etc., and NOT for Tables. For generating ALTER Table script there is an work around. Right click on the table, choose "Modify" and enter into the design mode. Make what ever changes you want to make and WITHOUT saving it right click anywhere on the top half of the window (above Column properties) and choose "Generate Change Script". Please be advised that SQL Server would drop actually create a new table with modifications, move the data from the old table into it and then drop the old table. Sounds simple but assume you have a very large table for which you want to do this! Then it woul

AWS fatal error: An error occurred (400) when calling the HeadObject operation: Bad Request

While using AWS and trying to copy a file from a S3 bucket to my EC2 instance ended up with this error message. Command Used: aws s3 cp s3://mybucketname/myfilename.html /var/www/html/ Error: fatal error: An error occurred (400) when calling the HeadObject operation: Bad Request The error goes off if we add the region information to the command statement. I am using Asia Pacific (Mumbai) so used ap-south-1 as the region name. Modified Command: aws s3 cp s3://mybucketname/myfilename.html /var/www/html/ --region ap-south-1

[Non Tech] Want to know the recipe for Omelette :)

Fed up with Bread - Jam and Curd Rice, today i wanted to eat Omelette. Interesting part is I wanted to cook it myself :) So in the first picture you see all the items which are needed for preparing an Omelette. When I had a closer look at the eggs I see that almost all the eggs are broken. But believe me when I bought it couple of days back it was in perfect condition! I was wondering whether the eggs have become rotten or pretty old to consume! I tried taking an egg and break it but couldn't break it at all :) Since I have kept in the freezer all the eggs have frozen and looked like a iron ball :) After trying for few minutes of trying i removed the shell of the egg and then kept that iron ball :) into a bowl and placed it within Oven. I heated it for 1 minute and checked. It melted only to a limit. So i just set it for another 2 minutes and checked it later. It has melted but the part of the egg white has become a Omelette :( I didn't leave it there. I took the bowl out of