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.