vsdbcmd deployment to SQL Azure

Unless you're targeting a data-tier application, you can’t generate a create script from a .dbproj (datadude) database project in Visual Studio that’s compatible with SQL Azure. This is a big pain if you’re attempting to implement a continuous deployment/continuous delivery approach.

Following is a snippet of Powershell code (a component of a psake build script) that deploys the dbschema to a local SQL instance, then uses SMO to script out the objects & data in an SQL Azure compatible format. This doesn’t solve the problem of generating schema update statements for an existing database, it’s a big time-saver while your Azure solution is in development.

Write-Host "Generate SQL Create script" -ForegroundColor Green

mkdir "$build\_artifacts\_dir\\Database"

# Do a Prod deployment locally
Exec { vsdbcmd /manifest:"$build\_artifacts\_dir\Release\$dbproj.deploymanifest" /a:Deploy /dd /cs:"Data Source=(LOCAL);Integrated Security=true" /p:AlwaysCreateNewDatabase=true /p:TargetDatabase=$dbname }

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMOExtended") | out-null

$server = new-object ('Microsoft.SqlServer.Management.Smo.Server') '(LOCAL)'
$db = $server.Databases[$dbname]

$transfer = new-object ('Microsoft.SqlServer.Management.Smo.Transfer') ($db) 
$transfer.CopyAllObjects = $True 
$transfer.CreateTargetDatabase = $False 
$transfer.Options.AppendToFile = $True 
$transfer.Options.ClusteredIndexes = $True 
$transfer.Options.DriAll = $True 
$transfer.Options.ScriptDrops = $True 
$transfer.Options.IncludeHeaders = $False 
$transfer.Options.ToFileOnly = $True 
$transfer.Options.Indexes = $True 
$transfer.Options.WithDependencies = $True 
$transfer.Options.TargetDatabaseEngineType = "SqlAzureDatabase"  
$transfer.Options.TargetServerVersion = "Version105"  
$transfer.Options.FileName = "$build\_artifacts\_dir\\Database\\Create.sql"

#Generate Drops 
$transfer.ScriptTransfer() 
#Generate Creates 
$transfer.Options.ScriptDrops = $False 
$transfer.ScriptTransfer() 
$scripter = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($server) 
$scripter.Options.AppendToFile = $True 
$scripter.Options.ScriptData = $True 
$scripter.Options.ScriptSchema = $False 
$scripter.Options.NoCommandTerminator = $True 
$scripter.Options.ToFileOnly = $True 
$scripter.Options.TargetDatabaseEngineType = "SqlAzureDatabase" 
$scripter.Options.TargetServerVersion = "Version105" 
$scripter.Options.WithDependencies = $True 
$scripter.Options.FileName = "$build\_artifacts\_dir\Database\Create.sql"

#Generate Inserts 
$scripter.EnumScript([Microsoft.SqlServer.Management.Smo.SqlSmoObject[]]$db.Tables)

From there you can use sqlcmd to execute the script and drop & recreate your Azure database objects and populate them with data.