< Back
Publish-DbaDacPackage
Post
NAME Publish-DbaDacPackage
SYNOPSIS
The Publish-DbaDacPackage command takes a dacpac and publishes it to a database.
SYNTAX
Publish-DbaDacPackage [-SqlInstance <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>] [-SqlCredential
<Pscredential>] -Path <String> -Database <System.String[]> [-ConnectionString <System.String[]>] [-ScriptOnly
<Switch>] [-Type <String>] [-OutputPath <String>] [-IncludeSqlCmdVars <Switch>] [-DacOption <System.Object>]
[-EnableException <Switch>] [-DacFxPath <String>] [<CommonParameters>]
Publish-DbaDacPackage [-SqlInstance <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>] [-SqlCredential
<Pscredential>] -Path <String> -PublishXml <String> -Database <System.String[]> [-ConnectionString
<System.String[]>] [-GenerateDeploymentScript <Switch>] [-GenerateDeploymentReport <Switch>] [-ScriptOnly
<Switch>] [-Type <String>] [-OutputPath <String>] [-IncludeSqlCmdVars <Switch>] [-EnableException <Switch>]
[-DacFxPath <String>] [<CommonParameters>]
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).
PARAMETERS
-ConnectionString [<System.String[]>]
Specifies the connection string to the database you are upgrading. This is not required if SqlInstance is
specified.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DacFxPath [<String>]
Path to the dac dll. If this is omitted, then the version of dac dll which is packaged with dbatools is used.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DacOption [<System.Object>]
Export options for a corresponding export type. Can be created by New-DbaDacOption -Type Dacpac | Bacpac
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Database [<System.String[]>]
Specifies the name of the database being published.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EnableException [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-GenerateDeploymentReport [<Switch>]
If this switch is enabled, the publish XML report will be generated.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-GenerateDeploymentScript [<Switch>]
If this switch is enabled, the publish script will be generated.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-IncludeSqlCmdVars [<Switch>]
If this switch is enabled, SqlCmdVars in publish.xml will have their values overwritten.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-OutputPath [<String>]
Specifies the filesystem path (directory) where output files will be generated.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Path [<String>]
Specifies the filesystem path to the DACPAC
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-PublishXml [<String>]
Specifies the publish profile which will include options and sqlCmdVariables.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ScriptOnly [<Switch>]
If this switch is enabled, only the change scripts will be generated.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlCredential [<Pscredential>]
Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory -
Integrated are all supported.
For MFA support, please use Connect-DbaInstance.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlInstance [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
The target SQL Server instance or instances.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Type [<String>]
Selecting the type of the export: Dacpac (default) or Bacpac.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
NOTES
Tags: Migration, Database, Dacpac
Author: Richie lee (@richiebzzzt)
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
Deploying a dacpac uses the DacFx which historically needed to be installed on a machine prior to use. In 2016
the DacFx was supplied by Microsoft as a nuget package (Microsoft.Data.Tools.MSBuild) and this uses that nuget
package.
-------------------------- 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
-GenerateDeploymentScript -ScriptOnly
Does not deploy the changes, but will generate the deployment script that would be executed against
WideWorldImporters.
RELATED LINKS
https://dbatools.io/Publish-DbaDacPackage
SYNOPSIS
The Publish-DbaDacPackage command takes a dacpac and publishes it to a database.
SYNTAX
Publish-DbaDacPackage [-SqlInstance <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>] [-SqlCredential
<Pscredential>] -Path <String> -Database <System.String[]> [-ConnectionString <System.String[]>] [-ScriptOnly
<Switch>] [-Type <String>] [-OutputPath <String>] [-IncludeSqlCmdVars <Switch>] [-DacOption <System.Object>]
[-EnableException <Switch>] [-DacFxPath <String>] [<CommonParameters>]
Publish-DbaDacPackage [-SqlInstance <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>] [-SqlCredential
<Pscredential>] -Path <String> -PublishXml <String> -Database <System.String[]> [-ConnectionString
<System.String[]>] [-GenerateDeploymentScript <Switch>] [-GenerateDeploymentReport <Switch>] [-ScriptOnly
<Switch>] [-Type <String>] [-OutputPath <String>] [-IncludeSqlCmdVars <Switch>] [-EnableException <Switch>]
[-DacFxPath <String>] [<CommonParameters>]
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).
PARAMETERS
-ConnectionString [<System.String[]>]
Specifies the connection string to the database you are upgrading. This is not required if SqlInstance is
specified.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DacFxPath [<String>]
Path to the dac dll. If this is omitted, then the version of dac dll which is packaged with dbatools is used.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DacOption [<System.Object>]
Export options for a corresponding export type. Can be created by New-DbaDacOption -Type Dacpac | Bacpac
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Database [<System.String[]>]
Specifies the name of the database being published.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EnableException [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-GenerateDeploymentReport [<Switch>]
If this switch is enabled, the publish XML report will be generated.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-GenerateDeploymentScript [<Switch>]
If this switch is enabled, the publish script will be generated.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-IncludeSqlCmdVars [<Switch>]
If this switch is enabled, SqlCmdVars in publish.xml will have their values overwritten.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-OutputPath [<String>]
Specifies the filesystem path (directory) where output files will be generated.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Path [<String>]
Specifies the filesystem path to the DACPAC
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-PublishXml [<String>]
Specifies the publish profile which will include options and sqlCmdVariables.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ScriptOnly [<Switch>]
If this switch is enabled, only the change scripts will be generated.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlCredential [<Pscredential>]
Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory -
Integrated are all supported.
For MFA support, please use Connect-DbaInstance.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlInstance [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
The target SQL Server instance or instances.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Type [<String>]
Selecting the type of the export: Dacpac (default) or Bacpac.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
NOTES
Tags: Migration, Database, Dacpac
Author: Richie lee (@richiebzzzt)
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
Deploying a dacpac uses the DacFx which historically needed to be installed on a machine prior to use. In 2016
the DacFx was supplied by Microsoft as a nuget package (Microsoft.Data.Tools.MSBuild) and this uses that nuget
package.
-------------------------- 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
-GenerateDeploymentScript -ScriptOnly
Does not deploy the changes, but will generate the deployment script that would be executed against
WideWorldImporters.
RELATED LINKS
https://dbatools.io/Publish-DbaDacPackage