Table of Contents

Publishing

You can deploy a database using the SDK's built-in publisher, SqlPackage locally, or SqlPackage in a container. The SDK also supports generating a script that creates the database from scratch.

Workflow Commands Publish profile support
SDK built-in publisher dotnet publish /t:PublishDatabase No; use project properties or command-line properties
SqlPackage locally dotnet build, then sqlpackage /Action:Publish /SourceFile:… /Profile:… Yes; SqlPackage reads the profile
SqlPackage in a container dotnet publish /t:PublishContainer, then docker run … /Profile:… Yes; mount the profile into the container for SqlPackage to read
Create script Set GenerateCreateScript=true, then run dotnet build No; generates a create script using project settings

PublishDatabase deploys immediately. PublishContainer builds an image; running that image performs the deployment. See Publishing with a profile for profile creation and examples of both SqlPackage workflows.

Note

Starting with version 4.0.0, the SDK supports publishing a container image. If you previously used dotnet publish to deploy directly to SQL Server, use dotnet publish /t:PublishDatabase to retain that behavior. This built-in publisher uses project and command-line properties rather than publish profiles.

We generally recommend using the container image approach for most scenarios, as it provides a consistent deployment experience across different environments. However, for local development and quick deployments the direct publishing approach might be more convenient.

Publishing directly to SQL Server

There is support for publishing a project to a SQL Server using the dotnet publish /t:PublishDatabase command. This support is designed to be used by developers to deploy or update their local development database quickly. For more advanced deployment scenarios we suggest using SqlPackage instead as it provides more options, or check out the container image approach described below.

There are a couple of properties that control the deployment process which have some defaults to make the experience as smooth as possible for local development. For example, on Windows if you have a default SQL Server instance running on your local machine running dotnet publish /t:PublishDatabase creates a database with the same name as the project. Unfortunately on Mac and Linux we cannot use Windows authentication, so you'll need to specify a username and password:

dotnet publish \
  /t:PublishDatabase \
  /p:TargetUser=<username> \
  /p:TargetPassword=<password>

To further customize the deployment process, you can use the following properties which can either be set in the project file or specified on the command line (using the /p:<property>=<value> syntax shown above).

Property Default Value Description
TargetServerName (local) Controls the name of the server to which the project is published. Defaults to '(localdb)\mssqllocaldb' when using Visual Studio.
TargetDatabaseName Project name Controls the name of the database published by dotnet publish
TargetPort Specifies an alternate port for connecting to the target server (only necessary if using a non-standard port)
TargetUser Username used to connect to the server. If empty, Windows authentication is used
TargetPassword Password used to connect to the server. If empty, but TargetUser is set you will be prompted for the password
IncludeCompositeObjects True Controls whether objects from referenced packages are deployed to the same database
RunScriptsFromReferences False Runs pre-deployment and post-deployment scripts from referenced packages and projects during direct database publishing. See deployment scripts from references.
TargetName Project name Controls the name of the .dacpac created by dotnet build. The default name for the .dacpac file is the name of the project file, e.g. MyProject.csproj produces MyProject.dacpac.
DeployOnPublish True Controls whether a deploy occurs when the project is published.
Important

Although you can set the username and password in your project file we don't recommend doing so since you'll be committing credentials to version control. Instead, you should specify these at the command line when needed.

In addition to these properties, you can also set any of the documented deployment options. These are typically set in the project file, for example:

  <PropertyGroup>
    ...
    <BackupDatabaseBeforeChanges>True</BackupDatabaseBeforeChanges>
    <BlockOnPossibleDataLoss>True</BlockOnPossibleDataLoss>
    ...
  </PropertyGroup>

Most of those properties are simple values (like booleans, strings and integers), but there are a couple of properties that require more complex values:

Property Example value Description
DatabaseSpecification Hyperscale,1024,P15 This property is specified in the format Edition,Maximum Size,Service Objective
DoNotDropObjectTypes Aggregates,Assemblies A comma separated list of Object Types that should not be dropped as part of the deployment
ExcludeObjectTypes Contracts,Endpoints A comma separated list of Object Types that should not be part of the deployment
SqlCommandVariableValues These should not be set as a Property, but instead as an ItemGroup as described in this section

Publishing as a container image

From version 4.0.0 of MSBuild.Sdk.SqlProj we now support publishing your database project as a runnable container image. The image will contain both SqlPackage and the .dacpac file. This allows you to run the image anywhere a container can be executed, making it ideal for CI/CD pipelines and other automated deployment scenarios.

Note

By default the published container will contain the latest version of SqlPackage available at the time of publishing. If you want to pin the specific version of SqlPackage used in the container you can set the SqlPackageDownloadUrl property in your project file to point to the specific version you want, ie: https://go.microsoft.com/fwlink/?linkid=2338525 for version 170.2.70. You can find the appropriate download links on the SqlPackage release notes page. Please make sure to use the link for the Linux .NET 8 version of SqlPackage.

To publish your project as a container image, use the following command:

dotnet publish /t:PublishContainer

By default, the image will be tagged with the name of your project. You can customize the image name and tag by setting the ContainerRepository and ContainerImageTag properties on the command line. For example:

dotnet publish \
  /t:PublishContainer \
  /p:ContainerRepository=my-database-image \
  /p:ContainerImageTag=v1.0.0

You can also set these properties in your project file if you prefer:

  <PropertyGroup>
    ...
    <ContainerRepository>my-database-image</ContainerRepository>
    <ContainerImageTag>v1.0.0</ContainerImageTag>
    ...
  </PropertyGroup>

Once the container image is published, you can run it using the following command:

docker run --rm \
  my-database-image:v1.0.0 \
  /TargetConnectionString=<your-connection-string>

This will execute SqlPackage inside the container, deploying the .dacpac to the specified SQL Server instance. You can pass any additional SqlPackage parameters as needed. However by default the container is configured to pass /Action:Publish and /SourceFile:<your-dacpac-file>.dacpac. For a full list of available parameters, refer to the SqlPackage documentation.

Important

Since SqlPackage currently only supports an x64 architecture the container image being published is also x64. Additionally we only support Linux-based containers at this time.

Script generation

Instead of using dotnet publish /t:PublishDatabase to deploy changes to a database, you can also have a full SQL script generated that will create the database from scratch and then run that script against a SQL Server. This can be achieved by adding the following to the project file:

  <PropertyGroup>
    <GenerateCreateScript>True</GenerateCreateScript>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
  </PropertyGroup>

With this enabled you'll find a SQL script with the name <database-name>_Create.sql in the bin folder. When the project is referenced by another project, the generated script is also copied to the referencing project's output directory alongside the .dacpac. The database name for the create script gets resolved in the following manner:

  1. TargetDatabaseName.
  2. Package name.
Note
  • the generated script also uses the resolved database name via a setvar command.
  • if IncludeCompositeObjects is true, the composite objects (tables, etc.) from external references are also included in the generated script. This property defaults to true

Publishing with a profile

Use the publishprofile item template to create a reusable .publish.xml file for SqlPackage. After installing the templates with dotnet new install MSBuild.Sdk.SqlProj.Templates, run this command in your project directory:

dotnet new publishprofile -n Development

This creates Development.publish.xml without building or deploying the project. Edit its TargetDatabaseName and TargetConnectionString to match your database, and set DeployScriptFileName to the desired deployment script filename. The starter profile follows the classic Visual Studio publish-profile structure. It uses integrated authentication, enables encryption and certificate validation, blocks possible data loss, and disables dropping objects absent from the source. Choose a connection string and authentication method appropriate for your server; keep passwords out of committed profiles.

Using SqlPackage locally

With SqlPackage installed, build the project and publish its .dacpac using the profile:

dotnet build
sqlpackage \
  /Action:Publish \
  /SourceFile:"bin/Debug/net10.0/MyDatabase.dacpac" \
  /Profile:"Development.publish.xml"

Replace the source path with your build output.

Using the container image

Build the image as described in Publishing as a container image:

dotnet publish \
  /t:PublishContainer \
  /p:ContainerRepository=my-database-image \
  /p:ContainerImageTag=v1.0.0

Mount the profile into the container as a read-only file and pass its container path to SqlPackage. Run this Bash example from the directory containing Development.publish.xml:

docker run --rm \
  --mount "type=bind,source=$(pwd)/Development.publish.xml,target=/profiles/Development.publish.xml,readonly" \
  my-database-image:v1.0.0 \
  /Profile:/profiles/Development.publish.xml

The image already supplies /Action:Publish and the source .dacpac path. Set the profile's connection string to a server address reachable from the container and use an authentication method suitable for the Linux container. Inside the container, localhost refers to the container itself.

For either workflow, you can create separate profiles with other names, such as dotnet new publishprofile -n Production, and select them with /Profile. Override individual settings by appending SqlPackage arguments, for example /TargetDatabaseName:MyDatabase_Test. Mounting the profile at runtime lets you reuse the same image across environments.

These profiles are consumed by SqlPackage; the template does not add profile support to dotnet publish /t:PublishDatabase. See Microsoft's publish profile documentation for additional deployment options and SQLCMD variables.

Edit this page