还原 Azure SQL 数据仓库Restoring Azure SQL Data Warehouse

本文介绍如何在 Azure 门户和 PowerShell 中执行以下操作:In this article you will learn how to do the following in the Azure portal and PowerShell:

  • 创建还原点Create a restore point
  • 从自动还原点或用户定义的还原点进行还原Restore from an automatic restore point or user-defined restore point
  • 从已删除的数据库进行还原Restore from a deleted database
  • 从异地备份进行还原Restore from a geo-backup
  • 从用户定义的还原点创建数据仓库的副本Create a copy of your data warehouse from a user-defined restore point

准备阶段Before you begin

Note

本文进行了更新,以便使用新的 Azure PowerShell Az 模块。This article has been updated to use the new Azure PowerShell Az module. 你仍然可以使用 AzureRM 模块,至少在 2020 年 12 月之前,它将继续接收 bug 修补程序。You can still use the AzureRM module, which will continue to receive bug fixes until at least December 2020. 若要详细了解新的 Az 模块和 AzureRM 兼容性,请参阅新 Azure Powershell Az 模块简介To learn more about the new Az module and AzureRM compatibility, see Introducing the new Azure PowerShell Az module. 有关 Az 模块安装说明,请参阅安装 Azure PowerShellFor Az module installation instructions, see Install Azure PowerShell.

验证 DTU 容量。Verify your DTU capacity. 每个 SQL 数据仓库都由一个具有默认 DTU 配额的 SQL 服务器(例如 myserver.database.chinacloudapi.cn)托管。Each SQL Data Warehouse is hosted by a SQL server (e.g. myserver.database.chinacloudapi.cn) which has a default DTU quota. 在还原 SQL 数据仓库之前,请确保 SQL Server 的剩余 DTU 配额足够进行数据库还原。Before you can restore a SQL data warehouse, verify that the your SQL server has enough remaining DTU quota for the database being restored. 若要了解如何计算所需 DTU 或请求更多的 DTU,请参阅 请求 DTU 配额更改To learn how to calculate DTU needed or to request more DTU, see Request a DTU quota change.

通过 Powershell 进行还原Restore through PowerShell

安装 PowerShellInstall PowerShell

若要对 SQL 数据仓库使用 Azure PowerShell,需要安装 Azure PowerShell。In order to use Azure PowerShell with SQL Data Warehouse, you will need to install Azure PowerShell. 可以通过运行 Get-Module -ListAvailable -Name Az 来检查版本。You can check your version by running Get-Module -ListAvailable -Name Az. 有关安装最新版本的详细信息,请参阅如何安装和配置 Azure PowerShellFor more information on installing the latest version, see How to install and configure Azure PowerShell.

使用 PowerShell 还原活动或暂停的数据库Restore an active or paused database using PowerShell

若要从还原点还原数据库,请使用 Restore-AzSqlDatabase PowerShell cmdlet。To restore a database from a restore point use the Restore-AzSqlDatabase PowerShell cmdlet.

  1. 打开 Windows PowerShell。Open Windows PowerShell.

  2. 连接到 Azure 帐户,并列出与帐户关联的所有订阅。Connect to your Azure account and list all the subscriptions associated with your account.

  3. 选择包含要还原的数据库的订阅。Select the subscription that contains the database to be restored.

  4. 列出数据库的还原点。List the restore points for the database.

  5. 使用 RestorePointCreationDate 选取所需的还原点。Pick the desired restore point using the RestorePointCreationDate.

    Note

    在还原时,可以指定不同的 ServiceObjectiveName (DWU) 或位于另一区域中的不同服务器。When restoring, you can specify a different ServiceObjectiveName (DWU) or a different server residing in a different region.

  6. 将数据库还原到所需的还原点。Restore the database to the desired restore point.

  7. 验证已还原的数据库是否处于联机状态。Verify that the restored database is online.


$SubscriptionName="<YourSubscriptionName>"
$ResourceGroupName="<YourResourceGroupName>"
$ServerName="<YourServerNameWithoutURLSuffixSeeNote>"  # Without database.chinacloudapi.cn
$DatabaseName="<YourDatabaseName>"
$NewDatabaseName="<YourDatabaseName>"

Connect-AzAccount -Environment AzureChinaCloud
Get-AzSubscription
Select-AzSubscription -SubscriptionName $SubscriptionName

# List the last 10 database restore points
((Get-AzSqlDatabaseRestorePoints -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName ($DatabaseName)).RestorePointCreationDate)[-10 .. -1]

# Or list all restore points
Get-AzSqlDatabaseRestorePoints -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName $DatabaseName

# Get the specific database to restore
$Database = Get-AzSqlDatabase -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName $DatabaseName

# Pick desired restore point using RestorePointCreationDate
$PointInTime="<RestorePointCreationDate>"  

# Restore database from a restore point
$RestoredDatabase = Restore-AzSqlDatabase –FromPointInTimeBackup –PointInTime $PointInTime -ResourceGroupName $Database.ResourceGroupName -ServerName $Database.ServerName -TargetDatabaseName $NewDatabaseName –ResourceId $Database.ResourceID

# Verify the status of restored database
$RestoredDatabase.status

Note

完成还原后,即可按恢复后配置数据库中的说明配置恢复的数据库。After the restore has completed, you can configure your recovered database by following Configure your database after recovery.

使用 PowerShell 通过用户定义的还原点复制数据仓库Copy your data warehouse with user-defined restore points using PowerShell

若要从用户定义的还原点还原数据库,请使用 Restore-AzSqlDatabase PowerShell cmdlet。To restore a database from a user-defined restore point use the Restore-AzSqlDatabase PowerShell cmdlet.

  1. 打开 Windows PowerShell。Open Windows PowerShell.
  2. 连接到 Azure 帐户,并列出与帐户关联的所有订阅。Connect to your Azure account and list all the subscriptions associated with your account.
  3. 选择包含要还原的数据库的订阅。Select the subscription that contains the database to be restored.
  4. 为数据库的即时复制创建还原点Create a restore point for an immediate copy of your database
  5. 将数据库重命名为一个临时名称。Rename your database to a temporary name.
  6. 根据指定的 RestorePointLabel 检索最新还原点。Retrieve the most recent restore point by the specified RestorePointLabel.
  7. 获取用于启动还原的数据库的资源 IDGet the resource id of the database to initiate the restore
  8. 将数据库还原到所需的还原点。Restore the database to the desired restore point.
  9. 验证已还原的数据库是否处于联机状态。Verify that the restored database is online.

$SubscriptionName="<YourSubscriptionName>"
$ResourceGroupName="<YourResourceGroupName>"
$ServerName="<YourServerNameWithoutURLSuffixSeeNote>"  # Without database.chinacloudapi.cn
$DatabaseName="<YourDatabaseName>"
$TempDatabaseName = "<YourTemporaryDatabaseName>"
$Label = "<YourRestorePointLabel"

Connect-AzAccount -Environment AzureChinaCloud
Get-AzSubscription
Select-AzSubscription -SubscriptionName $SubscriptionName

# Create a restore point of the original database
New-AzSqlDatabaseRestorePoint -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName $DatabaseName -RestorePointLabel $Label

# Rename the database to a temporary name
Set-AzSqlDatabase -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName $DatabaseName -NewName $TempDatabaseName

# Get the most recent restore point with the specified label
$LabelledRestorePoint = Get-AzSqlDatabaseRestorePoints -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName $TempDatabaseName | where {$_.RestorePointLabel -eq $Label} | sort {$_.RestorePointCreationDate} | select -Last 1

# Get the resource id of the database
$ResourceId = (Get-AzSqlDatabase -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName $TempDatabaseName).ResourceId

# Restore the database to its original name from the labelled restore point from the temporary database
$RestoredDatabase = Restore-AzSqlDatabase -FromPointInTimeBackup -ResourceGroupName $ResourceGroupName -ServerName $ServerName -ResourceId $ResourceId -PointInTime $LabelledRestorePoint.RestorePointCreationDate -TargetDatabaseName $DatabaseName

# Verify the status of restored database
$RestoredDatabase.status

# The original temporary database can be deleted at this point

使用 PowerShell 还原删除的数据库Restore a deleted database using PowerShell

若要还原已删除的数据库,请使用 Restore-AzSqlDatabase cmdlet。To restore a deleted database, use the Restore-AzSqlDatabase cmdlet.

  1. 打开 Windows PowerShell。Open Windows PowerShell.
  2. 连接到 Azure 帐户,并列出与帐户关联的所有订阅。Connect to your Azure account and list all the subscriptions associated with your account.
  3. 选择包含要还原的已删除数据库的订阅。Select the subscription that contains the deleted database to be restored.
  4. 获取特定的已删除数据库。Get the specific deleted database.
  5. 还原已删除的数据库。Restore the deleted database.
  6. 验证已还原的数据库是否处于联机状态。Verify that the restored database is online.
$SubscriptionName="<YourSubscriptionName>"
$ResourceGroupName="<YourResourceGroupName>"
$ServerName="<YourServerNameWithoutURLSuffixSeeNote>"  # Without database.chinacloudapi.cn
$DatabaseName="<YourDatabaseName>"
$NewDatabaseName="<YourDatabaseName>"

Connect-AzAccount -Environment AzureChinaCloud
Get-AzSubscription
Select-AzSubscription -SubscriptionName $SubscriptionName

# Get the deleted database to restore
$DeletedDatabase = Get-AzSqlDeletedDatabaseBackup -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName $DatabaseName

# Restore deleted database
$RestoredDatabase = Restore-AzSqlDatabase –FromDeletedDatabaseBackup –DeletionDate $DeletedDatabase.DeletionDate -ResourceGroupName $DeletedDatabase.ResourceGroupName -ServerName $DeletedDatabase.ServerName -TargetDatabaseName $NewDatabaseName –ResourceId $DeletedDatabase.ResourceID

# Verify the status of restored database
$RestoredDatabase.status

Note

完成还原后,即可按恢复后配置数据库中的说明配置恢复的数据库。After the restore has completed, you can configure your recovered database by following Configure your database after recovery.

使用 PowerShell 从 Azure 地理区域还原Restore from an Azure geographical region using PowerShell

若要恢复数据库,请使用 Restore-AzSqlDatabase cmdlet。To recover a database, use the Restore-AzSqlDatabase cmdlet.

Note

可以执行到第 2 代的异地还原!You can perform a geo-restore to Gen2! 若要执行此操作,请将一个第 2 代 ServiceObjectiveName(例如 DW1000c)指定为可选参数。To do so, specify an Gen2 ServiceObjectiveName (e.g. DW1000c) as an optional parameter.

  1. 打开 Windows PowerShell。Open Windows PowerShell.
  2. 连接到 Azure 帐户,并列出与帐户关联的所有订阅。Connect to your Azure account and list all the subscriptions associated with your account.
  3. 选择包含要还原的数据库的订阅。Select the subscription that contains the database to be restored.
  4. 获取要恢复的数据库。Get the database you want to recover.
  5. 创建对数据库的恢复请求。Create the recovery request for the database.
  6. 验证异地还原的数据库的状态。Verify the status of the geo-restored database.
Connect-AzAccount -Environment AzureChinaCloud
Get-AzSubscription
Select-AzSubscription -SubscriptionName "<Subscription_name>"

# Get the database you want to recover
$GeoBackup = Get-AzSqlDatabaseGeoBackup -ResourceGroupName "<YourResourceGroupName>" -ServerName "<YourServerName>" -DatabaseName "<YourDatabaseName>"

# Recover database
$GeoRestoredDatabase = Restore-AzSqlDatabase –FromGeoBackup -ResourceGroupName "<YourResourceGroupName>" -ServerName "<YourTargetServer>" -TargetDatabaseName "<NewDatabaseName>" –ResourceId $GeoBackup.ResourceID -ServiceObjectiveName "<YourTargetServiceLevel>"

# Verify that the geo-restored database is online
$GeoRestoredDatabase.status

Note

若要在完成还原后配置数据库,请参阅在恢复后配置数据库To configure your database after the restore has completed, see Configure your database after recovery.

如果源数据库启用了 TDE,则已恢复的数据库会启用 TDE。The recovered database will be TDE-enabled if the source database is TDE-enabled.

通过 Azure 门户进行还原Restore through the Azure portal

使用 Azure 门户创建用户定义的还原点Create a user-defined restore point using the Azure portal

  1. 登录到 Azure 门户Sign in to the Azure portal.

  2. 导航到要为其创建还原点的 SQL 数据仓库。Navigate to the SQL data warehouse that you want to create a restore point for.

  3. 在“概览”边栏选项卡顶部,选择“+ 新建还原点”。At the top of the Overview blade, select +New Restore Point.

    新建还原点

  4. 为还原点指定一个名称。Specify a name for your restore point.

    还原点的名称

使用 Azure 门户还原活动或暂停的数据库Restore an active or paused database using the Azure portal

  1. 登录到 Azure 门户Sign in to the Azure portal.

  2. 导航到要从中进行还原的 SQL 数据仓库。Navigate to the SQL data warehouse that you want to restore from.

  3. 在“概览”边栏选项卡顶部,选择“还原”。At the top of the Overview blade, select Restore.

     还原概述Restore Overview

  4. 选择“自动还原点”或“用户定义的还原点”。Select either Automatic Restore Points or User-Defined Restore Points.

    自动还原点

  5. 对于用户定义的还原点,请选择还原点新建用户定义的还原点For User-Defined Restore Points, select a Restore point or Create a new user-defined restore point.

    用户定义的还原点

通过 Azure 门户还原已删除的数据库Restore a deleted database using the Azure portal

  1. 登录到 Azure 门户Sign in to the Azure portal.

  2. 导航到承载着已删除数据库的 SQL Server。Navigate to the SQL server your deleted database was hosted on.

  3. 在目录中选择“已删除的数据库”图标。Select the Deleted databases icon in the table of contents.

    已删除的数据库

  4. 选择要还原的已删除数据库。Select the deleted database that you want to restore.

    选择“已删除的数据库”

  5. 指定一个新的数据库名称。Specify a new database name.

    指定数据库名称