When you create a SQL Change Automation project, you have the option to create a baseline schema by specifying a 'Deployment Target' in the SQL Change Automation initial setup wizard.

What is a baseline schema?

A baseline schema captures changes that have already been deployed to your production database. A baseline schema may be set so that future migration scripts only capture “deltas,” or incremental changes from this point forward.

When to create an initial baseline schema

We recommend creating an initial baseline schema when you are beginning a project for an existing database. The initial baseline schema captures the state of the production environment, which simplifies the process of deploying changes.

Drafting an initial baseline schema with the SQL Change Automation initial setup wizard may only be done when your SQL Change Automation project is first created. After you move forward with creating incremental migration scripts, you do not have an option to re-run the initial setup wizard for that project. You do, however, have the option to re-baseline throughout the future of your project.

Creating an initial baseline schema is optional, because there are cases when it is preferable to work without one:

•  You are working on new development for a project where there is no existing production database
  • See 'Getting Started with SQL Change Automation projects in Visual Studio' for a tutorial of this workflow
• You are experimenting and want to get the feel for migration scripts in a temporary project which you do not plan to use for deployments

How to create an initial baseline schema

To create an initial baseline schema, select your production database as the ‘Deployment Target’ when you run the SQL Change Automation setup wizard. See the example 'Working with an Existing Database' for a walk-through of the process of setting up an initial baseline schema with the wizard.

If you can’t access the production database for this task, you may also use a restored backup of the production database, or another environment where the schema matches the production database, such as a staging or pre-production environment, as the 'Deployment Target'.

Completing the setup wizard will result in a draft of an initial baseline script, which will be placed in a folder named ‘1.0.0-Baseline’.

Verifying and modifying an initial baseline schema

In some cases, you may have programmable objects such as views, stored procedures, and user defined functions in your production database which have a "dependency chain" scenario you need to address in the initial baseline script. Look for a folder named ‘Unsupported’ in your solution to see if this is the case. If it is present, see 'Resolving Unsupported Programmable Objects' for more details. If the ‘Unsupported’ folder is not present, you do not have one of these dependency chain scenarios in your project.

You may also need to modify the baseline due to problems in inconsistencies present in production code, such as a stored procedure referencing a full-text index that doesn’t exist. Errors of this type will appear with the error “Script verification failed” when you perform a refresh after drafting the baseline schema in SQL Change Automation.  Learn more about script verification.

When will the baseline schema script get deployed to a target environment?

By default, the baseline script will be deployed when you use SQL Change Automation’s automation components against an empty database, or you allow the SQL Change automation deployment to create an empty database.

One example of this type of automation component is the Use-DatabaseReleaseArtifact PowerShell cmdlet. You may also use a deployment tool with a Redgate SQL Change Automation add-on or plugin instead of calling PowerShell cmdlets directly. See ‘Deploying SQL Change Automation Projects’ for more information.

When you deploy a SQL Automation Project against a database which is not empty, the baseline script will not be run. Instead, it will be ‘marked as deployed’, and incremental migration scripts will then be run. Whether or not the “first folder” in your SQL Change Automation project will be considered a baseline is controlled by the ‘Baselining’ setting in the Project Settings of your SQL Change Automation project:

When this setting is enabled and you deploy to a database for the first time, it is important to validate that the database is empty or that the baseline schema has been fully applied to the database. This validation can be done with the SQL Compare tool, which may also be used to bring the environment in line with the baseline schema before your first deployment.

Baseline schemas and environment drift

One common customer question is, "What if I generate my baseline from production, and then deploy to another environment, such as QA, that is missing part of that initial baseline?"

In this example, the QA database is not empty, but it does not have the correct baseline applied. The baseline script will be marked as deployed and it will not run. If you have added migration scripts to your project, those will be applied, followed by any programmable objects.

If an error is encountered due to the baseline not being fully present, execution will stop. Providing you are using the default option to deploy your changes within a transaction, the entire deployment would then roll back. There is a potential that the initial deployment might succeed, if none of these changes reference the portions of schema which differ from the baseline, however an error may occur in a later deployment.

To avoid this risk, it is better to do one of the following:

• Replace the QA database with a new, empty database, which you deploy to from SQL Change Automation, then populate with test data as needed
• Provision a new QA database based on a production backup which has the correct baseline schema, and mask or obfuscate sensitive data as required for your organization
• Use SQL Compare before the first deployment to ensure that the populated QA database schema matches the baseline schema, and bring it into alignment if needed

How can I tell if my existing SQL Change Automation project has an initial baseline schema created?

By default, the initial baseline schema will be created in a folder named 1.0.0-Baseline. This folder resides in the migrations folder. The initial name of the baseline file will follow the pattern 001_YYYYMMDD-HHMM_username.sql.

Note that you may rename the initial baseline script, it need to follow the pattern shown in this screenshot.