ssms怎么导入sql文件_使用SSMS导入SQL数据库的BACPAC文件

ssms怎么导入sql文件

This article gives you an overview of BACPAC package and its usage in SQL Database data refresh (data import and export) using SQL Server management studio.

本文概述了BACPAC软件包及其在使用SQL Server Management StudioSQL数据库数据刷新（数据导入和导出）中的用法。

介绍 (Introduction)

Sometimes we get the requirement of refreshing few tales from other copies of the SQL Database. You can use various methods for this task as following:

有时我们需要从SQL数据库的其他副本中刷新一些故事。您可以使用多种方法来完成此任务，如下所示：

Generate data script
生成数据脚本
Data import and export wizard
数据导入和导出向导

SQL Server does not support restoring individual tables from database backups. You can restore database backup with another database and import data from that database. For large databases, we cannot use this approach due to space and time constraints. You require space for storing the backup file as well as data and log files.

SQL Server不支持从数据库备份还原单个表。您可以使用另一个数据库还原数据库备份，并从该数据库导入数据。对于大型数据库，由于空间和时间限制，我们无法使用此方法。您需要空间来存储备份文件以及数据和日志文件。

Another problem is the firewall issues. Many times, organizations do not allow firewalls between production and test environment. In this case, it becomes difficult to copy production data to lower the environment using a jump server using import and export wizards and backup methods as well. In this article, we will look at an alternative method of using a BACPAC package for refreshing tables.

另一个问题是防火墙问题。很多时候，组织不允许在生产和测试环境之间使用防火墙。在这种情况下，使用跳转服务器使用导入和导出向导以及备份方法来复制生产数据以降低环境变得困难。在本文中，我们将介绍使用BACPAC包刷新表的另一种方法。

DACPAC和BACPAC封装概述 (Overview of a DACPAC and BACPAC package)

DACPAC is an acronym of Data-Tier Application Package. It is a logical database entity defining all database objects such as tables, views, users and logins. It enables developers and database administrators to create a single package file consisting of database objects. We call this package a DACPAC package.

DACPAC是d ATA-层的首字母缩写甲 PPLIÇ通货膨胀的Pac卡格。它是一个逻辑数据库实体，定义了所有数据库对象，例如表，视图，用户和登录名。它使开发人员和数据库管理员可以创建由数据库对象组成的单个程序包文件。我们将此程序包称为DACPAC程序包。

Usually, developers use SQL Server Data Tools (SSDT) for writing t-SQL scripts and making changes as per the requirement. They can generate a DACPAC package and send it to DBAs for deploying in multiple environments. Similarly, DBA can give the DACPAC copy of a production database to developers for their development purpose.

通常，开发人员使用SQL Server数据工具（SSDT）编写t-SQL脚本并根据需要进行更改。他们可以生成DACPAC软件包并将其发送给DBA，以在多个环境中进行部署。同样，DBA可以将生产数据库的DACPAC副本提供给开发人员以用于其开发目的。

We can also use DACPAC packages for schema comparison as well between different copies of it. You can use the Azure Data Studio SQL Server Schema Compare extension for this.

我们还可以使用DACPAC软件包进行模式比较，以及在其不同副本之间进行模式比较。您可以为此使用Azure Data Studio SQL Server架构比较扩展。

Similarly, BACPAC ( Backup package) consists of schema and data for SQL Server tables. We can export database tables in a BACPAC package and give it to developers for refreshing data per their convenience.

同样，BACPAC（ Bac kup pac kage）由SQL Server表的架构和数据组成。我们可以将数据库表导出为BACPAC包，并提供给开发人员以方便他们刷新数据。

We get options for DACPAC and BACPAC package in SSMS.

我们在SSMS中获得DACPAC和BACPAC封装的选项。

Note: I would recommend always using the latest version of SQL Server Management Studio. The latest version is SSMS 18.4.

注意：我建议始终使用最新版本SQL Server Management Studio。最新版本是SSMS 18.4。

Connect to a SQL instance and expand databases in the object explorer. Right-click on a database in SSMS and you get data-tier application options as shown below.

连接到SQL实例并在对象资源管理器中扩展数据库。右键单击SSMS中的数据库，您将获得如下所示的数据层应用程序选项。

Export Data-tier application: We can extract schema and data as well in a BACPAC file using this option
导出数据层应用程序：我们也可以使用此选项在BACPAC文件中提取模式和数据
Extract Data-tier application: We use this wizard for extracting a database in a DACPAC package. This DACPAC package does not contain data
提取数据层应用程序：我们使用此向导来提取DACPAC软件包中的数据库。此DACPAC程序包不包含数据
Upgrade Data-tier application: We can upgrade a previously deployed database using this upgrade data tier option
升级数据层应用程序：我们可以使用此升级数据层选项升级以前部署的数据库

BACPAC软件包的导出数据层应用程序向导 (Export Data-tier application wizard for BACPAC package)

Let’s export data and schema for a database and create another copy of a database in the same instance for this demonstration.

让我们导出数据库的数据和架构，并在同一实例中为该演示创建数据库的另一个副本。

We use the following steps for doing this task.

我们使用以下步骤执行此任务。

Right-click on the source SQL Database and click on Export Data-tier application. It launches an introduction page. This introduction page defies the summary and high-level steps for this wizard. The high-level steps are as follows:

右键单击源SQL数据库，然后单击“ 导出数据层应用程序”。 它会启动一个简介页面。本简介页违反了此向导的摘要步骤和高级步骤。高级步骤如下：

Specify export settings
指定导出设置
Review the export summary
查看导出摘要
Check the result of the operation
检查操作结果

If we do not require this introduction page from now onwards, we can check Do not show this page again option.

如果从现在起不再需要此介绍页面，则可以选中“不再显示此页面”选项。

Click next, and it takes you to the Export setting page.

单击下一步，将带您到“ 导出设置”页面。

The export setting page allows us to configure either a local disk directory or Microsoft Azure storage container. For Microsoft Azure, you should have a valid storage account and container for storing the BACPAC file.

通过导出设置页面，我们可以配置本地磁盘目录或Microsoft Azure存储容器。对于Microsoft Azure，您应该具有一个有效的存储帐户和用于存储BACPAC文件的容器。

Let’s use a local disk directory, as shown below.

让我们使用本地磁盘目录，如下所示。

By default, it extracts all schema, tables (with data)available in the database. We can also filter selected objects in the advanced page select all database schema, tables in the advanced tab of this page.

默认情况下，它将提取数据库中可用的所有架构，表（以及数据）。我们还可以在高级页面中过滤选定的对象，在此页面的高级选项卡中选择所有数据库模式，表。

Click on the advanced tab and review the objects. It groups objects per schema such as dbo.

单击高级选项卡，然后查看对象。它将每个架构（例如dbo）的对象分组。

We are not using any object filtering in BACPAC export. It will create an export for all tables in the database. Click on Next and review the configurations.

BACPAC导出中未使用任何对象过滤。它将为数据库中的所有表创建一个导出。单击下一步并查看配置。

Click Finish, and it starts the export of the database. You can see the success message for each step.

单击“完成”，它开始导出数据库。您可以看到每个步骤的成功消息。

We can browse to the directory and verify a BACPAC extension file exists.

我们可以浏览到目录并验证BACPAC扩展文件是否存在。

使用SSMS导入数据层应用程序向导导入BACPAC文件
(Import a BACPAC file using SSMS Import Data-tier Application wizard
)

We can copy this BACPAC file to destination SQL instance having either on-premise or Azure SQL Database. Right-click on the connected destination instance and launch Import Data-tier applications wizard.

我们可以将此BACPAC文件复制到具有本地或Azure SQL数据库的目标SQL实例。右键单击连接的目标实例，然后启动“ 导入数据层应用程序”向导。

The high-level steps for importing a BACPAC package are:

导入BACPAC软件包的高级步骤是：

Specify the BACPAC import file
指定BACPAC导入文件
Import Configuration
导入配置
Import and verify data
导入并验证数据

Click next, and it takes you to the import settings page. Here, we specify the path of a BACPAC file either from the local disk or Microsoft Azure Storage container. We used local disk storage during the import process so, select the local disk option and browse to the directory containing required BACPAC package.

单击下一步，将带您到导入设置页面。在这里，我们从本地磁盘或Microsoft Azure存储容器中指定BACPAC文件的路径。我们在导入过程中使用了本地磁盘存储，因此，选择本地磁盘选项并浏览到包含所需BACPAC软件包的目录。

In the next page, we specify a setting for the new database. We cannot use an existing SQL Database for importing a BACPAC package.

在下一页中，我们为新数据库指定一个设置。我们不能使用现有SQL数据库导入BACPAC程序包。

It also requires input for the data and log file of this new database. By default, it shows you default data and log file location of connected SQL instance. We can change it per requirement.

它还需要输入此新数据库的数据和日志文件。默认情况下，它显示连接SQL实例的默认数据和日志文件位置。我们可以根据要求更改它。

As highlighted earlier, we cannot use an existing database for BACPAC file import. We get the following error message if the database already exists.

如前所述，我们不能将现有数据库用于BACPAC文件导入。如果数据库已经存在，我们将收到以下错误消息。

Modify the new database name. In my demonstration, I specified AdventureWorksDW_BACPAC name for SQL Database. Click next and view summary.

修改新的数据库名称。在演示中，我为SQL数据库指定了AdventureWorksDW_BACPAC名称。单击下一步，然后查看摘要。

The next page is a summary page showcasing your configurations. I recommend you always review it before proceeding. You can easily navigate back and make the required changes.

下一页是显示您的配置的摘要页面。我建议您在继续操作之前始终对其进行检查。您可以轻松地向后导航并进行所需的更改。

Click Finish, and you can see the following steps.

单击完成，您可以看到以下步骤。

Creates a new database
创建一个新的数据库
Imports schema and data into this new database
将架构和数据导入此新数据库
Disables indexes
禁用索引
Inserts data into tables
将数据插入表
Enable indexes
启用索引

Refresh the object explorer, and you see the newly restored database.

刷新对象资源管理器，您将看到新还原的数据库。

Let’s compare the record counts for [dbo].[DimDate] tables in both AdventureWorksDW2017 and AdventureWorksDW_BACPAC databases.

让我们比较AdventureWorksDW2017和AdventureWorksDW_BACPAC数据库中[dbo]。[DimDate]表的记录计数。

In the following screenshot, verify that it contains the same record count in both the SQL Databases.

在下面的屏幕截图中，验证两个SQL数据库中是否包含相同的记录数。

验证从BACPAC软件包导入的数据库的架构 (Validating schema of the database imported from a BACPAC package)

Let’s take a step further and compare the schema in both the databases using schema compare extension of Azure Data Studio. I am not elaborating the steps here for this. You can refer to this article Schema Compare extension for a detailed explanation.

让我们更进一步，使用Azure Data Studio的架构比较扩展在两个数据库中比较架构。我在这里没有详细说明步骤。您可以参考本文的Schema Compare扩展以获取详细说明。

As you can see, there is no schema difference between the source (AdventureWorksDW2017), and the target database restored from BACPAC (AdventureWorksDW2017_ BACPAC).

如您所见，源（AdventureWorksDW2017）与从BACPAC恢复的目标数据库（AdventureWorksDW2017_ BACPAC）之间没有架构差异。

You have data in a new database. Now, you can use import and export wizard as explained in this article How to import/export data to SQL Server using the SQL Server Import and Export Wizard.

您在新数据库中有数据。现在，您可以按照本文如何使用SQL Server导入和导出向导将数据导入/导出到SQL Server中的说明使用导入和导出向导。

For example, let’s say we require a refresh of[dbo].[ProspectiveBuyer] the table in the AdventureWorks2017DW database. We can use the following steps for it.

例如，假设我们需要刷新AdventureWorks2017DW数据库中的[dbo]。[ProspectiveBuyer]表。我们可以使用以下步骤。

Create a BACPAC file from the source AdventureWorks2017DW database for the [dbo].[ProspectiveBuyer] table as steps shown above
从源AdventureWorks2017DW数据库为[dbo]。[ProspectiveBuyer]表创建BACPAC文件，如上所示。
Copy this BACPAC file from the source server to the destination server
将此BACPAC文件从源服务器复制到目标服务器
Import this BACPAC file and create a new SQL Database on the destination instance
导入此BACPAC文件并在目标实例上创建一个新SQL数据库
Use Import and export wizard in copying data from a new database to source database
使用导入和导出向导将数据从新数据库复制到源数据库

结论 (Conclusion)

In this article, we explored the usage of a BACPAC package for export and import of SQL database tables data. It is an alternate method for data refresh; however, you should review the possible approaches and choose what fits best for your requirements.

在本文中，我们探讨了如何使用BACPAC软件包导出和导入SQL数据库表数据。它是数据刷新的替代方法。但是，您应该查看可能的方法并选择最适合您的要求的方法。

翻译自: https://www.sqlshack.com/importing-a-bacpac-file-for-a-sql-database-using-ssms/