7 Salient Aspects about SQLPackage.exe and Key Development Tasks It is Used For

This article explains the multiple tasks that can be automated using SQLPackage.exe along with a brief description of those tasks.

Out of the multitude of tasks and operations that you can perform in Microsoft SQL Server, there are a few that can be automated using the SQLpackage.exe. This is one of those command lines utility which can be used for automating the given development tasks in SQL Server. This not only makes performing a given a task easier but also helps you save the precious time that you would otherwise spend performing the actions manually. However, this command line utility cannot be used in isolation of other aspects. When making use of Sqlpackage.exe you also need to use the relevant parameters and properties for each of the given objects.

SQLPackage

1. Extract

Using this one can create a snapshot of the database i.e. the .dacpac file, with the help of a Microsoft Azure Database or live SQL Server.

2. Publish

This command is used for incrementally updating the schema of a database so that it matches the schema of source .dacpac file. If the database that you are using is not present on the server, it will be created by the publish operation or will be updated by an existing database. If you are publishing a deployment package containing the user data for subsets of the table or just one subset of tables, it will update the complete table data along with the schema.

3. Export

This can be used for exporting a live database, including elements like database schema as well as user data. This is used for exporting data from MS SQL or Azure database to a BACPAC package. Data in all the tables are a part of the .bacpac file by default. However, the user has the option of specifying a table subset for which they wish to export data. 

4. Import

This can be used for importing table data and schema from a BACPAC package to a newly created database in SQL Server or MS Azure database instance. When the import process for an existing database is in action, the target database will not be able to contain schema objects which are defined by the user.

5. Drift Report

This helps you include all the changes made in a registered database in an XML report. Changes made since the last registration date of the database are included.

6. Deploy Report

This is used for creating an XML report that would comprise of all the changes which would be incorporated once the publish action is performed.

7. Script

This is used for creating a T – SQL incremental update script which is used for updating the target schema so that it matches the source schema. 

These are the actions that can be specified using SqlPackage.exe, along with the specific properties and parameters for the action. There is a long list of parameter and properties that are associated with each of the above-mentioned actions which can be used along with SqlPackage.exe to make operations across the application easier for the users.

Even with a recovery plan in place, companies need to invest in a tool that can fix SQL Server as a failsafe measure. In case of a contingency such specialized tools can help you avoid data loss.  

Author Introduction:

Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit www.datanumen.com