Compare database between prod and dev

Database

Compare database between prod and dev

Hi All,

I need to compare all the database objects(tables,views,macros etc) and their definitions between prod and dev box. I can do it manually by using DBC tables. I am planning to do this by exporting data object names/definitions to a file and there by doing a file compare.

But, it will be time costuming process as i need to do manually and I am wondering if some one can help in suggesting a better way which will be faster and efficient.

Please put your thoughts here and you will be greatly appreciated for your help

Thanks.
7 REPLIES
Enthusiast

Re: Compare database between prod and dev

Well, AtanaSuite has a tool called the Delta Tool that does exactly this. You point it to the source (reference) and target (comparison) systems, specify the database(s) you want to compare and choose the attributes you want to compare. It then does a comparison and generates a report that shows the differences and a set of DDL statements to make them identical. I use it all the time.

Short of buying AtanaSuite, your best bet is to probably look at DBC tables and export the data from both systems and either use Excel or a file comparison tool like diff on *nix to match them up. This will tell you what's different and then it's up to you to create a script to change them.

A third option is to copy the DBC tables you want to compare (Tables, Databases, Columns, etc.) from your Prod system to your Dev system (in a separate table) and then use SQL to compare with the actual DBC tables in your dev system. It's probably a wash between this one and option 2.

Re: Compare database between prod and dev

Thanks Matt. I would like to go with the 3rd option. Thanks for your time.
Enthusiast

Re: Compare database between prod and dev

Hello , I need to compare and then replicate object between two systems, Actual Prod and DR.  Is there a way to identify missing /out of sync objects in DR system ?

Thanks,

14.10 user.

Senior Supporter

Re: Compare database between prod and dev

Hi Tdarc1,

as stated above:

AtanaSuite, good third party tool, offers this functionality.

If you are going to use Unity in the future you should also be able overcome this issue.

Otherwise compare dbc content, where this can become tricky in some areas - there exists more posts on this question in the form.

So, what is your real question?

Ulrich

Enthusiast

Re: Compare database between prod and dev

Thank you Ulrich, question  is  to keep the DR system in sync with production system . We have unity director and Dataloader. can you explain what functionality in unity  supports object sync between systems.    How do we keep objects definitions in sync (currently out of sync)

 looking if there is any automated process / manual script to do the comparison.

Thanks,

14.10 user.

Enthusiast

Re: Compare database between prod and dev

I am doing this with a Unix shell script which exports all object definitions to flat files considering differences between Teradata Releases.

In a second step I use Beyond Compare (http://www.scootersoftware.com) - they have a 30 day trial version - to compare the objects.

This approach is really fast and convinient for me. The shell script I can send you (send me a message).

Roland Wenzlofsky
New Member

Re: Compare database between prod and dev

Hi..can you pls send me the shell script as i have an idea of doing but got stuck on proceed further...