SqlPackage.exe

SqlPackage.exe is a command line utility to manage DACPACs (Data-tier Application Component Packages), extract schemas or tables from a source database and import those into a target database.

If installed with the SSMS or/and(?) the Dac Framework MSI:, SqlPackage.exe is installed under C:\Program Files (x86)\Microsoft SQL Server\nnn\DAC\bin.

If installed via SSDT, SqlPackage.exe is installed in a path like C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130.

Fundamental operation modes

When invoked, SqlPackage operates in one of seven possible operation modes:

Extract
DeployReport
DriftReport
Publish
Script
Export
Import

This mode needs to specified with the /Action (abbreviatable with /a) command line flag, for example

C:\> SqlPackage /a:script …

Cloning a database structure

The following SqlPackage.exe invocations try to demonstrate how it is possible to clone a database structure without data. Such a cloned database might then be used for example to load it with with test data.

First, SqlPackage.exe is used to extract the structure of a database. The following command creates a .dacpac file, named extract.dacpac:

sqlPackage.exe                          ^
  /action:extract                       ^
  /sourceServerName:SrvFrom\Inst        ^
  /sourceDatabaseName:DBFrom            ^
  /targetFile:extract.dacpac            ^
  /p:extractAllTableData=true

This extract.dacpac file is then used to create an SQL script with the statements to create the database. The parameter /DeployScriptPath names the file to be created:

sqlPackage.exe                          ^
  /action:script                        ^
  /sourceFile:extract.dacpac            ^
  /deployScriptPath:createDatabase.sql  ^
  /targetServerName:SrvDest\Inst        ^
  /targetDatabaseName:DbTo              ^
  /p:createNewDatabase=true

This script can now be executed on the target instance with sqlcmd.exe.

createDatabase.sql was created with a :on error exit statement. Thus, if it encounters an error, it does not install the remaining objects. If the source objects reference objects in another database (for example views), these objects would then cause such an error. If I am not concerned with such cases and still want to install the remainin objects, I can force sqlcmd to skip errors with the -V 20 option:

sqlcmd -S SrvDest\Inst -i createDatabase.sql -V 20

Of course, if the original and cloned database are on the same instance, dbcc clonedatabase is the easier option.

SqlPackage.exe

Fundamental operation modes

Cloning a database structure

See also