When getting started with existing databases, we recommend creating a baseline script that reflects the state of your objects and reference data that have already been deployed to Production so that you can generate new migrations from this point.  A baseline script is required to bring the Shadow Database up to the current state of Production so that subsequent migration scripts just include changes from this point onwards.  A baseline script is also necessary to spin up new  environments for testing purposes or when deploying new installations.

The easiest way to create your baseline script is by having all your environments (eg, Dev, Test, Staging, Prod) in sync.  You can use Schema Compare for Oracle, which is part of the Deployment Suite for Oracle, to make sure all your environments are in sync.  There is a 28-day free trial for the Deployment Suite for Oracle on our website.

Contents

Creating a baseline script as part of the New Project wizard

1. Open Redgate Change Control and click New project...
   
   
   
2. Proceed through the wizard to the final step, where you will be prompted to create a baseline script.
   
   
   
3. Click Connect to database. You will be shown a connection dialog.
   
   
   
4. Enter the credentials for the database you want to use as the baseline, then click Choose Database. If the databases across your environments are in sync, then you can use your development database for this.  You can optionally customize the version number and description in the filename.  Click Create and a baseline script will be generated.
   
   
   
5. You may want to customize your baseline script if it contains environment-specific information such as removing users and permissions that don't belong to the baseline script.  You may also include additional INSERT statements to populate static data (you may find Data Compare for Oracle, which is also part of the Deployment Suite for Oracle, useful to generate these scripts).
6. If your development database is empty you can bring it inline with the baseline by manually running the baseline script against your development database

Creating a baseline script for an existing empty project in Redgate Change Control

1. Open Redgate Change Control and open your empty project. Navigate to the Generate Migrations tab.
   Note: You can only generate a baseline for a project with no migrations.
   
   
2. You will see a banner prompting you to baseline. Click Baseline
   
   
   
3. A dialog will appear allowing you to fill in the connection details for the database you want to use as your baseline. See the 'Creating a baseline script as part of the New Project wizard' section above for details.
   
   
   
4. Once the details have been filled in click Baseline to generate the baseline script.
   
   
5. You may want to edit your baseline script if it contains Production only information like usernames or permissions that shouldn't be part of the baseline script.  You may also want to include additional SQL statements to populate static data.
   
   
6. If your development database is empty you can bring it in line with production by manually running the baseline script against your development database.

Creating a baseline script outside of Redgate Change Control

First, create a new project.

1. Use Redgate Change Control to create a New project by following the steps in the wizard. Do not generate any migrations yet.  Take a note of the Migrations Folder location for your scripts.  
    → 
   
   

Second, create your baseline script. 

If your environments are in sync, then you can use your development database to create the baseline script.  There are a few ways to do this:

1. Use Oracle SQL Developer to export the Database into a script.  Follow Tools > Database Export...
   
   
2. Or, use Schema Compare for Oracle to create your baseline script. 
   1. Launch Schema Compare for Oracle and do a comparison between your development database and an empty database. In this example, DEV_SHADOW is currently empty.
      
      
      
   2. Click on the checkbox at the top to include all objects and then click Deployment Wizard…
      
      
      
   3. Select Create a deployment script and click Don’t worry about specifying what to open the script in for now.
      
      
      
   4. The next screen gives you a preview of the script along with tabs to see a summary and any warnings. Click Save Script...
      
      
      
3. Or, use the Schema Compare for Oracle cmdline to generate a script from your development database to an empty database.  You can learn more about the Schema Compare for Oracle cmdline at https://documentation.red-gate.com/sc14/using-the-command-line.

Third, save your baseline script and add it to your project folder.

1. Save this script starting with a capital V, a number, two underscores, a description, and dot sql (eg, V001.001__BaselineScript.sql).  The V is a naming convention that signals this is a Versioned Migration script, which is only run on a target database if it hasn't been executed on that target yet.
   1. We recommend the leading zeros so that the scripts appear in order when looking at them in your filesystem.
   2. If you have any configuration data (also known as seed data, static data, or data in lookup tables), you would want to add this into the baseline script as well. You can use Data Compare for Oracle to help script this out.
      
      
2. If you used Schema Compare for Oracle to create your baseline, remove “SET DEFINE OFF” at the top of the script after the comments. (This is currently causing an issue.  Please let us know if you need this.)
   
   
3. Copy your baseline script into the Migrations folder for your project that you specified above.  You can jump directly to this folder by clicking on the path at the top of the Generate migrations tab.  You can create a folder called Baseline in your Migrations folder, if you’d like to keep it separate from your other scripts.  
   
   
4. Make sure the script is encoded in UTF-8.

Fourth, setup the baseline on your development database.

You need to baseline your development database so that this baseline script isn't re-applied to it.  If your entire team is working against the same shared development database, then only one person on the team needs to perform this action.

1. In Redgate Change Control, refresh your migrations tab.  Your new baseline script will appear as Pending.
   
   
   
2. Open a Windows cmd prompt to baseline your development database.
   

3. Run the RCA baseline verb and set the --target argument to your development database's JDBC url.
   Note: you don’t have to do anything to the Shadow Database, just baseline your development database.

   
   
4. In Redgate Change Control, refresh your Migrations tab. You can now see that your baseline has been set because the status is Baseline.
   
   
   
5. If you go to the Generate migrations tab, there should not be any changes.
   
   

That's it.

You can now make changes to your development database and then generate new migrations. In this example, I used Oracle SQL Developer to add a new PAYMENTS table.  When I clicked Refresh on the Generate migrations tab, I now see my database changes that are ready to import into my project.