如何使用sqlpackage和PowerShell将示例bacpac文件导入到Azure SQL数据库

介绍 (Introduction)

In our first part of this series, we showed how to import a bacpac file using the Azure Portal with Microsoft Azure Storage Explorer (MASE) and SQL Server Management Studio (SSMS) to create a Database in Azure.

在本系列的第一部分中，我们展示了如何使用带有Microsoft Azure存储资源管理器（MASE）和SQL Server Management Studio（SSMS）的Azure门户导入bacpac文件，以在Azure中创建数据库。

In the second part, we will show how to import a bacpac file to create a Database using the sqlpackage.exe tool and PowerShell.

在第二部分中，我们将展示如何导入bacpac文件以使用sqlpackage.exe工具和PowerShell创建数据库。

In PowerShell, we will learn how to create an Azure SQL Server, how to create an Azure storage account and finally how to import the bacpac file.

在PowerShell中，我们将学习如何创建Azure SQL Server，如何创建Azure存储帐户以及最终如何导入bacpac文件。

要求 (Requirements)

1. A machine with sqlpackage.exe installed.
   安装了sqlpackage.exe的计算机。
2. PowerShell installed with PowerShell for Azure installed.
   已安装PowerShell和Azure的PowerShell 。
3. We will use the WideWorldImporters-standard database, but you can use any bacpac file of your preference.
   我们将使用WideWorldImporters-standard数据库 ，但您可以使用自己喜欢的任何bacpac文件。
4. Access to Azure Portal
   访问Azure门户

入门 (Getting started)

If you already read the first part of the article, you already have an Azure SQL Server. If you did not, you need to go to the Azure Portal>More Services>SQL servers:

如果您已经阅读了文章的第一部分，则已经具有Azure SQL Server。 如果没有，则需要转到Azure门户>更多服务> SQL服务器：

You can add the Server name, login, and password. We will not explain this part on detail because it was already explained in the first part:

您可以添加服务器名称，登录名和密码。 我们将不详细解释这一部分，因为它已经在第一部分中进行了解释：

Once created the SQL server, go to properties to see the Server name:

创建SQL服务器后，请转到属性以查看服务器名称：

The SQL Server name will be used in the command line. Do not forget to create a rule to enable local IP access to azure. This is explained in the figure 11 of the first part of this article.

SQL Server名称将在命令行中使用。 不要忘记创建一个规则以启用本地IP访问Azure。 本文第一部分的图11中对此进行了解释。

Navigate to the path with the sqlpackage.exe file. In Visual Studio, it is usually stored in a path similar to this one:

使用sqlpackage.exe文件导航到路径。 在Visual Studio中，通常将其存储在与此路径相似的路径中：

C:\Program Files\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120

C：\ Program Files \ Microsoft Visual Studio 14.0 \ Common7 \ IDE \ Extensions \ Microsoft \ SQLDB \ DAC \ 120

In SQL Server, it is usually stored in a path similar to this one:

在SQL Server中，通常将其存储在与此路径相似的路径中：

C:\Program Files\Microsoft SQL Server\130\DAC\bin

C：\ Program Files \ Microsoft SQL Server \ 130 \ DAC \ bin

If you do not find the sqlpackage.exe run the dir/s sqlpackage.exe command in the cmd in your drive to search the file.

如果找不到sqlpackage.exe，请在驱动器的cmd中运行dir / s sqlpackage.exe命令以搜索文件。

In Windows Explorer, navigate to the path with sqlpackage.exe, press Shift and right click on the folder and select Open Command Prompt here:

在Windows资源管理器中，使用sqlpackage.exe导航到路径，按Shift并右键单击该文件夹，然后在此处选择“打开命令提示符”：

Run the following command in cmd:

在cmd中运行以下命令：

sqlpackage.exe /Action:Import /tsn:sqlshack.database.windows.net /tdn:worldwide /tu:daniel /tp:MySecretPwd /sf:c:\sql\WideWorldImporters-Standard.bacpac

sqlpackage.exe / Action：Import /tsn:sqlshack.database.windows.net / tdn：worldwide / tu：daniel / tp：MySecretPwd /sf:c:\sql\WideWorldImporters-Standard.bacpac

The command will import the bacpac file to Azure. The parameters used are the following:

该命令会将bacpac文件导入到Azure。 使用的参数如下：

/Action is used to indicate if we are going to import, export, publish, and extract data.

/ Action用于指示我们是否要导入，导出，发布和提取数据。

/tsn is used to define the Target Server Name. In this example, the Azure SQL Server in figure 2.

/ tsn用于定义目标服务器名称。 在此示例中，图2中的Azure SQL Server。

/tdn is the name of the new database to be created in Azure

/ tdn是要在Azure中创建的新数据库的名称

/tu is the Target User. It is the Server Admin Login in figure 1.

/ tu是目标用户。 这是图1中的服务器管理员登录名。

/tp is the Target Password. It is the password defined in figure 1.

/ tp是目标密码。 它是图1中定义的密码。

/sf is the source file downloaded in requirements.

/ sf是在需求中下载的源文件。

If everything is OK, you will be able see the new database created named wordwide:

如果一切正常，您将看到创建的名为wordwide的新数据库：

A common problem is the following error message:

常见问题是以下错误消息：

A project which specifies SQL Server 2016 as the target platform may experience compatibility issues with Microsoft Azure SQL Database v12.

将SQL Server 2016指定为目标平台的项目可能会遇到与Microsoft Azure SQL数据库v12的兼容性问题。

I could solve this problem by rewriting manually the “:”and “/” characters in the command line. It seems that the characters were not recognized in the copy paste process.

我可以通过手动重写命令行中的“：”和“ /”字符来解决此问题。 在复制粘贴过程中似乎无法识别字符。

使用PowerShell导入 (Importing with PowerShell)

Another solution is PowerShell. We can do anything with this shell.

另一个解决方案是PowerShell。 我们可以使用此shell做任何事情。

In PowerShell, we will do the following:

在PowerShell中，我们将执行以下操作：

1. Create a SQL Server in Azure
   在Azure中创建SQL Server
2. Create an Azure Storage Account.
   创建一个Azure存储帐户。
3. Create a container in the Storage Account.
   在存储帐户中创建一个容器。
4. Upload the bacpac to the container.
   将bacpac上传到容器。
5. Import the bacpac to create a Database in Azure SQL Server.
   导入bacpac以在Azure SQL Server中创建数据库。

1. Create a SQL Server in Azure

1.在Azure中创建一个SQL Server

Make sure that you have PowerShell for Azure installed.

确保已安装PowerShell for Azure。

To connect to Azure, use this cmdlet:

若要连接到Azure，请使用以下cmdlet：

Add-AzureRmAccount

Add-AzureRmAccount

It will ask your Azure Portal credentials. Specify your credentials.

它将询问您的Azure门户凭据。 指定您的凭据。

We will create a resource group. The groups are used to administer Azure resources:

我们将创建一个资源组。 这些组用于管理Azure资源：

New-AzureRmResourceGroup -Name “sqlgroup” -Location “South Central US”

New-AzureRmResourceGroup-名称“ sqlgroup”-位置“ South Central US”

Sqlgroup is the name of the group. This group is located in South Central US.

Sqlgroup是组的名称。 该小组位于美国中南部。

Here you have some possible values for Azure location:

在这里，您可以了解Azure位置的一些可能值：

1. East Asia
   东亚
2. Southeast Asia
   东南亚
3. Central US
   美国中部
4. East US
   美国东部
5. East US 2
   美国东部2
6. West US
   美国西部
7. North Central US
   美国中北部
8. South Central US
   美国中南部
9. North Europe
   北欧
10. West Europe
    西欧
11. Japan West
    日本西部
12. Japan East
    东日本
13. Brazil South
    巴西南部
14. Australia East
    东澳大利亚
15. Australia Southeast
    澳大利亚东南
16. South India
    南印度
17. West India
    西印度
18. Central India
    印度中部

To create a SQL Server in Azure using PowerShell using the following cmdlet:

使用以下cmdlet使用PowerShell在Azure中创建SQL Server：

New-AzureRmSqlServer -ResourceGroupName “sqlgroup” -Location “South Central US” -ServerName “powershellserver” -ServerVersion “12.0”

New-AzureRmSqlServer -ResourceGroupName“ sqlgroup”-位置“ South Central US” -ServerName“ powershellserver” -ServerVersion“ 12.0”

Where sqlgroup is the Resource Group created before and South Central US is the location of the SQL Server. ServerName is the name of the new server and 12. 0 is the version of SQL Azure. The cmdlets will ask for a login name and a password. Specify that information.

其中sqlgroup是之前创建的资源组，而US Central是SQL Server的位置。 ServerName是新服务器的名称，并且12。0是SQL Azure的版本。 cmdlet将要求输入登录名和密码。 指定该信息。

If everything is OK you would be able to see the SQL Server in your Azure Portal:

如果一切正常，您将可以在Azure门户中看到SQL Server：

There is a Firewall in Azure. You need to enable your local IP to access to that Azure SQL Server. The following cmdlets will create a rule to enable your IP to access to your Azure SQL Server:

Azure中有一个防火墙。 您需要启用本地IP才能访问该Azure SQL Server。 以下cmdlet将创建一个规则，以使您的IP可以访问Azure SQL Server：

New-AzureRmSqlServerFirewallRule -ResourceGroupName ‘sqlgroup’ -ServerName ‘powershellserver’ -FirewallRuleName “myFirewallRule” -StartIpAddress ‘192.168.1.1’ -EndIpAddress ‘192.168.1.1’

New-AzureRmSqlServerFirewallRule -ResourceGroupName'sqlgroup'-ServerName'powershellserver'-FirewallRuleName“ myFirewallRule” -StartIpAddress'192.168.1.1'-EndIpAddress'192.168.1.1'

Where sqlgroup is the resource group created. ServerName is the Azure SQL Server named powershellserver. FirewallRuleName is the name of the firewall, which can be any name. StartIpAddress and EndIpAddress are the range of IP allowed to access to the Azure SQL Server.

其中sqlgroup是创建的资源组。 ServerName是名为powershellserver的Azure SQL Server。 FirewallRuleName是防火墙的名称，可以是任何名称。 StartIpAddress和EndIpAddress是允许访问Azure SQL Server的IP范围。

You can see your local machine IP address here.

您可以在此处查看本地计算机的IP地址。

2. Create an Azure Storage Account

2.创建一个Azure存储帐户

To create a Storage Account we need to specify a subscription. You can have several subscriptions. That is why you need to specify your subscription for the Azure Storage Account. You can check your Subscription Name in the Azure Portal by clicking the key icon for subscriptions:

要创建存储帐户，我们需要指定一个订阅。 您可以有多个订阅。 因此，您需要为Azure存储帐户指定订阅。 您可以通过单击订阅的键图标在Azure门户中检查您的订阅名称：

In this example, the subscription name is Visual Studio Ultimate with MSDN.

在此示例中，订阅名称是带有MSDN的Visual Studio Ultimate。

You can assign the name to a variable:

您可以将名称分配给变量：

$subscription= “Visual Studio Ultimate with MSDN”

$ subscription = ““带有MSDN的Visual Studio Ultimate”

If you do not want to see in Azure Portal, you can use the Get-AzureSubscription cmdlet to retrieve the subscription name.

如果不想在Azure门户中看到，可以使用Get-AzureSubscription cmdlet检索订阅名称。

In this example, it is just one subscription. You can assign the subscription name to a variable using the following commands:

在此示例中，它只是一个订阅。 您可以使用以下命令将订阅名称分配给变量：

$subscription=(Get-AzureSubscription).SubscriptionName

$ subscription =（Get-AzureSubscription）.SubscriptionName

To set the subscription as the default subscription use the following cmdlets:

要将订阅设置为默认订阅，请使用以下cmdlet：

Select-AzureSubscription -SubscriptionName $subscription

Select-AzureSubscription -SubscriptionName $ subscription

Where $subscription is the subscription name. Set a default subscription is a requirement to create an Azure Storage Account.

其中$ subscription是订阅名称。 设置默认订阅是创建Azure存储帐户的要求。

The following cmdlet creates a StorageAccountName named sqlshackaccount in South Central USA:

以下cmdlet在美国中南部创建一个名为sqlshackaccount的StorageAccountName：

New-AzureStorageAccount -StorageAccountName “sqlshackaccount” -Location “South Central US”

New-AzureStorageAccount -StorageAccountName“ sqlshackaccount”-位置“ South Central US”

You can check the Azure Storage Account in the Azure Portal by clicking More Services>Storage accounts:

您可以通过单击更多服务>存储帐户来检查Azure门户中的Azure 存储帐户：

The sqlshackaccount storage account list will be displayed:

将显示sqlshackaccount存储帐户列表：

3. Create a container in the Storage Account

3.在存储帐户中创建一个容器

In the Azure Storage Account, we will create a Blob Container. We will upload the file bacpac file in the Blob Container. To select the Azure Storage Account just created as the default container, use the following cmdlet:

在Azure存储帐户中，我们将创建一个Blob容器。 我们将把文件bacpac文件上传到Blob容器中。 若要选择刚刚创建的Azure存储帐户作为默认容器，请使用以下cmdlet：

Set-AzureSubscription -CurrentStorageAccountName “sqlshackaccount” -SubscriptionName $subscription

Set-AzureSubscription -CurrentStorageAccountName“ sqlshackaccount” -SubscriptionName $ subscription

Now you can create your blob container named mycontainer:

现在，您可以创建名为mycontainer的Blob容器：

New-AzureStorageContainer -Name “mycontainer” -Permission Off

New-AzureStorageContainer-名称“ mycontainer”-权限关闭

Permissions off means that only the owner of the container has access to it. Permission Container allows full read access to all the blobs, but cannot enumerate containers. Permission Blob provides full read permissions to the data in blobs, but not to data in the container.

权限关闭意味着只有容器的所有者可以访问它。 权限容器允许对所有Blob进行完全读取访问，但不能枚举容器。 权限Blob提供对Blob中数据的完全读取权限，但不提供对容器中数据的完全读取权限。

4. Upload the bacpac to the container

4.将bacpac上传到容器

The following cmdlet uploads the bacpac file named WideWorldImporters-Standard to the container mycontainer:

以下cmdlet将名为WideWorldImporters-Standard的bacpac文件上载到容器mycontainer：

Set-AzureStorageBlobContent -Container “mycontainer” -File “c:\sql\WideWorldImporters-Standard.bacpac”

Set-AzureStorageBlobContent-容器“ mycontainer”-文件“ c：\ sql \ WideWorldImporters-Standard.bacpac”

You can verify with MASE that the file is uploaded:

您可以通过MASE验证文件是否已上传：

5. Import the bacpac to create a Database in Azure SQL Server

5.导入bacpac以在Azure SQL Server中创建数据库

To import from Azure SQL Server, you need to the Azure Storage Key. The Azure Account contains a key to access to it. There is a primary and secondary key. You can see the keys of the storage account named sqlshackaccount with the following cmdlets:

若要从Azure SQL Server导入，需要使用Azure存储**。 Azure帐户包含用于访问它的**。 有一个主键和辅助键。 您可以使用以下cmdlet查看名为sqlshackaccount的存储帐户的**：

Get-AzureStorageKey –StorageAccountName “sqlshackaccount”

Get-AzureStorageKey –StorageAccountName“ sqlshackaccount”

The following cmdlets store the primary Azure Storage Account in the variable $primarykey:

以下cmdlet将主Azure存储帐户存储在变量$ primarykey中：

$primarykey=(Get-AzureStorageKey -StorageAccountName “sqlshackaccount”).Primary

$ primarykey =（Get-AzureStorageKey -StorageAccountName“ sqlshackaccount”）。主要

Alternatively, you can get the primary key using MASE. Check the figure 18 of the first part of this article.

或者，您可以使用MASE获取主键。 检查本文第一部分的图18。

Another important parameter is the URI (UNIFORM RESOURCE IDENTIFIER). This is the address of the file. You can Store the URI in a variable named $StorageUri using the following cmdlets:

另一个重要参数是URI（UNIFORM RESOURCE IDENTIFIER）。 这是文件的地址。 您可以使用以下cmdlet将URI存储在名为$ StorageUri的变量中：

$StorageUri=(Get-AzureStorageBlob -blob ‘WideWorldImporters-Standard.bacpac’ -Container ‘mycontainer’).ICloudBlob.uri.AbsoluteUri

$ StorageUri =（Get-AzureStorageBlob -blob'WideWorldImporters-Standard.bacpac'-容器'mycontainer'）。ICloudBlob.uri.AbsoluteUri

Alternatively, you can get the URI using MASE. Just click the bacpac file in MASE and you will see the URI in properties.

或者，您可以使用MASE获取URI。 只需单击MASE中的bacpac文件，您将在属性中看到URI。

To store the Azure SQL credentials in a variable use the following cmdlet:

要将Azure SQL凭据存储在变量中，请使用以下cmdlet：

$credential = Get-Credential

$ credential =获取凭证

Get-Credential will ask for a login and a password. Specify your Azure SQL Server Login and Password.

Get-Credential将要求输入登录名和密码。 指定您的Azure SQL Server登录名和密码。

Finally, to import the bacpac in Azure SQL, use the following cmdlets:

最后，要在Azure SQL中导入bacpac，请使用以下cmdlet：

New-AzureRmSqlDatabaseImport –ResourceGroupName “sqlgroup” –ServerName “powershellserver” –DatabaseName “mypowershelldatabase” –StorageKeytype “StorageAccessKey” –StorageKey $primarykey -StorageUri $StorageUri –AdministratorLogin $credential.UserName –AdministratorLoginPassword $credential.Password –Edition Basic –ServiceObjectiveName B -DatabaseMaxSizeBytes 50000

New-AzureRmSqlDatabaseImport –ResourceGroupName“ sqlgroup” –ServerName“ powershellserver” –DatabaseName“ mypowershelldatabase” –StorageKeytype“ StorageAccessKey” –StorageKey $ primarykey -StorageUri $ StorageUri –AdministratorLogin $ credential.UserName –AdministratorLoginPassword $ credential.Password –Nameion数据库最大大小字节50000

Where sqlgroup is the Azure Resource Group that we have. Powershellserver is the name of our Azure SQL Server. Mypowershelldatabase is the name of the new database. You can specify any database name. We will connect using a StorageAccessKey. $primarykey is the variable that contains the access keys to access to the Storage Account. $StorageUri contains the path of the bacpac file and $credential.UserName and $credential.Password contain the Azure SQL Login and Password. Edition Basic is the type of database. Basic is the cheapest one of type B. For a complete picture about Editions, refer to the Azure SQL prices.

其中sqlgroup是我们拥有的Azure资源组。 Powershellserver是我们的Azure SQL Server的名称。 Mypowershelldatabase是新数据库的名称。 您可以指定任何数据库名称。 我们将使用StorageAccessKey进行连接。 $ primarykey是包含访问存储帐户访问**的变量。 $ StorageUri包含bacpac文件的路径，$ credential.UserName和$ credential.Password包含Azure SQL登录名和密码。 Edition Basic是数据库的类型。 Basic是类型B中最便宜的一种。有关版本的完整图片，请参考Azure SQL价格。

结论 (Conclusion)

In this article, we explained 2 alternatives to import bacpac files to Azure. One way is using sqlpackage.exe. This option is very easy and basically only requires sqlpackage.exe installed an Azure connection. If you need to import a file in a batch file, it is a good alternative.

在本文中，我们介绍了两种将bacpac文件导入Azure的替代方法。 一种方法是使用sqlpackage.exe。 此选项非常简单，基本上只需要在sqlpackage.exe中安装Azure连接即可。 如果需要将文件导入批处理文件中，这是一个很好的选择。

PowerShell requires more parameters, but it is more flexible to create custom automation tasks. PowerShell is recommended for complex automation tasks with variables, loops and a more sophisticated solution.

PowerShell需要更多参数，但是创建自定义自动化任务更加灵活。 对于具有变量，循环和更复杂解决方案的复杂自动化任务，建议使用PowerShell。

翻译自: https://www.sqlshack.com/import-sample-bacpac-file-azure-sql-database-using-sqlpackage-powershell/