Packaging and Deploying SQL Databases

INFO

This documentation is for FAKE.exe before version 5 (or the non-netcore version). The documentation for FAKE 5 can be found here

FAKE can be used to create a SQL DACPAC and also deploy it to a SQL Server using the MSDeploy executable. This is installed by default with Visual Studio and with the SQL Server Data Tools (SSDT) package.

DACPACs automatically diff from the source to the destination and generate the SQL script dynamically.

You can read up more on DACPac and MSDeploy arguments here.

Sample usage

Ensure that you have already built your database project (you can do this with standard MSBuild). Then, use the deployDb command to deploy the dbProject.dacpac to the myDatabase.

1: 
2: 
3: 
4: 
5: 
6: 
7: 
open Fake.Sql.DacPac

/// the database for local development + compile
Target "DeployLocalDb" (fun _ ->
    let connectionString = "Data Source=(localdb)\MSSQLLocalDB;Integrated Security=True;Database=MyDatabase;Pooling=False"
    let dacPacPath = "path/to/dbProject.dacpac"
    deployDb (fun args -> { args with Source = dacPacPath; Destination = localDbConnectionString }) |> ignore)

Deployment Options

You can optionally specify the type of command to use (again, refer to the documentation above for more detail): -

  • Deploy - full deploy to destination
  • Script - SQL script
  • Report - XML report of diff

In addition, you can also elect to deploy to Dacpac files rather than SQL databases - simply pass in the pass to the dacpac that you wish to generate.

val ignore : value:'T -> unit