Extract DacPac Using Command Line

When extracting Dacpacs it’s quite straightforward to go through the UI and extract, however what if you want to do this for more than one database, or automate the process for a build? The sqlpackage.exe enables us to extract a dacpac in silent mode. Below is the command, and I will go through each parameter:

"C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe" /action:Extract /OverwriteFiles:False /tf:"C:\Users\richard.lee\Documents\dacpacs\dbname_Baseline.dacpac" /SourceConnectionString:"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=AdventureWorks;Data Source=SQLA"
  • /action: we want to extract the dacpac to a location. The other options available for the action parameter are: Extract | DeployReport | DriftReport | Publish | Script | Export | Import
  • /tf: short form for the parameter /targetfile. This is the location of where we want to create the dacpac to. If a dacpac of the same name already exists then it will overwrite that dacpac.
  • /sourceconnectionstring: the short form for this parameter is /scs. This connects to the database you want to extract the dacpac from.
  • /OverwriteFiles: Specifies if sqlpackage.exe should overwrite existing files. Specifying False causes sqlpackage.exe to abort action if an existing file is encountered. Default value is True

Looking at the MSDN site for sqlpackage.exe it is clear that there are many more parameters available, but for my database, these are the only ones I need.
The /tf parameter is also the only /target parameter that is accepted when the action is set to extract, and all /source parameters are valid except for /sourcefile.

Some you need to be wary of:

  • /p:IgnorePermissions: if you have the permissions scripted out for all the service accounts and users, or they exist in the database solution then it would make sense to set this to false. By default, users with sys_admin on the instance will not lose their access.  Ultimately what you choose here will depend on whether you also have the IgnorePermissions property set in the publish.xml file for when you script out the deployment file.
  • /p:Storage: I’ve blogged in depth about this one before here but to sum up; if you have lots of memory on the servers that you are extracting from, or they’re not production servers, or the databases are small, then keep this at memory. However if you don’t want to impede performance on a data warehouse server, or there is no spare memory, then set this option to file. It may take longer but it is worth it in the long term.
  • /p:ExtractAllTableData: It’s important to note that when you are extracting a dacpac,if you set the /p:ExtractAllTableData=true then you will not be able to specify individual tables using the /p:TableData property. In order to specify one or more user tables from which to extract data set /p:ExtractAllTableData=false.

Here is the MSDN site for all the parameters that are available for SqlPackage.exe.

 

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

4 thoughts on “Extract DacPac Using Command Line”

  1. how do I specify more than one table in parameter /p:TableData
    I delimit between two tables with comma and it says “Unrecognized command line argument ,”
    When I specify a single table it works fine.

  2. I got it, just duplicate the /p:TableData for each table. e.g. /p:TableData=dbo.Tab1 /p:TableData=dbo.Tab2 /p:TableData dbo.Tab3

    thank you

    1. Hi Paul, thanks for sharing your solution here. I was going to suggest a list in double quotes. Glad you got it sorted.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s