Deploying changes from scripted database files

In the previous tutorial, we showed how to work with database changes together with DB Version Control tool.

Keeping database changes in the version control system is great but the ultimate goal is to deploy database changes automatically.

In this tutorial, we will demonstrate how to deploy a database from the scripted database files.

Migrations

In short, DB Version Control does two things:

  • generates migration scripts automatically
  • scripts individual database objects to files

However, to deploy database changes, only the migrations part is needed - the migrations allow the database deployment process to be repeatable and automatic.

The migrations are all files under Migrations directory and sub-directories.

Migration runner

The way migration scripts are executed is critical to have repeatable and automated deployments. This is what a free tool, Migration runner, does. It is based on DbUp change script runner with a couple of additional features.

The migration scripts could be executed with other tools as well (as they contain SQL commands) but Migration runner:

  • ensures that the scripts are applied in the correct order and script output stored in dbup.Versions table
  • supports variable substitution
  • executes each migration script inside a transaction (unless explicitly specified to run without a transaction)

The Migration runner could be executed from the command prompt, MSBuild task, or PowerShell script - we will go through each available option.

Before continuing with this tutorial, download Migration runner and extract the zip archive. We will be creating a new database from the scripted database files on the same server as AdventureWorks database.

Deploy from the command prompt

First, start the command prompt.

We will execute DbVersionControl.MigrationRunner.exe with the following parameters:

  • database server (-s)
  • database name (-d)
  • username (-u) - if SQL authentication is used
  • password (-p) - if SQL authentication is used
  • path to migrations directory (-f)

Here is how the command could look like.

When executing the command, you will see a lot of messages (printouts) and you should get a message (Upgrade successful) indicating that the commands succeeded.

To verify, we can check if the database exists and look into the dbup.Versions table to see which scripts were executed.

The tool creates a new database if it does not exist. If the database already exists, the tool will only apply those scripts that have not been executed yet.

In our case, if we re-run the same command again, no scripts should be executed because we did not add any new migration scripts.

Deploy from Powershell

Deploying using the Powershell command is very similar to deploying from the command prompt. The only differences are that we have to import Migrations module first and the syntax is slightly different.

The output messages are exactly the same as running from the command prompt.

Deploy from MSBuild

The same deployment can be executed with the MSBuild task. Here is an example how the task could be described. Note that Migrations.MsBuild.targets project should be imported first - only then MigrationRunner task is available.

To execute MSBuild task from the command prompt, we can use the following command.

Again, the output from the MSBuild task is exactly the same as in previous deployment options.

Summary

In this tutorial, we showed that:

  • only migration scripts and Migration runner (free tool) are needed in order to deploy database
  • multiple deployment options are supported - from the command prompt, using Powershell cmdlet and MSBuild task
  • we can review executed scripts in dbup.Versions table

To learn more, check additional documentation on:

Leave a comment