DbUp is a .NET library that helps you to deploy changes to SQL Server databases. It tracks which SQL scripts have been run already, and runs the change scripts that are needed to get your database up to date.

	Stable	Prerelease
Documentation
DbUp
DbUp-MySql
DbUp-SQLite
DbUp-SQLite-Mono
DbUp-SqlCe
DbUp-PostgreSQL
DbUp-Firebird

For use in Octopus Deploy we created an additional, specialized file system provider to improve support for our build scripts folder structure. DbUp is a very useful project to us, so i hope i can help providing some extra value here. Feel free to cherry pick. Al of these customizations (in master) were submitted to upstream as a pull request (DbUp#148).

The new provider is named VersionedFolderScriptProvider and adds the following features:

Example folder structure:

my-script-root-folder\
   1.0\
        1_apple.sql
        2_banana.sql
   1.0.1\
        1_apple.sql
        2_pear.sql
   2.0\
        apple.sql
        banana.sql

The found scripts are flattened to one list by combining the relative folder name with the script name. In this example, the resulting ordered set of scripts of will be:

1.0\1_apple.sql
1.0\2_banana.sql
1.0.1\1_apple.sql
1.0.1\2_pear.sql
2.0\apple.sql
2.0\banana.sql

Use semantic versioning to exclude scripts from folders from versions newer than the target version. This makes it possible to use the same scriptbase for patch and release deploys. To make this possible, the foldersnames must be parseable to a version (i.e. contains 1 - 4 delimited decimals),

In the example above, a target version of '1.0.1' will result in the following ordered set of scripts:

1.0\1_apple.sql
1.0\2_banana.sql
1.0.1\1_apple.sql
1.0.1\2_pear.sql

1. To the folder name
2. To the effective scriptname

$databaseName = $args[0]
$databaseServer = $args[1]
$scriptRootFolder = $args[2]
$targetVersion = $args[3]

Add-Type -Path "C:\Development resources\Code\Github\DbUp\src\DbUp\bin\Debug\DbUp.dll"

function FilterFunc ($a,$b) {
  return `
	-not $a.ToLower().Contains("obsolete") -and `
	-not $a.ToLower().StartsWith("branches") -and `
	-not $a.ToLower().StartsWith("customerspecific") -and `
	-not $a.ToLower().StartsWith("warehouse");
}

$dbUp = [DbUp.DeployChanges]::To
$dbUp = [SqlServerExtensions]::SqlDatabase($dbUp, "server=$databaseServer;database=$databaseName;Trusted_Connection=Yes;Connection Timeout=120;")
$dbUp = [StandardExtensions]::WithScriptsFromVersionFolders($dbUp, $scriptRootFolder, ${function:FilterFunc}, $targetVersion)
$dbUp = [SqlServerExtensions]::JournalToSqlTable($dbUp, 'dbo', 'SchemaVersions')
$dbUp = [StandardExtensions]::LogToConsole($dbUp)
$upgradeResult = $dbUp.Build().PerformUpgrade()