Toad - Tips N Tricks - Pushing your dev database to production

The time has come to finally move all the awesome changes that you have made in your development database to a production database. Your production database is live and you need to merge your changes from development into production..... I hope you kept track of all the changes you made to your development database.

What is that you are saying? You didn't? Oh! you forgot! No worries, Toad comes to the rescue.....

Toad allows you to compare 2 schemas using the Database Compare tool. (Database->Compare->Schemas)

There are 3 tabs in the Compare tool.

In the first tab you set up the schemas that will be compared. The second tab allows you to define what parts of the schema should be compared (tables, packages, etc). The object set allows you to define filters which will be used in selecting objects that need to be compared (useful if you use a standardized naming convention on your db objects).

To use the Compare Schemas tool do the following:

1. Set the source schema.
1. If you already have a connection open this will default to currently open connection.
2. You can also choose any other valid connection you have in Toad.
2. Define the target schema as well as the folders where all the output files will be stored.
3. Under options select those objects that you wish to be compared.
1. The options tab has 4 nested tabs, each one allowing you to customize exactly which objects need to be compared.
2. The most important tab is the first one "Object Types to Compare"
   
3. The second tab "Object Type Specific Options" is equally important as it allows you to specify what needs to be ignored in objects such as your PL/SQL scripts when the comparison is made.
   
4. Once you have set up how Toad should compare your schemas hit the "Begin Schema Compare" button .
5. This will result in 2 new tabs being added to Toad Compare space.
   
1. The result tab shows you in a tree what is different as well as similar between the 2 schemas.
   
   This will allow you to quickly jog your memory about what all changed between your development and production schemas.
   You can also output the differences to an excel sheet or a HTML report.
2. The more interesting tab is the Sync Script tab.
   The sync script tab has a script that you can run on the target schema to make it look like the source schema. This sync script is a time as well as a life saver, as it cuts down the time required to modify your production schema as well as reduces the number of errors and mistakes that you might make if you had to do this manually.
3. But do your due diligence and read each line of code in the Sync Script and make sure that each statement is doing something to the production schema that you expect.
4. I suggest that you have a test schema which mirrors your production schema. Make sure that you are first able to migrate your changes from development to test. Have everybody (developers, testers and users) test the test schema. Once you are sure that your test schema is indeed working correctly you can run your sync script on your production schema.
5. An Important Note: The Sync Script is only available in the DBA version of Toad. Without it you will be able to view the sync script but not save it or run it.
   To find out if you have the correct license to save the Sync Script, run the Toad Support Bundle Tool (Help -> Support Bundle).
   
    
   If you see the "DB Admin Module", then you can save the Sync Script, otherwise when you hit the save button, Toad will write out the first few lines of comments from the Sync Script and stop without any error message. (For the longest time ever I though Toad had a bug, until one of their tech support people straightened it out for me).
   I wish this feature was part of a lower bundle and not isolated to the DBA bundle - as I think developers will find it useful to parse the script and then provide it to their DBA to run on the schema. (Quest software are you listening?)

The Compare tool has been a huge time saver as well as a life saver to me. And now that you know that this feature exists in Toad, I hope it does the same for you as well.