SQL Change Automation can produce two types of SQLCMD-compatible artifacts which allow you to handle the deployment of your migrations in different ways:

Patch deployment script
Package deployment script

For a full list of build artifacts see Continuous integration.

Patch deployment scripts

Patch deployment scripts are single-use scripts which can be used to bring a given database environment up-to-date.

Patch scripts are produced based on a specific SQL Server instance and database name combination, so they will only contain a delta of migration scripts that have yet to be deployed to the specified database. Any migrations that have already been deployed to the database will not be included in the patch script.

Patch scripts are produced whenever you deploy your database within Visual Studio (e.g. when you build your solution after updating from source control), and can only be executed on the server/database that the script was produced against.

To find out how to deploy by patch, see Command Line Deployment (MSBuild).

Package deployment scripts

Package deployment scripts are multi-use scripts which can be used to bring all of your database environments up-to-date.

Instead of containing just a delta of migrations that have yet to be deployed, these scripts contain a concatenated set of all of the migrations within your project.

When the package script is executed against a given database, it will determine which migrations have yet to be applied and execute the appropriate scripts on-the-fly. Given that package scripts only need to be produced once, this makes them suitable for continuous delivery scenarios: rather than needing to produce a separate script for each environment (Dev/Test/Prod), a single script can be generated at build time and applied to all of your target databases.

To find out how to deploy by package, see SQLCMD Package Deployment.

Sample package script

Let's assume that we have a migration file with the below content:

Sample content of sql migration file

-- <Migration ID="42f0706e-b084-4da8-afe6-357b653c7bf9" />
GO
USE MyDatabase
SET ANSI_DEFAULTS, NUMERIC_ROUNDABORT ON
CREATE TABLE tab1(id INT IDENTITY(1,1))
SET IDENTITY_INSERT tab1 ON
GO
INSERT INTO tab1 (id) VALUES (1)
SET IDENTITY_INSERT tab1 OFF
UPDATE tab1 SET id = 2 WHERE id = 1
GO
GO
USE MyDatabase
GO
SET ANSI_DEFAULTS, NUMERIC_ROUNDABORT ON
GO
CREATE TABLE tab2(id INT IDENTITY(1,1))
GO
SET IDENTITY_INSERT tab2 ON
GO
INSERT INTO tab2 (id) VALUES (1)
GO
SET IDENTITY_INSERT tab2 OFF
GO
UPDATE tab2 SET id = 2 WHERE id = 1
GO
USE MyDatabase
SET ANSI_DEFAULTS, NUMERIC_ROUNDABORT ON
GO

SQL Change Automation will produce a package script that looks like:

Sample package deployment script with batches wrapped in EXECUTE() with single SET NOEXEC ON

IF DB_NAME() != '$(DatabaseName)'
    USE [$(DatabaseName)];

GO
IF NOT EXISTS (SELECT 1
               FROM   [$(DatabaseName)].[dbo].[__MigrationLogCurrent]
               WHERE  [migration_id] = CAST ('42f0706e-b084-4da8-afe6-357b653c7bf9' AS UNIQUEIDENTIFIER))
    PRINT '
***** EXECUTING MIGRATION "Y:\fakePath\myMigration.sql", ID: {42f0706e-b084-4da8-afe6-357b653c7bf9} *****';

GO
IF EXISTS (SELECT 1
           FROM   [$(DatabaseName)].[dbo].[__MigrationLogCurrent]
           WHERE  [migration_id] = CAST ('42f0706e-b084-4da8-afe6-357b653c7bf9' AS UNIQUEIDENTIFIER))
    SET NOEXEC ON;

GO
EXECUTE ('USE MyDatabase
SET ANSI_DEFAULTS, NUMERIC_ROUNDABORT ON
CREATE TABLE tab1(id INT IDENTITY(1,1))
SET IDENTITY_INSERT tab1 ON
');

GO
EXECUTE ('INSERT INTO tab1 (id) VALUES (1)
SET IDENTITY_INSERT tab1 OFF
UPDATE tab1 SET id = 2 WHERE id = 1
');

GO
USE MyDatabase;

GO
SET ANSI_DEFAULTS, NUMERIC_ROUNDABORT ON;

GO
EXECUTE ('CREATE TABLE tab2(id INT IDENTITY(1,1))
');

GO
SET IDENTITY_INSERT tab2 ON;

GO
EXECUTE ('INSERT INTO tab2 (id) VALUES (1)
');

GO
SET IDENTITY_INSERT tab2 OFF;

GO
EXECUTE ('UPDATE tab2 SET id = 2 WHERE id = 1
');

GO
USE MyDatabase;
SET ANSI_DEFAULTS, NUMERIC_ROUNDABORT ON;

GO
SET NOEXEC OFF;

GO
IF N'$(IsSqlCmdEnabled)' <> N'True'
    SET NOEXEC ON;

GO
IF NOT EXISTS (SELECT 1
               FROM   [$(DatabaseName)].[dbo].[__MigrationLogCurrent]
               WHERE  [migration_id] = CAST ('42f0706e-b084-4da8-afe6-357b653c7bf9' AS UNIQUEIDENTIFIER))
    PRINT '***** FINISHED EXECUTING MIGRATION "Y:\fakePath\myMigration.sql", ID: {42f0706e-b084-4da8-afe6-357b653c7bf9} *****
';

GO
IF NOT EXISTS (SELECT 1
               FROM   [$(DatabaseName)].[dbo].[__MigrationLogCurrent]
               WHERE  [migration_id] = CAST ('42f0706e-b084-4da8-afe6-357b653c7bf9' AS UNIQUEIDENTIFIER))
    INSERT  [$(DatabaseName)].[dbo].[__MigrationLog] ([migration_id], [script_checksum], [script_filename], [complete_dt], [applied_by], [deployed], [version], [package_version], [release_version])
    VALUES                                          (CAST ('42f0706e-b084-4da8-afe6-357b653c7bf9' AS UNIQUEIDENTIFIER), 'myChecksum', 'myMigration.sql', SYSDATETIME(), SYSTEM_USER, 1, NULL, '$(PackageVersion)', CASE '$(ReleaseVersion)' WHEN '' THEN NULL ELSE '$(ReleaseVersion)' END);

Please note that:

For the whole migration there is only one check whether a migration was already run. If it was run, the SET NOEXEC ON statement is executed, which prevents executing this migration. After the last batch of the migration, the NOEXEC option is turned off.
All batches are wrapped into the EXECUTE() statement, unless a batch contains only USE database or any of the SET option ON/OFF statements, including setting IDENTITY_INSERT, offset and statistics.
In case you're receiving an error stating that "Cannot insert explicit value for identity column in table 'testTable' when IDENTITY_INSERT is set to OFF.", make sure that the SET IDENTITY_INSERT testTable ON is the only statement in the batch or that you don't insert rows to this table in a separate batch than setting the IDENTITY_INSERT option.

SQL Change Automation 3

Deployment scripts

Patch deployment scripts

Package deployment scripts

Sample package script