commands

^

Publish-DbaDacPackage

Author Richie lee (@richiebzzzt)
Availability Windows, Linux, macOS

 

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

Alias
Required True
Pipeline true (ByPropertyName)
Default Value
-Database

Specifies the name of the database being published.

Alias
Required True
Pipeline true (ByPropertyName)
Default Value

Optional Parameters

-SqlInstance

The target SQL Server instance or instances.

Alias
Required False
Pipeline false
Default Value
-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.

Alias
Required False
Pipeline false
Default Value
-PublishXml

Specifies the publish profile which will include options and sqlCmdVariables.

Alias
Required False
Pipeline false
Default Value
-ConnectionString

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

Alias
Required False
Pipeline false
Default Value
-GenerateDeploymentReport

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

Alias
Required False
Pipeline false
Default Value False
-ScriptOnly

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

Alias
Required False
Pipeline false
Default Value False
-Type

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

Alias
Required False
Pipeline false
Default Value Dacpac
Accepted Values Dacpac,Bacpac
-OutputPath

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

Alias
Required False
Pipeline false
Default Value (Get-DbatoolsConfigValue -FullName 'Path.DbatoolsExport')
-IncludeSqlCmdVars

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

Alias
Required False
Pipeline false
Default Value False
-DacOption

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

Alias Option
Required False
Pipeline false
Default Value
-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.

Alias
Required False
Pipeline false
Default Value False
-DacFxPath

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

Alias
Required False
Pipeline false
Default Value
-WhatIf

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

Alias wi
Required False
Pipeline false
Default Value
-Confirm

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

Alias cf
Required False
Pipeline false
Default Value

 

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.