Export and Import BACPAC using command line

SQLPackage.exe can be used for the following tasks:

  • Extract: Creates a database snapshot (.dacpac) file from an on-premise (local) SQL Server or Windows Azure SQL Database.
  • Export: Exports a on-premise (local) database – including database schema and user data – from SQL Server or Windows Azure SQL Database to a BACPAC package (.bacpac file).
  • Import: Imports the schema and table data from a BACPAC package into a new user database in an instance of SQL Server or Windows Azure SQL Database.
  • Publish: Incrementally updates a database schema to match the schema of a source .dacpac file. If the database does not exist on the server, the publish operation will create it. Otherwise, an existing database will be updated.
  • DeployReport: Creates an XML report of the changes that would be made by a publish action.
  • DriftReport: Creates an XML report of the changes that have been made to a registered database since it was last registered.
  • Script: Creates a Transact-SQL incremental update script that updates the schema of a target to match the schema of a source.

Let’s see some examples

Export the local database to a bacpac file

“C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe” /a:Export /ssn:mysqlserver /sdn:Tailspintoys /tf:C:\DataExtraction\Tailspintoys.bacpac

ssn – Specifies the name of the server that hosts the database.
sdn – Defines the name of the source database.
tf – Specifies a disk file path where the .dacpac or .bacpac file will be written.
If the logged in user is executing the script, Integrated Windows Authentication will be used. So no need to specify the username or password.

Import the bacpac file to SQLAzure

“C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe” /a:Import /sf:C:\DataExtraction\Tailspintoys.bacpac /tsn:cgrd7z8kac.database.windows.net /tdn:Tailspintoys /tu:mysysadmin@cgrd7z8kac /tp:Pa55w0rd

sf – Specifies a source file to be used as the source of action instead of database.
tsn – Specifies the name of the server that hosts the target database.
tdn – Specifies the name of the target database.
tu – SQL Server user that is used to get access to the target database.
tp – specifies password that is used to get access to the target database.

If you are planning to automate this using C# console application, there is a code sample available here



To put it simple,

  • DACPAC – Only Schema
  • BACPAC – Schema + Data (Native BCP format, previously compressed JSON)

A data-tier application (DAC) is a self-contained unit for developing, deploying, and managing data-tier objects. A DAC enables data-tier developers and database administrators to package Microsoft SQL Server objects, including database objects and instance objects, into a single entity called a DAC package (.dacpac file). The BACPAC format extends the DAC package format to include BACPAC-specific metadata and JavaScript Object Notation (JSON)–encoded table data in addition to the standard DAC package contents.

They can be interacted using SSMS, PowerShell and .NET. It is the most used method of backup and restore SQL Azure database.

You can rename either of the packages to a *.zip extension to see the XML content, however it is not the same as unpacking them.

If you have DAC installed on the machine, you can “Unpack” the file by right click contest menu entry.

DACPACs can be extracted through commandline using sqlpackage.exe

e.g. “C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe” /action:Extract /OverwriteFiles:False /tf:”C:\MyTestDB.dacpac” /SourceConnectionString:”Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TailSpinToys;Data Source=SQL2012SERVER”

More info on sqlpackage – http://msdn.microsoft.com/en-us/library/hh550080%28v=vs.103%29.aspx