In this article, I’m going to explain in detail about the SQLPackage utility that is provided by Microsoft in order to automate database deployments. In my previous article regarding Data-Tier Applications in SQL Server, I have mentioned how to create a DACPAC and a BACPAC file from an existing SQL Server Database. The underlying technology behind creating the DACPAC and the BACPAC files is the SQLPackage utility, which helps to create those files.

在本文中，我将详细解释Microsoft提供SQLPackage实用程序，以实现数据库部署自动化。 在上一篇有关SQL Server中的数据层应用程序的文章中 ，我提到了如何从现有SQL Server数据库中创建DACPAC和BACPAC文件。 创建DACPAC和BACPAC文件的基础技术是SQLPackage实用程序，该实用程序有助于创建这些文件。

The SQLPackage utility is a cross platform, command line tool and can be installed on Windows, Linux and MacOS. You can easily download the utility from the official website based on the operating system you are using. At the time of writing this article, the latest version of the utility is 18.5, however, it may change if you are reading it at some later point.

SQLPackage实用程序是一个跨平台的命令行工具，可以安装在Windows，Linux和MacOS上。 您可以根据所使用的操作系统从官方网站轻松下载该实用程序。 在撰写本文时，该实用程序的最新版本是18.5，但是，如果稍后再阅读，它可能会更改。

安装SQLPackage实用程序 (Installing the SQLPackage Utility)

Let’s head over to the official website and download the windows MSI installer file and start installing the package on the local machine.

让我们转到官方网站并下载Windows MSI安装程序文件，然后开始在本地计算机上安装软件包。

Figure 1 – Download SQL Package

图1 –下载SQL包

The name of the downloaded file will be DacFramework.msi. Click on the file and start installing it.

下载文件的名称将为DacFramework.msi 。 单击该文件并开始安装。

Figure 2 – Install the SQL Package

图2 –安装SQL软件包

Once the package is installed successfully, lets move to set the environment variables so that we can start using the SQLPackage utility from the command line directly. Click on the Start button and search for Environment Variables. Select Edit the system environment variables. A new dialog box will open.

成功安装软件包后，让我们继续设置环境变量，以便我们可以直接从命令行开始使用SQLPackage实用程序。 单击开始按钮，然后搜索环境变量 。 选择编辑系统环境变量 。 将打开一个新对话框。

Figure 3 – Edit Environment Variables

图3 –编辑环境变量

Click on the Environment Variables button…

单击环境变量按钮…

Figure 4 – Environment Variables

图4 –环境变量

In the next step, click on the New button and then add the binary file path of the DAC program directory. In my case, the executable file for the SQLPackage is usually located at “C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin“. Once the path has been added click on OK.

在下一步中，单击“ 新建”按钮，然后添加DAC程序目录的二进制文件路径。 就我而言，SQLPackage的可执行文件通常位于“ C：\ Program Files（x86）\ Microsoft SQL Server \ 140 \ DAC \ bin ”。 添加路径后，单击“ 确定” 。

Figure 5 – Added path for the Executable File

图5 –可执行文件的添加路径

Now that the path of the executable has been added to the system variables, we can directly call the SQLPackage from the command-line. Start the command prompt and type SQLPackage and press Enter. If the package has been installed successfully and the path set correctly, you will see the options from the package.

现在，可执行文件的路径已添加到系统变量中，我们可以从命令行直接调用SQLPackage。 启动命令提示符并键入SQLPackage ，然后按Enter 。 如果软件包已成功安装并且路径设置正确，您将看到软件包中的选项。

Figure 6 – SQLPackage from Command Line

图6 –命令行中SQLPackage

使用SQLPackage实用程序和操作 (Using the SQLPackage Utility and Actions)

The SQLPackage can be used to perform several different database functions like deploying a DACPAC file to a database, creating a DACPAC from an existing database, comparing the schema between two different databases and so on. In order to achieve these, the utility offers the following functions or actions which are discussed in detail in the next section.

SQLPackage可用于执行几种不同的数据库功能，例如将DACPAC文件部署到数据库，从现有数据库创建DACPAC，比较两个不同数据库之间的架构等等。 为了实现这些目的，该实用程序提供以下功能或动作，下一部分将对其进行详细讨论。

The common syntax to execute the commands are “SQLPACKAGE <<PARAMETERS>> <<PROPERTIES>> <<SQLCMDVARIABLES>>“. You can add multiple parameters to a command to perform complex tasks. The SQLCMD variables are used to pass variable values to a parameterized script which can be executed against a SQL Server database.

执行命令的常用语法是“ SQLPACKAGE << PARAMETERS >> << PROPERTIES >> << SQLCMDVARIABLES >> ”。 您可以向命令添加多个参数以执行复杂的任务。 SQLCMD变量用于将变量值传递到可以针对SQL Server数据库执行的参数化脚本。

The following actions are performed by the SQLPackage which are described below:

SQLPackage执行以下操作，如下所述：

1. Extract: This action is used to create a DACPAC file from the SQL Server database. For this, a database needs to be up and running in the server before creating the DACPAC file. The source database can be either SQL Server or Azure SQL Database. The utility connects to the source database using the connection string provided and then extracts the DACPAC file to the local path specified while initiating the command 提取 ：此操作用于从SQL Server数据库创建DACPAC文件。 为此，在创建DACPAC文件之前，需要在服务器中启动并运行数据库。 源数据库可以是SQL Server或Azure SQL数据库。 该实用程序使用提供的连接字符串连接到源数据库，然后在启动命令时将DACPAC文件提取到指定的本地路径
2. DeployReport: This action is used by the database administrators to generate an XML report that provides the list of the changes that are going to be applied to a database when it is published. The utility compares the source database or the DACPAC file to the target database and generates the report based on the schema DeployReport ：数据库管理员使用此操作来生成XML报告，该报告提供将在发布数据库时应用于数据库的更改的列表。 该实用程序将源数据库或DACPAC文件与目标数据库进行比较，并根据架构生成报告
3. DriftReport: This is also another action that generates a list of changes that has been applied to a published database after it has been registered. For example, let’s consider a database has been published today and after a few days some changes were applied to the database directly. The DriftReport will generate a list of changes that has been applied to the database since the day it was published DriftReport ：这也是另一个操作，它生成已注册的发布数据库之后已应用到已发布数据库的更改列表。 例如，让我们考虑一个数据库已于今天发布，几天后直接将一些更改应用于该数据库。 DriftReport将生成自数据库发布之日起已应用于数据库的更改列表。
4. Publish: This is the most widely used action according to me. It is used to publish a database from a DACPAC file. The magic here is that the utility automatically detects the state of the database on the server and then incrementally updates it based on the information available. For example, if in the target location, the database is not present then the utility will create the database as a fresh, otherwise it will incrementally update the schema changes which is present in the DACPAC and not within the database 发布 ：根据我的看法，这是使用最广泛的操作。 它用于从DACPAC文件发布数据库。 这里的妙处在于，实用程序会自动检测服务器上数据库的状态，然后根据可用信息进行增量更新。 例如，如果在目标位置中不存在数据库，则该实用程序将创建数据库作为新数据库，否则它将增量更新DACPAC中而不是数据库中存在的架构更改
5. Script: This is the action which generates a T-SQL script after comparing the schema difference between the source and the target database and saves the file to the local machine. This script is also an incremental T-SQL script, which means that if the target database is not present then it will create it otherwise it will just update the database 脚本 ：此操作是在比较源数据库和目标数据库之间的架构差异之后生成T-SQL脚本并将该文件保存到本地计算机的操作。 该脚本也是一个增量式T-SQL脚本，这意味着如果目标数据库不存在，它将创建它，否则它将仅更新数据库
6. Export: This action is used to create a backup of a live SQL Server database or Azure SQL Database. The data along with the schema is exported to a BACPAC file. The utility connects to the source database and then creates the BACPAC file on the local machine. This action might take some time depending on the size of the database as there is going to be data movement 导出 ：此操作用于创建实时SQL Server数据库或Azure SQL数据库的备份。 数据与模式一起导出到BACPAC文件。 该实用程序连接到源数据库，然后在本地计算机上创建BACPAC文件。 此操作可能需要一些时间，具体取决于数据库的大小，因为将要移动数据
7. Import: This is the exact opposite action to the Export action. It is used to import a BACPAC file to a SQL Server instance or an Azure SQL Database instance. The utility considers the BACPAC file as the source and then restores the database along with the schema and data to the SQL Server database 导入 ：这与导出操作完全相反。 它用于将BACPAC文件导入到SQL Server实例或Azure SQL数据库实例。 该实用程序将BACPAC文件视为源，然后将数据库以及架构和数据还原到SQL Server数据库中

SQLPackage实用工具中的发布操作的参数 (Parameters of the Publish Action in SQLPackage Utility)

In the previous section, we have seen several Actions that are supported by SQLPackage. Now let us go ahead and learn the most important parameters that are available to be used within these actions. Apart from the parameters mentioned in this article, there are several others which are available to use and you can see those in the documentation.

在上一节中，我们已经看到了SQLPackage支持的几个动作。 现在，让我们继续学习这些动作中可以使用的最重要的参数。 除了本文提到的参数外，还有其他几个参数可供使用，您可以在文档中看到这些参数。

1. Action: This specifies the action that we are trying to achieve using the SQLPackage utility. It can be anything from the above-mentioned actions. In order to deploy the database, the action should be “动作 ：这指定了我们试图使用SQLPackage实用程序实现的动作。 可以是上述动作中的任何事情。 为了部署数据库，操作应为“ Publish” 发布 ”
2. SourceFile: Usually, in order to deploy the database from a DACPAC file, we need to specify the location of the file. This can be done by specifying the path in the “SourceFile ：通常，为了从DACPAC文件部署数据库，我们需要指定文件的位置。 这可以通过在“ SourceFile” parameter SourceFile ”参数中指定路径来完成
3. TargetDatabaseName: This parameter is used to specify the name of the database in the SQL Server. The DACPAC file will the deployed against this database name and specific actions will be taken forward TargetDatabaseName ：此参数用于指定SQL Server中数据库的名称。 DACPAC文件将针对该数据库名称进行部署，并且将采取特定措施
4. TargetServerName: As it goes by the name, the server to which the database is going to be deployed is mentioned here TargetServerName ：顾名思义，此处提到要将数据库部署到的服务器

For the purpose of this article, I am going to demonstrate the Publish action of the SQLPackage utility. The command to publish a DACPAC file is as below:

出于本文的目的，我将演示SQLPackage实用程序的“ 发布”操作。 发布DACPAC文件的命令如下：

sqlpackage
/action:Publish
/SourceFile:”C:\temp\SQLShackSnapshot.dacpac”
/TargetDatabaseName:SQLShackSnapshot
/TargetServerName:”localhost”

sqlpackage
/ action：发布
/SourceFile：“C:\temp\SQLShackSnapshot.dacpac”
/ TargetDatabaseName：SQLShackSnapshot
/ TargetServerName：“本地主机”

Figure 7 – Database Snapshot Published

图7 –已发布的数据库快照

If you see the output in the above screen, you can see that the SQLPackage has successfully connected to the target server and then created a database based on the schema available in the DACPAC file. This utility can also publish to remote SQL Server machines instead of a local host or even to Azure SQL Database.

如果在上面的屏幕中看到输出，则可以看到SQLPackage已成功连接到目标服务器，然后根据DACPAC文件中可用的架构创建了数据库。 此实用程序还可以发布到远程SQL Server计算机而不是本地主机，甚至发布到Azure SQL数据库。

结论 (Conclusion)

In this article, we have seen the basic features of SQLPackage utility. SQLPackage is an important utility when modern database development and deployment is taken into consideration. It can be easily used create new databases or updated existing databases with minimal configurations. In my upcoming articles, I will also explain about the other actions that are possible with this utility in detail.

在本文中，我们已经了解了SQLPackage实用程序的基本功能。 考虑到现代数据库的开发和部署，SQLPackage是重要的实用程序。 它可以很容易地用于以最少的配置创建新数据库或更新现有数据库。 在接下来的文章中，我还将详细说明此实用程序可能执行的其他操作。

翻译自: https://www.sqlshack.com/getting-started-with-sqlpackage-utility/