Database Deployments with Automatic Rollbacks, part III: Deploying the Database Updates

This post is part of a series on deploying database updates with automatic rollbacks using EF Core Migrations and Octopus DeployIn this part of the series, we’ll create the actual deployment script which will run on the server.

All posts in the series:

Part I: Where We Are, and Where We Want To Be
Part II: Packaging the Database Updates
Part III: Deploying the Database Updates (this post)
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.

Step 1: Determine what migrations to apply

Before we do anything else, we want to determine whether we actually need to do any work. If the database is already up to date, we can skip the rest of the steps. In order to do that, we need to determine what migrations are already applied and what migrations are available. However, we can’t use the dotnet ef  utility for this, because we don’t want to have to install that on the server. Instead, we’ll figure it out by looking at the bundled script files, and in the database.

 $Database = $OctopusParameters['Database'],
 $Server = $OctopusParameters['DatabaseServer'],
 $BackupLocation = $OctopusParameters['DbBackupLocation']

function Get-AppliedMigrations {
 $migrationsTableExists = (
Invoke-SqlCommand `
-Query @"SELECT CASE WHEN EXISTS (SELECT * FROM [sys].[tables] WHERE [name] = '__EFMigrationsHistory') THEN 1 ELSE 0 END AS MigrationsTableExists").MigrationsTableExists -eq 1

 if ($migrationsTableExists) {
   $appliedMigrations = Invoke-SqlCommand -Query 'SELECT MigrationId FROM [__EFMigrationsHistory]' | Select-Object -ExpandProperty MigrationId
 else {
   $appliedMigrations = @()

 return $appliedMigrations

function Find-PendingMigrations {
 $appliedMigrations = Get-AppliedMigrations
 $allMigrations = Get-ChildItem "scripts" -Name | ForEach-Object { $_.Replace(".sql", "") }

 $allMigrations | Where-Object { $_ -NotIn $appliedMigrations }

The Invoke-SqlCommand  function is just a convenience wrapper around SQLCMD, and is included in the gist.

With that in place, we can list all the pending migrations, and exit early if the list is empty:

$pendingMigrations = Find-PendingMigrations

if ($pendingMigrations.Length -eq 0) {
  Write-Host "No pending migrations - we're done!"


Step 2: Back up the database

Backing up is easy, with the Backup-SqlDatabase  cmdlet:

$BackupFile = "$BackupLocation\ActDb_backup_$(Get-Date -Format "yyyy-MM-dd_HH-mm-ss").bak"

 Write-Host "Backing up $Server\$Database to $BackupFile..."
 Backup-SqlDatabase `
   -ServerInstance $Server `
   -Database $Database `
   -BackupFile $BackupFile `
   -BackupAction Database `
   -CopyOnly `
   -ErrorAction Stop `
 Write-Host "Backup done!"

Step 3: Apply pending migrations

For this, we’ll just loop through the list of pending migrations and execute it inside a transaction, as outlined in Part II of this series.

try {
   foreach ($migration in $pendingMigrations) {
     Write-Host "Applying $migration..."
     SQLCMD.EXE -S $Server -d $Database -i ./TransactionWrapper.sql -v ScriptFile = "scripts/$migration.sql" -b
     if ($LASTEXITCODE -ne 0) {
       throw "$migration failed!"
   Write-Host "All migrations applied!"

Step 4: In case of failure, restore the backup

A backup is only useful if we restore from it when something goes wrong. In order to restore it, we first need to take it offline, and then take it online again after restoring.

catch {
   Write-Warning "DB Migration failed; restoring from backup..."


   Restore-SqlDatabase `
     -ServerInstance $Server `
     -Database $Database `
     -BackupFile $BackupFile `
     -ReplaceDatabase `
     -ErrorAction Stop `

   Invoke-SqlCommand -Query "ALTER DATABASE [$Database] SET ONLINE"

   Write-Host "Backup restored!"
   throw $_.Exception

Of course, you should verify that this works on your setup, e.g. by deploying a migration that throws an exception. If you have everything in place, you’ll see a log message from the failing migration detailing what went wrong, and the database will be restored to its previous state.

Step 5: Configure Octopus to Run the Deployment

In order to actually run the deployment, add a step to your deployment process which deploys the NuGet package we created in the previous post in this series. Octopus will automatically run Deploy.ps1  from the root of the package, which is precisely what we want.

Leave a Reply