< Back
Export-DbaDacPackage
Post
NAME Export-DbaDacPackage
SYNOPSIS
Exports a dacpac from a server.
SYNTAX
Export-DbaDacPackage -SqlInstance <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]> [-SqlCredential
<Pscredential>] [-Database <System.Object[]>] [-ExcludeDatabase <System.Object[]>] [-AllUserDatabases <Switch>]
[-Path <String>] [-FilePath <String>] [-DacOption <System.Object>] [-Type <String>] [-Table <System.String[]>]
[-EnableException <Switch>] [<CommonParameters>]
Export-DbaDacPackage -SqlInstance <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]> [-SqlCredential
<Pscredential>] [-Database <System.Object[]>] [-ExcludeDatabase <System.Object[]>] [-AllUserDatabases <Switch>]
[-Path <String>] [-FilePath <String>] [-ExtendedParameters <String>] [-ExtendedProperties <String>] [-Type
<String>] [-EnableException <Switch>] [<CommonParameters>]
DESCRIPTION
Using SQLPackage, export a dacpac from an instance of SQL Server.
Note - Extract from SQL Server is notoriously flaky - for example if you have three part references to external
databases it will not work.
For help with the extract action parameters and properties, refer to
https://msdn.microsoft.com/en-us/librar ... .103).aspx
PARAMETERS
-AllUserDatabases [<Switch>]
Run command against all user databases
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.Object[]>]
The database(s) to process - this list is auto-populated from the server. If unspecified, all databases will
be processed.
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
-ExcludeDatabase [<System.Object[]>]
The database(s) to exclude - this list is auto-populated from the server
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExtendedParameters [<String>]
Optional parameters used to extract the DACPAC. More information can be found at
https://msdn.microsoft.com/en-us/library/hh550080.aspx
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExtendedProperties [<String>]
Optional properties used to extract the DACPAC. More information can be found at
https://msdn.microsoft.com/en-us/library/hh550080.aspx
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-FilePath [<String>]
Specifies the full file path of the output file.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Path [<String>]
Specifies the directory where the file or files will be exported.
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
-Table [<System.String[]>]
List of the tables to include into the export. Should be provided as an array of strings: dbo.Table1, Table2,
Schema1.Table3.
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
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>Export-DbaDacPackage -SqlInstance sql2016 -Database SharePoint_Config
Exports the dacpac for SharePoint_Config on sql2016 to $home\\Documents\\SharePoint_Config.dacpac
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>$options = New-DbaDacOption -Type Dacpac -Action Export
PS C:\\> $options.ExtractAllTableData = $true
PS C:\\> $options.CommandTimeout = 0
PS C:\\> Export-DbaDacPackage -SqlInstance sql2016 -Database DB1 -Options $options
Uses DacOption object to set the CommandTimeout to 0 then extracts the dacpac for DB1 on sql2016 to
$home\\Documents\\DB1.dacpac including all table data.
-------------------------- EXAMPLE 3 --------------------------
PS C:\\>Export-DbaDacPackage -SqlInstance sql2016 -AllUserDatabases -ExcludeDatabase "DBMaintenance","DBMonitoring"
-Path "C:\\temp"
Exports dacpac packages for all USER databases, excluding "DBMaintenance" & "DBMonitoring", on sql2016 and saves
them to C:\\temp
-------------------------- EXAMPLE 4 --------------------------
PS C:\\>$moreparams = "/OverwriteFiles:$true /Quiet:$true"
PS C:\\> Export-DbaDacPackage -SqlInstance sql2016 -Database SharePoint_Config -Path C:\\temp -ExtendedParameters
$moreparams
Using extended parameters to over-write the files and performs the extraction in quiet mode. Uses command line
instead of SMO behind the scenes.
RELATED LINKS
https://dbatools.io/Export-DbaDacPackage
SYNOPSIS
Exports a dacpac from a server.
SYNTAX
Export-DbaDacPackage -SqlInstance <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]> [-SqlCredential
<Pscredential>] [-Database <System.Object[]>] [-ExcludeDatabase <System.Object[]>] [-AllUserDatabases <Switch>]
[-Path <String>] [-FilePath <String>] [-DacOption <System.Object>] [-Type <String>] [-Table <System.String[]>]
[-EnableException <Switch>] [<CommonParameters>]
Export-DbaDacPackage -SqlInstance <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]> [-SqlCredential
<Pscredential>] [-Database <System.Object[]>] [-ExcludeDatabase <System.Object[]>] [-AllUserDatabases <Switch>]
[-Path <String>] [-FilePath <String>] [-ExtendedParameters <String>] [-ExtendedProperties <String>] [-Type
<String>] [-EnableException <Switch>] [<CommonParameters>]
DESCRIPTION
Using SQLPackage, export a dacpac from an instance of SQL Server.
Note - Extract from SQL Server is notoriously flaky - for example if you have three part references to external
databases it will not work.
For help with the extract action parameters and properties, refer to
https://msdn.microsoft.com/en-us/librar ... .103).aspx
PARAMETERS
-AllUserDatabases [<Switch>]
Run command against all user databases
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.Object[]>]
The database(s) to process - this list is auto-populated from the server. If unspecified, all databases will
be processed.
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
-ExcludeDatabase [<System.Object[]>]
The database(s) to exclude - this list is auto-populated from the server
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExtendedParameters [<String>]
Optional parameters used to extract the DACPAC. More information can be found at
https://msdn.microsoft.com/en-us/library/hh550080.aspx
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExtendedProperties [<String>]
Optional properties used to extract the DACPAC. More information can be found at
https://msdn.microsoft.com/en-us/library/hh550080.aspx
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-FilePath [<String>]
Specifies the full file path of the output file.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Path [<String>]
Specifies the directory where the file or files will be exported.
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
-Table [<System.String[]>]
List of the tables to include into the export. Should be provided as an array of strings: dbo.Table1, Table2,
Schema1.Table3.
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
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>Export-DbaDacPackage -SqlInstance sql2016 -Database SharePoint_Config
Exports the dacpac for SharePoint_Config on sql2016 to $home\\Documents\\SharePoint_Config.dacpac
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>$options = New-DbaDacOption -Type Dacpac -Action Export
PS C:\\> $options.ExtractAllTableData = $true
PS C:\\> $options.CommandTimeout = 0
PS C:\\> Export-DbaDacPackage -SqlInstance sql2016 -Database DB1 -Options $options
Uses DacOption object to set the CommandTimeout to 0 then extracts the dacpac for DB1 on sql2016 to
$home\\Documents\\DB1.dacpac including all table data.
-------------------------- EXAMPLE 3 --------------------------
PS C:\\>Export-DbaDacPackage -SqlInstance sql2016 -AllUserDatabases -ExcludeDatabase "DBMaintenance","DBMonitoring"
-Path "C:\\temp"
Exports dacpac packages for all USER databases, excluding "DBMaintenance" & "DBMonitoring", on sql2016 and saves
them to C:\\temp
-------------------------- EXAMPLE 4 --------------------------
PS C:\\>$moreparams = "/OverwriteFiles:$true /Quiet:$true"
PS C:\\> Export-DbaDacPackage -SqlInstance sql2016 -Database SharePoint_Config -Path C:\\temp -ExtendedParameters
$moreparams
Using extended parameters to over-write the files and performs the extraction in quiet mode. Uses command line
instead of SMO behind the scenes.
RELATED LINKS
https://dbatools.io/Export-DbaDacPackage