Creating the baseline script

When generating a baseline script, SQL Change Automation will generate a single migration script for all the objects in your target database. If programmable objects are enabled, these objects will appear in both the initial migration script and in separate programmable object files. There are some cases where programmable objects get added to the unsupported folder instead of the programmable objects folder. As these objects are included in the migration script, they will still deploy correctly. You cannot make development changes to unsupported objects in the same way as other programmable objects, so we strongly recommend resolving these issues before deploying.

Editing the baseline script

You can manually amend the baseline script after the process has completed. You may need to do this if you have schema level objects that depend on server level objects that don't exist on your development server. Verifying the project will help you identify these issues.

Note that if programmable objects are enabled, any edits to them will need to be made both in the baseline script and in the individual programmable object scripts.

The baseline script can also be renamed (as can its folder) or split into multiple files before deployment. All migration scripts which appear in the first folder (ordered by file path) will be treated as baseline migrations.

Applying a baseline script

When a baseline script is generated, it will not be automatically applied to your development database if it is empty. This provides the opportunity to edit the script if necessary.

If the development database is not empty, the script (and all programmable objects) will be marked as applied, as it will be assumed that the development database is based off the target database. Any divergences will then show up as differences.

Filters

It is very likely when baselining that you will want to configure filters to exclude certain objects from being handled by the baseline and subsequent migrations.

Note that filters are taken into account when:

• Performing the initial import
• Determining if a database is empty
• Applying
• Deploying
• Testing for drift when deploying

Deploying a project containing a baseline

Baselined projects can be deployed using the automation components. The baseline script will be run if the deployment target is an empty database.

If you deploy a project to a database which is not empty, it is assumed that you are deploying the project onto the environment from which the baseline script was created. In this case, the baseline script will be marked as deployed and not run. If you have added any additional migration scripts or programmable objects to your project, those will still be applied.

It is strongly recommended that when deploying to a non-empty database that the database has a schema consistent with the baseline. If you deploy to an environment that is inconsistent, the initial or subsequent deployments to this environment could fail if a migration references an object that differs from the expected schema at that point. This issue may be encountered either due to environment drift, or deploying to environments that are different from production, such as QA. We suggest one of the following alternatives to address these scenarios:

• Replace the database with a new, empty database, which you deploy to from SQL Change Automation
• Provision a new database based on a backup which has the correct baseline schema
• Use SQL Compare before the first deployment to ensure that the populated database schema matches the baseline schema, and bring it into alignment if needed
• Identify and resolve drift

Configuration

Configuration should be handled automatically if performing baselining through the UI in SSMS or Visual Studio.

The Baselining property must be set to FolderBased in the project file for baselining to be enabled.

Learn more

• Setting up a project with an existing database in SSMS
• Setting up a project with an existing database in Visual Studio