Entity Framework Code First Migrations (CI/CD)

I have been increasingly using code first migrations in my projects, and have always struggled with how exactly to deploy schema updates to staging and live servers.

The current setup we have is Atlassian Bamboo and Octopus deploy (which has been brilliant so far), and I have been looking at ways of automatically running code first updates against the staging database. This is just a summary of the progress so far:

  1. Staging build is triggered in Bamboo by a commit on the master branch
  2. Bamboo triggers a new staging deployment.
  3. ?
  4. Profit.

Stage 3 needs to run in a safe automated manner. Some of our internally hosted beta sites have useful information in the database which I want to preserve.

My idea is to use a Powershell script stage on Octopus to

  1. back up the database
  2. run any migrations
  3. check everything is ok
  4. roll back the database if not
  5. roll back the application to a version which works with the existing schema

I found out it's possible to grab the migrate.exe application from the EntityFramework source folder and use this application to carry out migrations.
There seems to be a ton of caveats though, and my google-fu turned up tons of people who had struggled to get this working fully.

Things that I found important:
Make sure your contexts don't use a name parameter in the constructor eg: "name=MyContext"
Make sure you have a constructor that accepts a connection string, eg:

public class MyApplicationContext : IdentityDbContext<Account>
{
    public MyApplicationContext(string name) : base(name)
    {
    }

    public MyApplicationContext() : base("MyApplicationContext", throwIfV1Schema: false)
    {
    }
    // Your DB stuff here...
}

With your contexts set up like this, using migrate.exe, it's possible to migrate a database like this (powershell syntax)

.\migrate.exe MyApp.Data.dll MyAppConfiguration /startUpDirectory="C:\Temp\MyApp.Web\bin" /connectionString:"Data Source=.\SQLEXPRESS;Initial Catalog=MyAppDB;Integrated Security = true" /connectionProviderName:"System.Data.SqlClient"

This will allow you to run a specified configuration (MyAppConfiguration) in a specified dll with a connection string of your choice. This opens up a lot of options for automatically upgrading and downgrading databases with your CI/CD system..