Click here to Skip to main content
15,867,453 members
Articles / Programming Languages / SQL
Article

Using FluentMigrator with MSBuild

Rate me:
Please Sign up or sign in to vote.
5.00/5 (9 votes)
12 Jun 2012CPOL11 min read 50.4K   412   22   6
MSBuild database migrations/upgrades with backup and restore functionality

Features diagram

Introduction

For all my database related projects, I like to have some way of keeping my db schema in sync with my code and generally maintaining the db. For the last 4-5 years and 10-15 projects, I have used a custom written tool (with an innovative name - dbtool), loosely based on Ash Tewari's DbUpdater.

For the new project, I wanted to try some new approach. Instead of doing SQL incremental change scripts, I've chosen to go with FluentMigrator. Now, since all my colleagues and myself are used to dbtool's functionality, I needed to re-implement several things using FluentMigrator. This is the required functionality:

  1. Upgrade db - migrate to the latest available version of the database
  2. Backup db - backup database to a zip file
  3. Restore db - restore database from zip file

All the functionality should be implemented through MSBuild script, making it easy for use in build environment like TeamCity, CruiseControl.NET, Hudson, etc.

Disclaimer: This isn't going to be a comprehensive article on FluentMigrator, nor MSBuild. For that, take a look at links at the end of the article, or do a Google search. This article is organized as a set of problems that I run into while implementing the mentioned functionality, and solutions to those problems.

So, let's begin...

Preparing the solution

Before running migration, we need to have a project with the actual migration code. You can download the source using the link on top of the article, or browse it on on GitHub. It contains an empty MVC web application, migration project, MSBuild scripts and some supporting tools.

Migration project is really simple. It contains Baseline class attributed with [Migration(0)] and Version001 class attributed with [Migration(1)]. As I already mentioned, I won't explain the FluentMigrator API here. For that, check the official documentation.

FluentMigrator and FluentMigrator.Tools NuGet packages are installed to the solution.

You will also need an empty database called FluentMigration in your SQL Server Express instance, or modify Database connection string in src/Migration.Web/web.config if you want to use another database name or another database instance.

There are two MSBuild scripts in the \src folder. Migration-initial.msbuild is the starting (almost) empty version of the build script, and Migration.msbuild is the finished version.

Once we have our solution ready, it's time to start working on those features.

Feature #1 - Upgrade database (run migration)

FluentMigrator has various migration runner tools, for command line, MSBuild and Nant. We'll be using it's MSBuild task called <Migrate>. Our first version of MSBuild script looks like this:

XML
<?xml version="1.0"?>
<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003"
         DefaultTargets="Migrate" ToolsVersion="4.0">

    <PropertyGroup>
        <MigratorTasksDirectory>
            $(MSBuildProjectDirectory)\packages\FluentMigrator.Tools.1.0.2.0\tools\AnyCPU\40\
        </MigratorTasksDirectory>
        <ConnectionString>
            Data Source=.\SQLEXPRESS;Initial Catalog=FluentMigration;Integrated Security=True;
        </ConnectionString>
    </PropertyGroup>

    <UsingTask
            TaskName="FluentMigrator.MSBuild.Migrate"
            AssemblyFile="$(MigratorTasksDirectory)FluentMigrator.MSBuild.dll" />

    <Target Name="Migrate">
        <Message Text="Starting FluentMigrator migration" />

        <!-- Important: Target must be your Migrations assembly name, not your dll file name -->
        <Migrate Database="SqlServer2008"
                 Connection="$(ConnectionString)"
                 Target="Migration" />
    </Target>
</Project>

Cool, we now have everything we need to start, right? Perhaps, but I see some potential problems here.

Problem #1

Since we are using NuGet (I choose to install both FluentMigrator and FluentMigrator.Tools using NuGet), we're kind of hardcoding to the current version of FluentMigrator (1.0.2.0 in the above script). When we update the FluentMigrator to the newer version, we also must update the MSBuild file to point to the right folder.

I don't like that, so I chose instead to go with old-school approach and have FluentMigrator.Tools in a separate subfolder under root\tools. Let's now modify our script to point to the right folder:

XML
<MigratorTasksDirectory>
    $(MSBuildProjectDirectory)\..\tools\FluentMigrator\
</MigratorTasksDirectory>

Problem #2

Connection string is hardcoded. Whenever we change application connection string, we need to remember to change it in MSBuild script also. It would be great if we could just read our current application connection string from web.config and use that. Well, we can. There's an <XmlPeek> task in MSBuild 4. An alternative is <XmlQuery> task of MSBuild Community Tasks library.

Let's add that, and also, let's also reorganize the script a bit:

XML
<?xml version="1.0"?>
<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003"
         DefaultTargets="Migrate" ToolsVersion="4.0">

  <PropertyGroup>
    <MigratorTasksDirectory>
      $(MSBuildProjectDirectory)\packages\FluentMigrator.Tools.1.0.2.0\tools\AnyCPU\40\
    </MigratorTasksDirectory>

    <MainProjectDirectory>
      $(MSBuildProjectDirectory)\..\Migration.Web\
    </MainProjectDirectory>
  </PropertyGroup>

  <UsingTask
      TaskName="FluentMigrator.MSBuild.Migrate"
      AssemblyFile="$(MigratorTasksDirectory)FluentMigrator.MSBuild.dll" />

  <Target Name="Common">
    <XmlPeek XmlInputPath="$(MainProjectDirectory)\web.config"
             Query="/configuration/connectionStrings/add[@name='Database']/@connectionString"
             Condition="'$(ConnectionString)' == ''">
      <Output TaskParameter="Result" PropertyName="ConnectionString" />
    </XmlPeek>
  </Target>

  <Target Name="Migrate" DependsOnTargets="Common">
    <Message Text="Starting FluentMigrator migration" />

    <!-- Important: Target must be your Migrations assembly name, not your dll file name -->
    <Migrate Database="SqlServer2008"
             Connection="$(ConnectionString)"
             Target="MyMigration" />
  </Target>
</Project>

Since there probably will be some more common things shared between our targets (Migrate, Backup, Restore), a Common target is added. ConnectionString property is removed from PropertyGroup and read from web.config in Common target. Note that the Migrate target now depends on Common target.

OK, that looks better, but it can still be improved.

Problem #3

Team management of connection strings. What if one team member needs to use different database name? And what if some team members are using SQL Server Express Edition and other team members SQL Server Standard or Developer edition. Note that the build server could also be considered as a team member. For all those cases, connection strings would differ, so whenever the working copy is updated, team member would need to check the connection string and modify it if necessary.

In order to avoid those problems, my team had used separate ConnectionStrings.config files that were outside of the source control, so each team member had his own version of ConnectionString.config. That file would then be referenced from web.config or app.config like this:

XML
<connectionStrings configSource="ConnectionStrings.config" />

This worked pretty well for us, but recently I came across another solution for this. Ayende's concept is simple. You have a default connection string and machine specific overrides:

XML
<add name="Database" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=FluentMigration;Integrated Security=True;"/>
<add name="Database-MiroslavI5" connectionString="Data Source=.;Initial Catalog=FluentMigration;Integrated Security=True;"/>
<add name="Database-MiroslavLaptop" connectionString="Data Source=.;Initial Catalog=FluentMigration;Integrated Security=True;"/>

The application would first look for machine specific connection string and fall back to default if machine specific key was not found. This is OK for a small teams with several different machines. For larger teams, these machine specific connection strings would soon add up. In that case, I would suggest you to use a separated ConnectionStrings.config file, since it would be easier to maintain.

Since we are using that machine specific trick in our web.config, MSBuild script would also need to be smart enough to figure out the machine specific or default connection string:

XML
<!-- Locate machine specific connection string first -->
<XmlPeek XmlInputPath="$(MainProjectDirectory)\web.config"
         Query="/configuration/connectionStrings/add[@name='Database-$(ComputerName)']/@connectionString"
         Condition="'$(ConnectionString)' == ''">
  <Output TaskParameter="Result" PropertyName="ConnectionString" />
</XmlPeek>
<!-- If machine specific connection string doesn't exist, fallback to default connection string -->
<XmlPeek XmlInputPath="$(MainProjectDirectory)\web.config"
         Query="/configuration/connectionStrings/add[@name='Database']/@connectionString"
         Condition="'$(ConnectionString)' == ''">
  <Output TaskParameter="Result" PropertyName="ConnectionString" />
</XmlPeek>

As you can see, we first try machine specific version, using $(ComputerName) MSBuild property, and if it's not found, then we use the default. Condition="'$(ConnectionString)' == ''" is used to check whether the connection string is already found or not. The result is saved to ConnectionString property.

This is it. Now our MSBuild script looks more robust. But if we try to run it now, we'll get an error, since Migrate task cannot find our migration project dll.

Problem #4

So, we would need to copy migration project dll to the FluentMigrator tools folder. There are several ways we could do that...

  • We could just use MSBuild's <Copy> task to copy the dll as the FluentMigrator documentation suggests, but in that case, we would also need to hardcode it's source location. Source could be either \bin\debug or \bin\release, depending on current build configuration.
  • How about using project's post build events to copy output to some root\build folder, and then we could have <Copy> task in our MSBuild script? Yes, that would work.
  • Instead, I just used Migration project's post build events to copy migration project dll to FluentMigrator tools folder, like this:
copy "$(TargetDir)Migration.dll" "$(SolutionDir)..\tools\FluentMigrator\"

After that, our MSBuild script runs and executes migration code on the database (providing you have at least an empty database named the same as in your connection string).

msbuild.exe /target:Migrate Migration.msbuild

Feature #2 - Database backup

Now that we have our migration process ready and running, let's move on to database backup functionality.

Problem #5

There are no SQL running tasks in MSBuild. Fortunately, there are many open source projects with additional tasks. We're going to use MSBuild Community Tasks.

XML
<!-- This goes to our PropertyGroup section -->
<!-- Fixing some problems with referencing community tasks -->
<MSBuildCommunityTasksPath>$(MSBuildProjectDirectory)\..\tools\MSBuild Community Tasks\Build\</MSBuildCommunityTasksPath>
<MSBuildCommunityTasksLib>$(MSBuildCommunityTasksPath)MSBuild.Community.Tasks.dll</MSBuildCommunityTasksLib>
<BackupFileDirectory>$(MSBuildProjectDirectory)\..\backup\</BackupFileDirectory>
<!-- /PropertyGrop -->

<!-- Backup target -->
<Target Name="Backup" DependsOnTargets="Common">
    <Message Text="Backing up the database to temp folder" />

    <MSBuild.Community.Tasks.SqlExecute
            ConnectionString="$(ConnectionString)"
            Command="BACKUP DATABASE [Migration] TO DISK = N'$(BackupFileDirectory)\Migration.bak' WITH NOFORMAT, INIT, NAME = N'Migration - Full Backup - $(BuildDate)', SKIP, NOREWIND, NOUNLOAD, STATS = 10" />
</Target>

In the code above, we have some property fixes for MSBuild Community Tasks (since it's in our \tools folder and not on default location) and also a new property BackupFileDirectory in our PropertyGroup. But the database name is hardcoded within the script. And I would like to have a unique backup file name based on the current time.

Problem #6

To pull out the database name from connection string, we would need to have some kind of regex. Again, MSBuild doesn't have regex support by default, but it can be achieved using property functions, a new feature of MSBuild 4.0.

XML
<!-- In Common target -->
<!-- Strip database name from connection string -->
<CreateProperty Value="$([System.Text.RegularExpressions.Regex]::Match($(ConnectionString), `Initial Catalog=([^;])*`))">
    <Output TaskParameter="Value" PropertyName="DatabaseName" />
</CreateProperty>
<CreateProperty Value="$(DatabaseName.Replace('Initial Catalog=', ''))">
    <Output TaskParameter="Value" PropertyName="DatabaseName" />
</CreateProperty>

This new part creates a property DatabaseName by pulling it from the ConnectionString property. I couldn't get the plain database name with regex function, so there's a second <CreateProperty> task that just removes Initial Catalog= string from the DatabaseName.

Great, now let's get the timestamp and add it to the mix.

XML
<!-- Prepare backup file name and path from current time -->
<MSBuild.Community.Tasks.Time Format="yyyy-MM-dd-HH-mm-ss">
    <Output TaskParameter="FormattedTime" PropertyName="BuildDate" />
</MSBuild.Community.Tasks.Time>
<CreateProperty Value="$(BackupFileDirectory)$(DatabaseName)-$(BuildDate).bak">
    <Output TaskParameter="Value" PropertyName="BackupFilePath" />
</CreateProperty>

Again, we are using a task from MSBuild Community Tasks called <Time> and put the formatted time to a BuildDate property. BackupFilePath is a new property that contains the full path of the new backup file.

Our <SqlExecute> task now looks like this:

XML
<MSBuild.Community.Tasks.SqlExecute
        ConnectionString="$(ConnectionString)"
        Command="BACKUP DATABASE [$(DatabaseName)] TO DISK = N'$(BackupFilePath)' WITH NOFORMAT, INIT, NAME = N'$(DatabaseName) - Full Backup - $(BuildDate)', SKIP, NOREWIND, NOUNLOAD, STATS = 10" />

Now we can try to do backup:

msbuild.exe /target:Backup Migration.msbuild

And... it fails.

Problem #7

We are trying to backup the same database we are connected to. SQL Server refuses to do that. We should connect using master table. For that, we would need to modify connection string. Here's some more regex magic:

XML
<CreateProperty Value="$([System.Text.RegularExpressions.Regex]::Replace($(ConnectionString), `Initial Catalog=([^;])*`, `Initial Catalog=master`))">
    <Output TaskParameter="Value" PropertyName="MasterConnectionString" />
</CreateProperty>

Now we use the new $(MasterConnectionString) property for <SqlExecute> task:

XML
<MSBuild.Community.Tasks.SqlExecute
        ConnectionString="$(MasterConnectionString)"
        Command="BACKUP DATABASE [$(DatabaseName)] TO DISK = N'$(BackupFilePath)' WITH NOFORMAT, INIT, NAME = N'$(DatabaseName) - Full Backup - $(BuildDate)', SKIP, NOREWIND, NOUNLOAD, STATS = 10" />

If you run it now, it might work... but then again, it might also fail Smile | :)

Problem #8

Depending on where you keep your project files, the backup might fail or not. Remember that we put our BackupFileDirectory under the project's root subfolder \backup? If you keep your project under your user folder (i.e. C:\Users\Miroslav), then SQL Server doesn't have a permission to create a file there. SQL Server service usually doesn't run under the current user account, and hence cannot access user's folder.

OK, so we can't backup directly to user folder, but we can create a backup somewhere else and then simply copy to our backup destination folder. Let's do that now:

XML
<BackupFileDirectory>$(ALLUSERSPROFILE)\CodeProject\Migration\</BackupFileDirectory>
<BackupOutputDirectory>$(MSBuildProjectDirectory)\..\backup\</BackupOutputDirectory>

The above code goes to PropertyGroup. We replace our old BackupFileDirectory property with a new value. This time the backup will be done to All Users profile folder (that's C:\ProgramData on Windows 7). And there's another property - BackupOutputDirectory containing our old BackupFileDirectory value - our real backup folder. Now we can do backup using those properties:

XML
<!-- Create database backup to temp file out of current user's profile folder -->
<MSBuild.Community.Tasks.SqlExecute
  ConnectionString="$(MasterConnectionString)"
  Command="BACKUP DATABASE [$(DatabaseName)] TO DISK = N'$(BackupFilePath)' WITH NOFORMAT, INIT, NAME = N'$(DatabaseName) - Full Backup - $(BuildDate)', SKIP, NOREWIND, NOUNLOAD, STATS = 10" />
<Message Text="Database backup created" />

<Copy SourceFiles="$(BackupFilePath)" DestinationFolder="$(BackupOutputDirectory)" />
<!-- Delete temporary backup file -->
<Delete Files="$(BackupFilePath)" />

<SqlExecute> task remains unchanged. We just copy the backup file from the temp all users folder to our destination backup folder, and delete the temp one at the end.

To make sure that our backup folders exist, add this to the Common task:

XML
<MakeDir Directories="$(BackupFileDirectory)" />
<MakeDir Directories="$(BackupOutputDirectory)" />

We have a pretty robust backup procedure now. One more thing that could be done. Backup files are usually very big, and usually are very "compress-able".

Problem #9

Now that we have everything else prepared, zipping the backup shouldn't be an issue. We'll use MSBuild Community Tasks and it's <Zip> task. And since we need to name our zip file, let's create another property in Common task:

XML
<CreateProperty Value="$(BackupOutputDirectory)$(DatabaseName)-$(BuildDate).zip">
  <Output TaskParameter="Value" PropertyName="BackupOutputPath" />
</CreateProperty>

Now we can use BackupOutputPath property in <Zip> task. Just replace the <Copy> task we had in the previous step with the <Zip> task.

XML
<MSBuild.Community.Tasks.Zip
    Files="$(BackupFilePath)"
    WorkingDirectory="$(BackupFileDirectory)"
    ZipFileName="$(BackupOutputPath)"
    ZipLevel="9" />

That's it. We now have everything that's required for backup. You can try it:

msbuild.exe /target:Backup Migration.msbuild

How about doing backup before each database upgrade / migration? Make your Migrate target depend on Backup target:

<Target Name="Migrate" DependsOnTargets="Backup">

Great. Two features completed. One more to go.

Feature #3 - Database restore

This isn't something you'll do quite often, but when you need it, an automated restore may come in handy. We already have all the necessary ingredients for doing restore, so let's just write the target down:

XML
<Target Name="Restore" DependsOnTargets="Common">
    <Message Text="Restoring the database from /backup folder" />

    <!-- Unzip the given backup file -->
    <Message Text="Unzipping the backup file $(RestoreFileName).zip" />
    <MSBuild.Community.Tasks.Unzip
        ZipFileName="$(BackupOutputDirectory)$(RestoreFileName).zip"
        TargetDirectory="$(BackupFileDirectory)" />

    <CreateProperty Value="$(BackupFileDirectory)$(RestoreFileName).bak">
      <Output TaskParameter="Value" PropertyName="BackupFilePath" />
    </CreateProperty>

    <MSBuild.Community.Tasks.SqlExecute
        ConnectionString='$(MasterConnectionString)'
        Command="DECLARE @SQL varchar(max); SET @SQL = ''; SELECT @SQL = @SQL + 'Kill ' + Convert(varchar, SPId) + ';' FROM MASTER..SysProcesses WHERE DBId = DB_ID('$(DatabaseName)') AND SPId &lt;&gt; @@SPId; EXEC(@SQL); RESTORE DATABASE [$(DatabaseName)] FROM  DISK = N'$(BackupFilePath)' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10;"
        />

    <!-- Delete temporary backup file -->
    <Delete Files="$(BackupFilePath)" />
    <Message Text="Database restored successfully from $(RestoreFileName).zip" />
</Target>

To do restore, we're just reversing the backup steps. Notice how we have a RestoreFileName property that's not defined in our MSBuild file? That property will need to be defined when calling MSBuild, like this:

msbuild.exe /property:Configuration=Release;RestoreFileName="CleanDatabase" Migration.msbuild /target:Restore

Note that only files within \backup folder are supported. That's enough for our simple case.

Unzipping is done to All Users profile folder again - to allow SQL Server to pick the backup, even if the zip file itself was under user profile folder.

Database restore is also using <SqlExecute> task. This time, SQL command is a little bigger, since it contains the code for killing all the connections to the particular database before trying to do restore operation.

After restoring the database, we remove the temp backup file from the All Users profile folder. Now we can run it:

msbuild.exe /property:Configuration=Release;RestoreFileName="<ExistingBackupName>" Migration.msbuild /target:Restore

Tiding things up

Downloaded code also contains three batch files dbupgrade, dbbackup and dbrestore, for easier manual running of these targets. It will save us some typing (no need to call msbuild.exe with additional parameters). Only dbrestore has an additional parameter - the name of backup file to restore.

Phew, that was longer than expected, but we now have everything we need to maintain our database. Beside the benefit of having an easy way of running Upgrade, Backup and Restore manually, the scripts can be used in continuous integration environment. Build server could run them within build tasks and prepare a testing and/or staging database.

Points of Interest

It was somewhat fun, somewhat frustrating to experience and solve / workaround all those problems, but the final result looks very usable. This combination is already used in two projects I'm working on. We didn't loose anything in moving from our custom dbtool to FluentMigrator and MSBuild, and we got the simpler migration code (C# code instead of SQL scripts). This won't work for everybody or for projects of all sizes, but for a small projects with smaller databases it would be a good choice.

Once more, the source is available on GitHub, and through the download link on top of this article.

FluentMigrator:

MSBuild:

History

Initial version

  • 2012-06-12: Original article

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect Abacus Solutions and Consulting, Inc.
Bosnia and Herzegovina Bosnia and Herzegovina
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
Questionbad idea Pin
df49423-Jun-17 10:42
df49423-Jun-17 10:42 
QuestionGreat!! Pin
Gerard Castelló Viader29-Jan-14 23:41
Gerard Castelló Viader29-Jan-14 23:41 
QuestionPlease explain about the following sentence of article: Pin
nasrinMirzaei1-Sep-13 2:29
nasrinMirzaei1-Sep-13 2:29 
QuestionGreat Post Pin
Chris Raastad27-Sep-12 21:37
Chris Raastad27-Sep-12 21:37 
QuestionNice post Pin
RadenkoZec13-Jun-12 6:32
RadenkoZec13-Jun-12 6:32 
AnswerRe: Nice post Pin
Miroslav Popovic13-Jun-12 6:45
Miroslav Popovic13-Jun-12 6:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.