I came across your SQL Deploy product, and it looks promising. My question is: how can I save such database changes as scripts in a standardized and convenient manner? I usually make changes to stored procedures and triggers using Enterprise Manager. But I am not familiar about generating diff scripts. I would appreciate if you could point me in the right direction.
The whole idea is that initially have a:
- Database Create Script - creating the database itself and inserting static data
- Database Change Scripts - to make database changes there after the initial release of the product (Create scripts)\
Here is a good approach to take:
- Script out your database in Enterprise Manager:
As per figure 1, in Enterprise Manager
- right-click on the database
- Click on 'All Tasks'
- Click on 'Generate SQL Script...'
Figure 1: Generating an initial SQL Script from Enterprise Manager.
Figure 2: Select all database objects as this will be the initial database creation.
- Save the script as version 1 script. i.e Version_001.sql
- Make a database change.
- Then Go to SSW SQL Deploy and create a new reconcile project as per figure 3. The reconcile functionality will display the difference (in sql code) between the current changed database and the original script(s) (created in step 1, etc...).
Figure 3: Create a new Reconcile project to reconcile the database with the scripts.
- After the reconcile is complete, Exam Diff will open up and show you the differences as per figure 4.
Figure 4: The differences will be shown in ExamDiff, this is to be copied out into a new file
- Copy the red script into a new file and this will be your new change script Version_002.sql
- Follow steps 3-6 each time you would like to make a product release.