Database Deployments with Automatic Rollbacks, part II: Packaging the Database Updates

This post is part of a series on deploying database updates with automatic rollbacks using EF Core Migrations and Octopus Deploy. In this part of the series, we’ll bundle the database updates in a NuGet package that requires no extra tooling other than what already exists on the database server.

All posts in the series:

Part I: Where We Are, and Where We Want To Be
Part II: Packaging the Database Updates (this post)
Part III: Deploying the Database Updates
Part IV: Adding Automatic Rollback of Application Services
Part V: Summary of the Series

All the scripts in this series are available in this gist.

Requirements on Our Package

We want our deployment to fulfill the following requirements:

  • Each migration is applied in its own transaction, so the database never ends up in a state where only part of a migration was applied.
  • Deploying the migrations requires no extra tooling on the server. (We’ll assume that the Octopus tentacle and SQLCMD.exe are available.)
  • The deployment is smart enough to skip the step entirely if all the database is up to date.

Wrapping Migrations in a Transaction

The dotnet ef  utility can’t generate scripts that include transaction boundaries, so we’ll create a small wrapper script that uses SQLCMD directives to wrap another script in a transaction:

:On Error Exit

:r $(ScriptFile)


Next, we’ll generate one script file for each migration. dotnet ef  sadly can’t do this out of the box either, but it’s quite easy to loop over all migrations and do it manually:

Write-Host 'Building project...'
dotnet restore
dotnet build

$migrations = dotnet ef migrations list --no-build
$previous = '0'

foreach ($migration in $migrations)
   Write-Host "Scripting $migration..."
   dotnet ef migrations script $previous $migration --idempotent --no-build --output "./scripts/$migration.sql"
   $previous = $migration

Now, we can issue the following command once for each migration to apply it within the context of a transaction:

SQLCMD.exe `
    -S <server> -d <database> `
    -i TransactionWrapper.sql `
    -v ScriptFile = "scripts/<migration>.sql" `

Finally, we’ll package all the scripts in a NuGet package using the following nuspec file:

<!-- MyApp.nuspec -->
<?xml version="1.0" encoding="utf-8" ?>
<package xmlns="">
   <!-- ... -->

   <file src="scripts\*.sql" target="scripts" />
   <file src="Deploy.ps1" target="" />
   <file src="TransactionWrapper.sql" target="" />

and add a command to actually create the package to the bottom of Package.ps1:

nuget pack MyProject.Db.nuspec -Version $Version -NoPackageAnalysis

You’ll note that we haven’t yet created the script Deploy.ps1 referenced in the nuspec file. That’s the topic of the next post in the series.

Leave a Reply