Publish-DbaDacPackage

Want to see the source code for this command? Check out Publish-DbaDacPackage on GitHub.
Want to see the Bill Of Health for this command? Check out Publish-DbaDacPackage.

Synopsis

The Publish-DbaDacPackage command takes a dacpac or bacpac and publishes it to a database.

Description

Publishes the dacpac taken from SSDT project or Export-DbaDacPackage. Changing the schema to match the dacpac and also to run any scripts in the dacpac (pre/post deploy scripts) or bacpac.

Syntax

Publish-DbaDacPackage
    [-SqlInstance <DbaInstanceParameter[]>]
    [-SqlCredential <PSCredential>]
    -Path <String>
    -Database <String[]>
    [-ConnectionString <String[]>]
    [-GenerateDeploymentReport]
    [-ScriptOnly]
    [-Type <String>]
    [-OutputPath <String>]
    [-IncludeSqlCmdVars]
    [-DacOption <Object>]
    [-EnableException]
    [-DacFxPath <String>]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Publish-DbaDacPackage
    [-SqlInstance <DbaInstanceParameter[]>]
    [-SqlCredential <PSCredential>]
    -Path <String>
    [-PublishXml <String>]
    -Database <String[]>
    [-ConnectionString <String[]>]
    [-GenerateDeploymentReport]
    [-ScriptOnly]
    [-Type <String>]
    [-OutputPath <String>]
    [-IncludeSqlCmdVars]
    [-EnableException]
    [-DacFxPath <String>]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> $options = New-DbaDacOption -Type Dacpac -Action Publish
PS C:\> $options.DeployOptions.DropObjectsNotInSource = $true
PS C:\> Publish-DbaDacPackage -SqlInstance sql2016 -Database DB1 -DacOption $options -Path c:\temp\db.dacpac

Uses DacOption object to set Deployment Options and updates DB1 database on sql2016 from the db.dacpac dacpac file, dropping objects that are missing from source.

Example: 2

PS C:\> Publish-DbaDacPackage -SqlInstance sql2017 -Database WideWorldImporters -Path C:\temp\sql2016-WideWorldImporters.dacpac -PublishXml C:\temp\sql2016-WideWorldImporters-publish.xml -Confirm

Updates WideWorldImporters on sql2017 from the sql2016-WideWorldImporters.dacpac using the sql2016-WideWorldImporters-publish.xml publish profile. Prompts for confirmation.

Example: 3

PS C:\> New-DbaDacProfile -SqlInstance sql2016 -Database db2 -Path C:\temp
PS C:\> Export-DbaDacPackage -SqlInstance sql2016 -Database db2 | Publish-DbaDacPackage -PublishXml C:\temp\sql2016-db2-publish.xml -Database db1, db2 -SqlInstance sql2017

Creates a publish profile at C:\temp\sql2016-db2-publish.xml, exports the .dacpac to $home\Documents\sql2016-db2.dacpac. Does not prompt for confirmation.
then publishes it to the sql2017 server database db2

Example: 4

PS C:\> $loc = "C:\Users\bob\source\repos\Microsoft.Data.Tools.Msbuild\lib\net46\Microsoft.SqlServer.Dac.dll"
PS C:\> Publish-DbaDacPackage -SqlInstance "local" -Database WideWorldImporters -Path C:\temp\WideWorldImporters.dacpac -PublishXml C:\temp\WideWorldImporters.publish.xml -DacFxPath $loc -Confirm

Publishes the dacpac using a specific dacfx library. Prompts for confirmation.

Example: 5

PS C:\> Publish-DbaDacPackage -SqlInstance sql2017 -Database WideWorldImporters -Path C:\temp\sql2016-WideWorldImporters.dacpac -PublishXml C:\temp\sql2016-WideWorldImporters-publish.xml -ScriptOnly

Does not deploy the changes, but will generate the deployment script that would be executed against WideWorldImporters.

Required Parameters

-Path

Specifies the filesystem path to the DACPAC

-Database

Specifies the name of the database being published.

Optional Parameters

-SqlInstance

The target SQL Server instance or instances.

-SqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Only SQL authentication is supported. When not specified, uses Trusted Authentication.

-PublishXml

Specifies the publish profile which will include options and sqlCmdVariables.

-ConnectionString

Specifies the connection string to the database you are upgrading. This is not required if SqlInstance is specified.

-GenerateDeploymentReport

If this switch is enabled, the publish XML report will be generated.

-ScriptOnly

If this switch is enabled the publish script will be generated.

-Type

Selecting the type of the export: Dacpac (default) or Bacpac.

-OutputPath

Specifies the filesystem path (directory) where output files will be generated.

-IncludeSqlCmdVars

If this switch is enabled, SqlCmdVars in publish.xml will have their values overwritten.

-DacOption

Export options for a corresponding export type. Can be created by New-DbaDacOption -Type Dacpac | Bacpac

-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.

-DacFxPath

Path to the dac dll. If this is omitted, then the version of dac dll which is packaged with dbatools is used.

-WhatIf

Shows what would happen if the command were to run. No actions are actually performed.

-Confirm

Prompts you for confirmation before executing any changing operations within the command.