使用 PowerShell 备份和还原 Azure VM 中的 SQL 数据库Back up and restore SQL databases in Azure VMs with PowerShell

本文介绍如何在 Azure PowerShell 中使用 Azure 备份恢复服务保管库来备份和恢复 Azure VM 中的 SQL 数据库。This article describes how to use Azure PowerShell to back up and recover a SQL DB within an Azure VM using Azure Backup Recovery Services vault.

本文介绍如何执行以下操作:This article explains how to:

  • 设置 PowerShell 并注册 Azure 恢复服务提供程序。Set up PowerShell and register the Azure Recovery Services Provider.
  • 创建恢复服务保管库。Create a Recovery Services vault.
  • 为 Azure VM 中的 SQL 数据库配置备份。Configure backup for SQL DB within an Azure VM.
  • 运行备份作业。Run a backup job.
  • 还原已备份的 SQL 数据库。Restore a backed up SQL DB.
  • 监视备份和还原作业。Monitor backup and restore jobs.

开始之前Before you start

恢复服务对象层次结构Recovery Services object hierarchy

下图汇总了对象层次结构。The object hierarchy is summarized in the following diagram.

恢复服务对象层次结构

查看 Azure 库中的 Az.RecoveryServices cmdlet 参考Review the Az.RecoveryServices cmdlet reference reference in the Azure library.

设置和安装Set up and install

按如下所述设置 PowerShell:Set up PowerShell as follows:

  1. 下载最新版本的 Azure PowerShellDownload the latest version of Az PowerShell. 所需的最低版本为 1.5.0。The minimum version required is 1.5.0.

  2. 找到包含以下命令的 Azure 备份 PowerShell cmdlet:Find the Azure Backup PowerShell cmdlets with this command:

    Get-Command *azrecoveryservices*
    
  3. 查看 Azure 备份和恢复服务保管库的别名与 cmdlet。Review the aliases and cmdlets for Azure Backup and the Recovery Services vault. 下面是可能显示的内容示例。Here's an example of what you might see. 此 cmdlet 列表并不完整。It's not a complete list of cmdlets.

    恢复服务 cmdlet 列表

  4. 使用 Connect-AzAccount 登录到 Azure 帐户。Sign in to your Azure account with Connect-AzAccount.

  5. 在出现的网页上,系统会提示你输入帐户凭据。On the web page that appears, you're prompted to input your account credentials.

    • 或者,可以结合 -CredentialConnect-AzAccount cmdlet 中将帐户凭据包含为参数。Alternately, you can include your account credentials as a parameter in the Connect-AzAccount cmdlet with -Credential.
    • 如果你是为某个租户工作的 CSP 合作伙伴,则需使用 tenantID 或租户主域名将客户指定为一名租户。If you're a CSP partner working for a tenant, specify the customer as a tenant, using their tenantID or tenant primary domain name. 例如,Connect-AzAccount -Tenant fabrikam.com。An example is Connect-AzAccount -Tenant fabrikam.com.
  6. 由于一个帐户可以有多个订阅,因此请将要使用的订阅与帐户关联在一起。Associate the subscription you want to use with the account, because an account can have several subscriptions.

    Select-AzSubscription -SubscriptionName $SubscriptionName
    
  7. 首次使用 Azure 备份时,请使用 Register-AzResourceProvider cmdlet 将 Azure 恢复服务提供程序注册到订阅。If you're using Azure Backup for the first time, use the Register-AzResourceProvider cmdlet to register the Azure Recovery Services provider with your subscription.

    Register-AzResourceProvider -ProviderNamespace "Microsoft.RecoveryServices"
    
  8. 验证提供程序是否已成功注册:Verify that the providers registered successfully:

    Get-AzResourceProvider -ProviderNamespace "Microsoft.RecoveryServices"
    
  9. 在命令输出中,确认 RegistrationState 是否更改为 RegisteredIn the command output, verify that RegistrationState changes to Registered. 如果不是,请再次运行 Register-AzResourceProvider cmdlet。If it doesn't, run the Register-AzResourceProvider cmdlet again.

创建恢复服务保管库Create a Recovery Services vault

请按照以下步骤创建恢复服务保管库。Follow these steps to create a Recovery Services vault.

恢复服务保管库是一种资源管理器资源,因此必须将其放在资源组中。The Recovery Services vault is a Resource Manager resource, so you must place it within a resource group. 可以使用现有资源组,也可以使用 New-AzResourceGroup cmdlet 创建资源组。You can use an existing resource group, or you can create a resource group with the New-AzResourceGroup cmdlet. 创建资源组时,请指定资源组的名称和位置。When you create a resource group, specify the name and location for the resource group.

  1. 保管库放在资源组中。A vault is placed in a resource group. 如果没有现有的资源组,请使用 New-AzResourceGroup 新建一个。If you don't have an existing resource group, create a new one with the New-AzResourceGroup. 此示例在“中国东部”区域创建一个新的资源组。In this example, we create a new resource group in the China East region.

    New-AzResourceGroup -Name "test-rg" -Location "China East"
    
  2. 使用 New-AzRecoveryServicesVault cmdlet 创建保管库。Use the New-AzRecoveryServicesVault cmdlet to create the vault. 请为保管库指定与资源组相同的位置。Specify the same location for the vault as was used for the resource group.

    New-AzRecoveryServicesVault -Name "testvault" -ResourceGroupName "test-rg" -Location "China East"
    
  3. 指定保管库存储使用的冗余类型。Specify the type of redundancy to use for the vault storage.

    $vault1 = Get-AzRecoveryServicesVault -Name "testvault"
    Set-AzRecoveryServicesBackupProperties  -Vault $vault1 -BackupStorageRedundancy GeoRedundant
    

在订阅中查看保管库View the vaults in a subscription

若要查看订阅中的所有保管库,请使用 Get-AzRecoveryServicesVaultTo view all vaults in the subscription, use Get-AzRecoveryServicesVault.

Get-AzRecoveryServicesVault

输出如下所示。The output is similar to the following. 其中已提供资源组名称和位置。The associated resource group and location are provided.

Name              : Contoso-vault
ID                : /subscriptions/1234
Type              : Microsoft.RecoveryServices/vaults
Location          : ChinaNorth
ResourceGroupName : Contoso-docs-rg
SubscriptionId    : 1234-567f-8910-abc
Properties        : Microsoft.Azure.Commands.RecoveryServices.ARSVaultProperties

设置保管库上下文Set the vault context

将保管库对象存储在变量中,并设置保管库上下文。Store the vault object in a variable, and set the vault context.

  • 许多 Azure 备份 cmdlet 要求将恢复服务保管库对象用作输入,因此可以方便地在变量中存储保管库对象。Many Azure Backup cmdlets require the Recovery Services vault object as an input, so it's convenient to store the vault object in a variable.
  • 保管库上下文是在保管库中受保护的数据的类型。The vault context is the type of data protected in the vault. 可以使用 Set-AzRecoveryServicesVaultContext 设置它。Set it with Set-AzRecoveryServicesVaultContext. 设置上下文后,它将应用于所有后续 cmdlet。After the context is set, it applies to all subsequent cmdlets.

以下示例为 testvault 设置保管库上下文。The following example sets the vault context for testvault.

Get-AzRecoveryServicesVault -Name "testvault" | Set-AzRecoveryServicesVaultContext

提取保管库 IDFetch the vault ID

我们已计划根据 Azure PowerShell 指导原则弃用保管库上下文设置。We plan on deprecating the vault context setting in accordance with Azure PowerShell guidelines. 你可以改为存储或提取保管库 ID,并将其传递给相关命令,如下所示:Instead, you can store or fetch the vault ID, and pass it to relevant commands, as follows:

$vaultID = Get-AzRecoveryServicesVault -ResourceGroupName "Contoso-docs-rg" -Name "testvault" | select -ExpandProperty ID

配置备份策略Configure a backup policy

备份策略指定备份计划,以及备份恢复点的保留期限:A backup policy specifies the schedule for backups, and how long backup recovery points should be kept:

默认情况下,会在“计划策略对象”中定义开始时间。By default, a start time is defined in the Schedule Policy Object. 请使用以下示例将开始时间更改为所需的开始时间。Use the following example to change the start time to the desired start time. 所需的开始时间也应采用 UTC 格式。The desired start time should be in UTC as well. 以下示例假设在进行每日备份时,所需的开始时间为 UTC 时间凌晨 1:00。The below example assumes the desired start time is 01:00 AM UTC for daily backups.

$schPol = Get-AzRecoveryServicesBackupSchedulePolicyObject -WorkloadType "MSSQL"
$UtcTime = Get-Date -Date "2019-03-20 01:30:00Z"
$UtcTime = $UtcTime.ToUniversalTime()
$schpol.ScheduleRunTimes[0] = $UtcTime

重要

只需以 30 分钟的倍数提供开始时间。You need to provide the start time in 30 minute multiples only. 在上面的示例中,开始时间只能是“01:00:00”或“02:30:00”。In the above example, it can be only "01:00:00" or "02:30:00". 开始时间不能为“01:15:00”The start time cannot be "01:15:00"

以下示例将计划策略和保留策略存储在变量中。The following example stores the schedule policy and the retention policy in variables. 然后,它使用这些变量作为新策略 (NewSQLPolicy) 的参数。It then uses those variables as parameters for a new policy (NewSQLPolicy). NewSQLPolicy 创建每日“完整”备份,将备份保留 180 天,并每隔 2 小时创建日志备份NewSQLPolicy takes a daily "Full" backup, retains it for 180 days and takes a log backup every 2 hours

$schPol = Get-AzRecoveryServicesBackupSchedulePolicyObject -WorkloadType "MSSQL"
$retPol = Get-AzRecoveryServicesBackupRetentionPolicyObject -WorkloadType "MSSQL"
$NewSQLPolicy = New-AzRecoveryServicesBackupProtectionPolicy -Name "NewSQLPolicy" -WorkloadType "MSSQL" -RetentionPolicy $retPol -SchedulePolicy $schPol

输出如下所示。The output is similar to the following.

Name                 WorkloadType       BackupManagementType BackupTime                Frequency                                IsDifferentialBackup IsLogBackupEnabled
                                                                                                                                Enabled
----                 ------------       -------------------- ----------                ---------                                -------------------- ------------------
NewSQLPolicy         MSSQL              AzureWorkload        3/15/2019 01:30:00 AM      Daily                                    False                True

启用备份Enable backup

注册 SQL VMRegistering the SQL VM

对于 Azure VM 备份和 Azure 文件共享,备份服务可以连接到这些 Azure 资源管理器资源并提取相关的详细信息。For Azure VM backups and Azure File shares, Backup service can connect to these Azure Resource Manager resources and fetch the relevant details. 由于 SQL 是 Azure VM 内部的一个应用程序,因此备份服务需要有权访问该应用程序并提取所需的详细信息。Since SQL is an application within an Azure VM, Backup service needs permission to access the application and fetch the necessary details. 为此,需要将包含 SQL 应用程序的 Azure VM“注册”到恢复服务保管库。In order to do that, you need to 'register' the Azure VM that contains the SQL application with a Recovery services vault. 将 SQL VM 注册到保管库后,可以仅在该保管库中保护 SQL 数据库。Once you register a SQL VM with a vault, you can protect the SQL DBs to that vault only. 使用 Register-AzRecoveryServicesBackupContainer PS cmdlet 注册 VM。Use Register-AzRecoveryServicesBackupContainer PS cmdlet to register the VM.

 $myVM = Get-AzVM -ResourceGroupName <VMRG Name> -Name <VMName>
Register-AzRecoveryServicesBackupContainer -ResourceId $myVM.ID -BackupManagementType AzureWorkload -WorkloadType MSSQL -VaultId $targetVault.ID -Force

此命令将返回此资源的“备份容器”,状态为“已注册”The command will return a 'backup container' of this resource and the status will be 'registered'

备注

如果未指定 force 参数,系统会显示文本“是否要对此容器禁用保护”并要求用户确认。If the force parameter is not given, user is asked to confirm with a text 'Do you want to disable protection for this container'. 请忽略此文本,并输入“Y”以确认。Please ignore this text and say "Y" to confirm. 这是一个已知的问题,我们正在努力删除该文本,并取消对 force 参数的要求。This is a known issue and we are working to remove the text and the requirement for the force parameter.

提取 SQL 数据库Fetching SQL DBs

完成注册后,备份服务可以列出 VM 中所有可用的 SQL 组件。Once the registration is done, Backup service will be able to list all the available SQL components within the VM. 若要查看需要备份到此保管库的所有 SQL 组件,请使用 Get-AzRecoveryServicesBackupProtectableItem PS cmdletTo view all the SQL components yet to be backed up to this vault use Get-AzRecoveryServicesBackupProtectableItem PS cmdlet

Get-AzRecoveryServicesBackupProtectableItem -WorkloadType MSSQL -VaultId $targetVault.ID

此输出将显示所有已注册到此保管库的 SQL VM 中所有不受保护的 SQL 组件以及 ItemType 和 ServerName。The output will show all unprotected SQL components across all SQL VMs registered to this vault with Item Type and ServerName. 可以通过传递“-Container”参数来进一步筛选特定的 SQL VM,或者结合 ItemType 标志使用“Name”和“ServerName”的组合来查看唯一的 SQL 项。You can further filter to a particular SQL VM by passing the '-Container' parameter or use the combination of 'Name' and 'ServerName' along with ItemType flag to arrive at a unique SQL item.

$SQLDB = Get-AzRecoveryServicesBackupProtectableItem -workloadType MSSQL -ItemType SQLDataBase -VaultId $targetVault.ID -Name "<Item Name>" -ServerName "<Server Name>"

配置备份Configuring backup

提取所需的 SQL 数据库并创建用于备份的策略后,接下来可以使用 Enable-AzRecoveryServicesBackupProtection cmdlet 来为此 SQL 数据库配置备份。Now that we have the required SQL DB and the policy with which it needs to be backed up, we can use the Enable-AzRecoveryServicesBackupProtection cmdlet to configure backup for this SQL DB.

Enable-AzRecoveryServicesBackupProtection -ProtectableItem $SQLDB -Policy $NewSQLPolicy

该命令将一直等到配置备份完成并返回以下输出。The command waits until the configure backup is completed and returns the following output.

WorkloadName     Operation            Status               StartTime                 EndTime                   JobID
------------     ---------            ------               ---------                 -------                   -----
master           ConfigureBackup      Completed            3/18/2019 6:00:21 PM      3/18/2019 6:01:35 PM      654e8aa2-4096-402b-b5a9-e5e71a496c4e

提取新的 SQL 数据库Fetching new SQL DBs

注册计算机后,备份服务将提取可用数据库的详细信息。Once the machine is registered, Backup service will fetch the details of the DBs available then. 如果用户以后将 SQL 数据库/SQL 实例添加到已注册的计算机,则需要手动触发备份服务,才能执行新的“查询”来再次获取所有不受保护的数据库(包括新添加的数据库)。If user adds SQL DBs/SQL instances to the registered machine later, one needs to manually trigger the backup service to perform a fresh 'inquiry' to get ALL the unprotected DBs (including the newly added ones) again. 在 SQL VM 上使用 Initialize-AzRecoveryServicesBackupItem PS cmdlet 执行新的查询。Use the Initialize-AzRecoveryServicesBackupItem PS cmdlet on the SQL VM to perform a fresh inquiry. 该命令会等到操作完成为止。The command waits until the operation is completed. 稍后使用 Get-AzRecoveryServicesBackupProtectableItem PS cmdlet 获取不受保护的最新 SQL 组件列表Later use the Get-AzRecoveryServicesBackupProtectableItem PS cmdlet to get the list of latest unprotected SQL components

$SQLContainer = Get-AzRecoveryServicesBackupContainer -ContainerType AzureVMAppContainer -FriendlyName <VM name> -VaultId $targetvault.ID
Initialize-AzRecoveryServicesBackupProtectableItem -Container $SQLContainer -WorkloadType MSSQL -VaultId $targetvault.ID
Get-AzRecoveryServicesBackupProtectableItem -workloadType MSSQL -ItemType SQLDataBase -VaultId $targetVault.ID

获取相关的可保护项后,根据上一部分中的说明启用备份。Once the relevant protectable items are fetched, enable the backups as instructed in the above section. 如果用户不想要手动检测新数据库,可以根据下面所述启用自动保护。If one doesn't want to manually detect new DBs, they can opt for autoprotection as explained below.

启用自动保护Enable autoprotection

用户可以配置备份,以使用特定的策略自动保护将来添加的所有数据库。A user can configure backup such that all DBs added in the future are automatically protected with a certain policy. 若要启用自动保护,请使用 Enable-AzRecoveryServicesBackupAutoProtection PS cmdlet。To enable autoprotection, use Enable-AzRecoveryServicesBackupAutoProtection PS cmdlet.

由于说明中的操作是备份将来的所有数据库,因此该操作将在 SQLInstance 级别进行。Since the instruction is to back up all future DBs, the operation is done at a SQLInstance level.

$SQLInstance = Get-AzRecoveryServicesBackupProtectableItem -workloadType MSSQL -ItemType SQLInstance -VaultId $targetVault.ID -Name "<Protectable Item name>" -ServerName "<Server Name>"
Enable-AzRecoveryServicesBackupAutoProtection -InputItem $SQLInstance -BackupManagementType AzureWorkload -WorkloadType MSSQL -Policy $NewSQLPolicy -VaultId $targetvault.ID

指明自动保护意向后,在计算机中提取新添加的数据库的查询将按计划的后台任务每隔 8 小时进行。Once the autoprotection intent is given, the inquiry into the machine to fetch newly added DBs takes place as a scheduled background task every 8 hours.

还原 SQL 数据库Restore SQL DBs

Azure 备份可以还原 Azure VM 上运行的 SQL Server 数据库,如下所述:Azure Backup can restore SQL Server databases that are running on Azure VMs as follows:

  • 使用事务日志备份还原到特定的日期或时间(精确到秒)。Restore to a specific date or time (to the second) by using transaction log backups. Azure 备份可自动确定相应的完整备份、差异备份和日志链备份,这些是根据所选时间进行还原所必需的。Azure Backup automatically determines the appropriate full differential backup and the chain of log backups that are required to restore based on the selected time.
  • 还原特定的完整备份或差异备份,这样就可以还原到特定的恢复点。Restore a specific full or differential backup to restore to a specific recovery point.

在还原 SQL 数据库之前,请查看此处所述的先决条件。Check the prerequisites mentioned here before restoring SQL DBs.

首先使用 Get-AzRecoveryServicesBackupItem PS cmdlet 提取相关的已备份 SQL 数据库。First fetch the relevant backed up SQL DB using the Get-AzRecoveryServicesBackupItem PS cmdlet.

$bkpItem = Get-AzRecoveryServicesBackupItem -BackupManagementType AzureWorkload -WorkloadType MSSQL -Name "<backup item name>" -VaultId $targetVault.ID

提取相关的还原时间Fetch the relevant restore time

如上所述,用户可将备份的 SQL 数据库还原到完整/差异副本,还原到某个日志时间点。As outlined above, user can restore the backed-up SQL DB to a full/differential copy OR to a log point-in-time.

提取不同的恢复点Fetch distinct recovery points

使用 Get-AzRecoveryServicesBackupRecoveryPoint 提取已备份的 SQL 数据库的不同(完整/差异)恢复点。Use Get-AzRecoveryServicesBackupRecoveryPoint to fetch distinct (Full/differential) recovery points for a backed-up SQL DB.

$startDate = (Get-Date).AddDays(-7).ToUniversalTime()
$endDate = (Get-Date).ToUniversalTime()
Get-AzRecoveryServicesBackupRecoveryPoint -Item $bkpItem -VaultId $targetVault.ID -StartDate $startdate -EndDate $endDate

输出类似于以下示例The output is similar to the following example

RecoveryPointId    RecoveryPointType  RecoveryPointTime      ItemName                             BackupManagemen
                                                                                                  tType
---------------    -----------------  -----------------      --------                             ---------------
6660368097802      Full               3/18/2019 8:09:35 PM   MSSQLSERVER;model             AzureWorkload

使用“RecoveryPointId”筛选器或数组筛选器提取相关的恢复点。Use the 'RecoveryPointId' filter or an array filter to fetch the relevant recovery point.

$FullRP = Get-AzRecoveryServicesBackupRecoveryPoint -Item $bkpItem -VaultId $targetVault.ID -RecoveryPointId "6660368097802"

提取时点恢复点Fetch point-in-time recovery point

如果用户想要将数据库还原到特定的时间点,可使用 Get-AzRecoveryServicesBackupRecoveryLogChain PS cmdlet。If the user wants to restore the DB to a certain point-in-time, use Get-AzRecoveryServicesBackupRecoveryLogChain PS cmdlet. 该 cmdlet 返回一个日期列表,这些日期表示该 SQL 备份项的已中断连续日志链的开始时间和结束时间。The cmdlet returns a list of dates that represent start and end times of an unbroken, continuous log chain for that SQL backup item. 所需的时间点应在此范围内。The desired point-in-time should be within this range.

Get-AzRecoveryServicesBackupRecoveryLogChain -Item $bkpItem -Item -VaultId $targetVault.ID

输出类似于以下示例。The output will be similar to the following example.

ItemName                       StartTime                      EndTime
--------                       ---------                      -------
SQLDataBase;MSSQLSERVER;azu... 3/18/2019 8:09:35 PM           3/19/2019 12:08:32 PM

以上输出表示用户可以还原到显示的开始时间与结束时间之间的任意时间点。The above output means that user can restore to any point-in-time between the displayed start time and end time. 这些时间为 UTC 时间。The times are in UTC. 在 PS 中构造处于上面所示范围内的任意时间点。Construct any point-in-time in PS that is within the range shown above.

备注

选择要还原到的日志时间点后,用户不需要指定起点(即,要从中还原数据库的完整备份)。When a log point-in-time selected for restore, user need not specify the starting point i.e., full backup from which the DB is restored. Azure 备份服务将处理整个恢复计划,即,选择所需的完整备份、应用所需的日志备份,等等。Azure Backup service will take care of the entire recovery plan i.e., which full backup to choose, what log backups to apply etc.

确定恢复配置Determine recovery configuration

SQL 数据库还原支持以下还原方案。In case of SQL DB restore, the following restore scenarios are supported.

  • 使用其他恢复点中的数据替代已备份的 SQL 数据库 - OriginalWorkloadRestoreOverriding the backed-up SQL DB with data from another recovery point - OriginalWorkloadRestore
  • 将 SQL 数据库还原为同一 SQL 实例中的新数据库 - AlternateWorkloadRestoreRestoring the SQL DB as a new DB in the same SQL instance - AlternateWorkloadRestore
  • 将 SQL 数据库还原为另一 SQL VM 上另一 SQL 实例中的新数据库 - AlternateWorkloadRestoreRestoring the SQL DB as a new DB in another SQL instance in another SQL VM - AlternateWorkloadRestore
  • 将 SQL 数据库还原为 .bak 文件 -RestoreAsFilesRestoring the SQL DB as .bak files -RestoreAsFiles

提取相关的恢复点(不同的时间点或日志时间点)后,使用 Get-AzRecoveryServicesBackupWorkloadRecoveryConfig PS cmdlet 根据所需的恢复计划提取恢复配置对象。After fetching the relevant recovery point (distinct or log point-in-time), use Get-AzRecoveryServicesBackupWorkloadRecoveryConfig PS cmdlet to fetch the recovery config object according to the desired recovery plan.

原始工作负荷还原Original workload restore

若要使用恢复点中的数据替代已备份的数据库,只需指定适当的标志和相关恢复点,如以下示例中所示。To override the backed-up DB with data from the recovery point, just specify the right flag and the relevant recovery point as shown in the following example(s).

使用不同恢复点的原始工作负荷还原Original restore with distinct Recovery point
$OverwriteWithFullConfig = Get-AzRecoveryServicesBackupWorkloadRecoveryConfig -RecoveryPoint $FullRP -OriginalWorkloadRestore -VaultId $targetVault.ID
使用日志时间点的原始工作负荷还原Original restore with log point-in-time
$OverwriteWithLogConfig = Get-AzRecoveryServicesBackupWorkloadRecoveryConfig -PointInTime $PointInTime -Item $bkpItem  -OriginalWorkloadRestore -VaultId $targetVault.ID

备用工作负荷还原Alternate workload restore

重要

备份的 SQL 数据库只能作为新数据库还原到已在此保管库中“注册”的 Azure VM 上的另一个 SQLInstance 中。A backed up SQL DB can be restored as a new DB to another SQLInstance only, in a Azure VM 'registered' to this vault.

如前所述,如果目标 SQLInstance 位于另一个 Azure VM 中,请确保该 VM 已注册到此保管库,并且相关的 SQLInstance 显示为可保护的项。As outlined above, if the target SQLInstance lies within another Azure VM, make sure it is registered to this vault and the relevant SQLInstance appears as a protectable item.

$TargetInstance = Get-AzRecoveryServicesBackupProtectableItem -WorkloadType MSSQL -ItemType SQLInstance -Name "<SQLInstance Name>" -ServerName "<SQL VM name>" -VaultId $targetVault.ID

然后,只需结合适当的标志传递相关的恢复点和目标 SQL 实例,如下所示。Then just pass the relevant recovery point, target SQL instance with the right flag as shown below.

使用不同恢复点的备用工作负荷还原Alternate restore with distinct Recovery point
$AnotherInstanceWithFullConfig = Get-AzRecoveryServicesBackupWorkloadRecoveryConfig -RecoveryPoint $FullRP -TargetItem $TargetInstance -AlternateWorkloadRestore -VaultId $targetVault.ID
使用日志时间点的备用工作负荷还原Alternate restore with log point-in-time
$AnotherInstanceWithLogConfig = Get-AzRecoveryServicesBackupWorkloadRecoveryConfig -PointInTime $PointInTime -Item $bkpItem -AlternateWorkloadRestore -VaultId $targetVault.ID
作为文件还原Restore as Files

若要将备份数据作为 .bak 文件而不是数据库还原,请选择“作为文件还原”选项。To restore the backup data as .bak files instead of a database, choose the Restore as Files option. 已备份的 SQL 数据库可以还原为任何已注册到此保管库的目标 VM。The backed-up SQL DB can be restored to any target VM that is registered to this vault.

$TargetContainer= Get-AzRecoveryServicesBackupContainer -ContainerType AzureVMAppContainer -FriendlyName "VM name" -VaultId $vaultID
使用不同恢复点还原为文件Restore as files with distinct Recovery point
$FileRestoreWithFullConfig = Get-AzRecoveryServicesBackupWorkloadRecoveryConfig -RecoveryPoint $FullRP -TargetContainer $TargetContainer -RestoreAsFiles -FilePath "<>" -VaultId $targetVault.ID
根据最新完整备份使用日志时间点还原为文件Restore as files with log point-in-time from latest full
$FileRestoreWithLogConfig = Get-AzRecoveryServicesBackupWorkloadRecoveryConfig -PointInTime $PointInTime -TargetContainer $TargetContainer -RestoreAsFiles -FilePath "<>" -VaultId $targetVault.ID
根据指定完整备份使用日志时间点还原为文件Restore as files with log point-in-time from a specified full

如果希望提供应该用于还原的特定完整备份,请使用以下命令:If you want to give a specific full that should be used for restore, use the following command:

$FileRestoreWithLogAndSpecificFullConfig = Get-AzRecoveryServicesBackupWorkloadRecoveryConfig -PointInTime $PointInTime -FromFull $FullRP -TargetContainer $TargetContainer -RestoreAsFiles -FilePath "<>" -VaultId $targetVault.ID

Get-AzRecoveryServicesBackupWorkloadRecoveryConfig PS cmdlet 获取的最终恢复点配置对象包含用于还原的所有相关信息,如下所示。The final recovery point configuration object obtained from Get-AzRecoveryServicesBackupWorkloadRecoveryConfig PS cmdlet has all the relevant information for restore and is as shown below.

TargetServer         : <SQL server name>
TargetInstance       : <Target Instance name>
RestoredDBName       : <Target Instance name>/azurebackup1_restored_3_19_2019_1850
OverwriteWLIfpresent : No
NoRecoveryMode       : Disabled
targetPhysicalPath   : {azurebackup1, azurebackup1_log}
ContainerId          : /Subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/testRG/providers/Microsoft.RecoveryServices/vaults/testVault/backupFabrics/Azure/protectionContainers/vmappcontainer;compute;computeRG;SQLVMName
SourceResourceId     : /subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/computeRG/VMAppContainer/SQLVMName
RestoreRequestType   : Alternate WL Restore
RecoveryPoint        : Microsoft.Azure.Commands.RecoveryServices.Backup.Cmdlets.Models.AzureWorkloadRecoveryPoint
PointInTime          : 1/1/0001 12:00:00 AM

可以编辑已还原的数据库名称、OverwriteWLIfpresent、NoRecoveryMode 和 targetPhysicalPath 字段。You can edit the restored DB name, OverwriteWLIfpresent, NoRecoveryMode, and targetPhysicalPath fields. 按如下所示获取目标文件路径的其他详细信息。Get more details for the target file paths as shown below.

$AnotherInstanceWithFullConfig.targetPhysicalPath
MappingType SourceLogicalName SourcePath                  TargetPath
----------- ----------------- ----------                  ----------
Data        azurebackup1      F:\Data\azurebackup1.mdf    F:\Data\azurebackup1_1553001753.mdf
Log         azurebackup1_log  F:\Log\azurebackup1_log.ldf F:\Log\azurebackup1_log_1553001753.ldf

按如下所示将相关 PS 属性设置为字符串值。Set the relevant PS properties as string values as shown below.

$AnotherInstanceWithFullConfig.OverwriteWLIfpresent = "Yes"
$AnotherInstanceWithFullConfig | fl
TargetServer         : <SQL server name>
TargetInstance       : <Target Instance name>
RestoredDBName       : <Target Instance name>/azurebackup1_restored_3_19_2019_1850
OverwriteWLIfpresent : Yes
NoRecoveryMode       : Disabled
targetPhysicalPath   : {azurebackup1, azurebackup1_log}
ContainerId          : /Subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/testRG/providers/Microsoft.RecoveryServices/vaults/testVault/backupFabrics/Azure/protectionContainers/vmappcontainer;compute;computeRG;SQLVMName
SourceResourceId     : /subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/computeRG/VMAppContainer/SQLVMName
RestoreRequestType   : Alternate WL Restore
RecoveryPoint        : Microsoft.Azure.Commands.RecoveryServices.Backup.Cmdlets.Models.AzureWorkloadRecoveryPoint
PointInTime          : 1/1/0001 12:00:00 AM

重要

确保最终恢复配置对象包含全部所需且正确的值,因为还原操作基于该配置对象。Make sure that the final recovery config object has all the necessary and proper values since the restore operation will be based on the config object.

使用相关配置还原Restore with relevant configuration

获取并验证相关恢复配置对象后,使用 Restore-AzRecoveryServicesBackupItem PS cmdlet 启动还原过程。Once the relevant recovery Config object is obtained and verified, use the Restore-AzRecoveryServicesBackupItem PS cmdlet to start the restore process.

Restore-AzRecoveryServicesBackupItem -WLRecoveryConfig $AnotherInstanceWithLogConfig -VaultId $targetVault.ID

还原操作将返回要跟踪的作业。The restore operation returns a job to be tracked.

WorkloadName     Operation            Status               StartTime                 EndTime                   JobID
------------     ---------            ------               ---------                 -------                   -----
MSSQLSERVER/m... Restore              InProgress           3/17/2019 10:02:45 AM                                3274xg2b-e4fg-5952-89b4-8cb566gc1748

管理 SQL 备份Manage SQL backups

按需备份On-demand backup

为数据库启用备份后,用户还可以使用 Backup-AzRecoveryServicesBackupItem PS cmdlet 来触发数据库的按需备份。Once backup has been enabled for a DB, user can also trigger an on-demand backup for the DB using Backup-AzRecoveryServicesBackupItem PS cmdlet. 以下示例对启用了压缩的 SQL 数据库触发完整备份,完整备份应保留 60 天。The following example triggers a full backup on a SQL DB with compression enabled and the full backup should be retained for 60 days.

$bkpItem = Get-AzRecoveryServicesBackupItem -BackupManagementType AzureWorkload -WorkloadType MSSQL -Name "<backup item name>" -VaultId $targetVault.ID
$endDate = (Get-Date).AddDays(60).ToUniversalTime()
Backup-AzRecoveryServicesBackupItem -Item $bkpItem -BackupType Full -EnableCompression -VaultId $targetVault.ID -ExpiryDateTimeUTC $endDate

按需备份命令返回要跟踪的作业。The on-demand backup command returns a job to be tracked.

WorkloadName     Operation            Status               StartTime                 EndTime                   JobID
------------     ---------            ------               ---------                 -------                   -----
MSSQLSERVER/m... Backup               InProgress           3/18/2019 8:41:27 PM                                2516bb1a-d3ef-4841-97a3-9ba455fb0637

如果未返回输出或者你要获取相关的作业 ID,请从 Azure 备份服务获取作业列表,并跟踪该列表及其详细信息。If the output is lost or if you want to get the relevant Job ID, get the list of jobs from Azure Backup service and then track it and its details.

更改备份项的策略Change policy for backup items

用户可以将备份项的策略从 Policy1 更改为 Policy2。User can change the policy of the backed-up item from Policy1 to Policy2. 若要切换备份项的策略,请提取相关策略并备份项,并使用 Enable-AzRecoveryServices 命令以备份项作为参数。To switch policies for a backed-up item, fetch the relevant policy and back up item and use the Enable-AzRecoveryServices command with backup item as the parameter.

$TargetPol1 = Get-AzRecoveryServicesBackupProtectionPolicy -Name <PolicyName>
$anotherBkpItem = Get-AzRecoveryServicesBackupItem -WorkloadType MSSQL -BackupManagementType AzureWorkload -Name "<BackupItemName>"
Enable-AzRecoveryServicesBackupProtection -Item $anotherBkpItem -Policy $TargetPol1

该命令将一直等到配置备份完成并返回以下输出。The command waits until the configure backup is completed and returns the following output.

WorkloadName     Operation            Status               StartTime                 EndTime                   JobID
------------     ---------            ------               ---------                 -------                   -----
master           ConfigureBackup      Completed            3/18/2019 8:00:21 PM      3/18/2019 8:02:16 PM      654e8aa2-4096-402b-b5a9-e5e71a496c4e

编辑现有备份策略Edit an existing backup policy

若要编辑现有策略,请使用 Set-AzRecoveryServicesBackupProtectionPolicy 命令。To edit an existing policy, use the Set-AzRecoveryServicesBackupProtectionPolicy command.

Set-AzRecoveryServicesBackupProtectionPolicy -Policy $Pol -SchedulePolicy $SchPol -RetentionPolicy $RetPol

经过一段时间后检查备份作业以跟踪任何故障。Check the backup jobs after some time has passed to track any failures. 如果存在故障,需要解决这些问题。If there are, you need to fix the issues. 然后使用 FixForInconsistentItems 参数重新运行编辑策略命令,重新对先前操作失败的所有备份项上的策略尝试编辑。Then rerun the edit policy command with the FixForInconsistentItems parameter to retry editing the policy on all the backup items for which the operation failed earlier.

Set-AzRecoveryServicesBackupProtectionPolicy -Policy $Pol -FixForInconsistentItems

重新注册 SQL VMRe-register SQL VMs

警告

在尝试重新注册之前,请务必阅读此文档,以了解失败症状和原因Make sure to read this document to understand the failure symptoms and causes before attempting re-registration

若要触发 SQL VM 的重新注册,请提取相关的备份容器,并将其传递给 register cmdlet。To trigger re-registration of the SQL VM, fetch the relevant backup container and pass it to the register cmdlet.

$SQLContainer = Get-AzRecoveryServicesBackupContainer -ContainerType AzureVMAppContainer -FriendlyName <VM name> -VaultId $targetvault.ID
Register-AzRecoveryServicesBackupContainer -Container $SQLContainer -BackupManagementType AzureWorkload -WorkloadType MSSQL -VaultId $targetVault.ID

停止保护Stop protection

保留数据Retain data

如果用户想要停止保护,他们可以使用 Disable-AzRecoveryServicesBackupProtection PS cmdlet。If user wishes to stop protection, they can use the Disable-AzRecoveryServicesBackupProtection PS cmdlet. 此命令将停止计划的备份,但到目前为止备份的数据将永远保留。This will stop the scheduled backups but the data backed up until now is retained forever.

$bkpItem = Get-AzRecoveryServicesBackupItem -BackupManagementType AzureWorkload -WorkloadType MSSQL -Name "<backup item name>" -VaultId $targetVault.ID
Disable-AzRecoveryServicesBackupProtection -Item $bkpItem -VaultId $targetVault.ID

删除备份数据Delete backup data

若要完全删除保管库中存储的备份数据,只需将“-RemoveRecoveryPoints”标志/开关添加到“disable”保护命令In order to completely remove the stored backup data in the vault, just add '-RemoveRecoveryPoints' flag/switch to the 'disable' protection command.

Disable-AzRecoveryServicesBackupProtection -Item $bkpItem -VaultId $targetVault.ID -RemoveRecoveryPoints

禁用自动保护Disable auto protection

如果在 SQLInstance 上配置了自动保护,用户可以使用 Disable-AzRecoveryServicesBackupAutoProtection PS cmdlet 来禁用自动保护。If autoprotection was configured on an SQLInstance, user can disable it using the Disable-AzRecoveryServicesBackupAutoProtection PS cmdlet.

$SQLInstance = Get-AzRecoveryServicesBackupProtectableItem -workloadType MSSQL -ItemType SQLInstance -VaultId $targetVault.ID -Name "<Protectable Item name>" -ServerName "<Server Name>"
Disable-AzRecoveryServicesBackupAutoProtection -InputItem $SQLInstance -BackupManagementType AzureWorkload -WorkloadType MSSQL -VaultId $targetvault.ID

取消注册 SQL VMUnregister SQL VM

如果 SQL 服务器的所有数据库不再受保护且不存在任何备份数据,用户可以从此保管库取消注册 SQL VM。If all the DBs of a SQL server are no longer protected and no backup data exists, user can unregister the SQL VM from this vault. 只有这样,用户才能在另一保管库中保护数据库。Only then user can protect DBs to another vault. 使用 Unregister-AzRecoveryServicesBackupContainer PS cmdlet 取消注册 SQL VM。Use Unregister-AzRecoveryServicesBackupContainer PS cmdlet to unregister the SQL VM.

$SQLContainer = Get-AzRecoveryServicesBackupContainer -ContainerType AzureVMAppContainer -FriendlyName <VM name> -VaultId $targetvault.ID
 Unregister-AzRecoveryServicesBackupContainer -Container $SQLContainer -VaultId $targetvault.ID

跟踪 Azure 备份作业Track Azure Backup jobs

请务必注意,Azure 备份只会跟踪 SQL 备份中用户触发的作业。It is important to note that Azure Backup only tracks user triggered jobs in SQL backup. 计划的备份(包括日志备份)不会显示在门户/PowerShell 中。Scheduled backups (including log backups) are not visible in portal/powershell. 但是,如果任一计划的作业失败,将会生成备份警报并在门户中显示该警报。However, if any scheduled jobs fail, a backup alert is generated and shown in portal. 使用 Azure Monitor 跟踪所有计划的作业和其他相关信息。Use Azure Monitor to track all the scheduled jobs and other relevant information.

用户可以使用备份等异步作业的输出中返回的 JobID,来跟踪按需/用户触发的操作。Users can track on-demand/user triggered operations with the JobID that is returned in the output of asynchronous jobs such as backup. 使用 Get-AzRecoveryServicesBackupJobDetail PS cmdlet 跟踪作业及其详细信息。Use Get-AzRecoveryServicesBackupJobDetail PS cmdlet to track job and its details.

 Get-AzRecoveryServicesBackupJobDetails -JobId 2516bb1a-d3ef-4841-97a3-9ba455fb0637 -VaultId $targetVault.ID

若要从 Azure 备份服务获取按需作业的列表及其状态,请使用 Get-AzRecoveryServicesBackupJob PS cmdlet。To get the list of on-demand jobs and their statuses from Azure Backup service, use Get-AzRecoveryServicesBackupJob PS cmdlet. 以下示例返回所有正在进行的 SQL 作业。The following example returns all the in-progress SQL jobs.

Get-AzRecoveryServicesBackupJob -Status InProgress -BackupManagementType AzureWorkload

若要取消正在进行的作业,请使用 Stop-AzRecoveryServicesBackupJob PS cmdlet。To cancel an in-progress job, use the Stop-AzRecoveryServicesBackupJob PS cmdlet.

管理 SQL Always On 可用性组Managing SQL Always On Availability groups

对于 SQL Always On 可用性组,请确保注册可用性组 (AG) 的所有节点For SQL Always On Availability Groups, make sure to register all the nodes of the Availability group (AG). 注册所有节点后,将在可保护项下以逻辑方式创建一个 SQL 可用性组对象。Once registration is done for all nodes, a SQL availability group object is logically created under protectable items. SQL AG 下的数据库作为“SQLDatabase”列出。The databases under the SQL AG will be listed as 'SQLDatabase'. 节点将显示为独立实例,其下的默认 SQL 数据库也将作为 SQL 数据库列出。The nodes will show up as standalone instances and the default SQL databases under them will be listed as SQL databases as well.

例如,假设某个 SQL AG 包含两个节点(“sql-server-0”与“sql-server-1”)和 1 个 SQL AG 数据库。For example, let's assume a SQL AG has two nodes: 'sql-server-0' and 'sql-server-1' and 1 SQL AG DB. 注册这两个节点后,如果用户列出可保护项,将列出以下组件Once both these nodes are registered, if user lists the protectable items, it lists the following components

  • SQL AG 对象 - 作为 SQLAvailabilityGroup 列出的可保护项类型A SQL AG object - protectable item type as SQLAvailabilityGroup
  • SQL AG 数据库 - 作为 SQLDatabase 列出的可保护项类型A SQL AG DB - protectable item type as SQLDatabase
  • sql-server-0 - 作为 SQLInstance 列出的可保护项类型sql-server-0 - protectable item type as SQLInstance
  • sql-server-1 - 作为 SQLInstance 列出的可保护项类型sql-server-1 - protectable item type as SQLInstance
  • sql server-0 下的所有默认 SQL 数据库(master、model、msdb)- 作为 SQLDatabase 列出的可保护项类型Any default SQL DBs (master, model, msdb) under sql-server-0 - protectable item type as SQLDatabase
  • sql server-1 下的所有默认 SQL 数据库(master、model、msdb)- 作为 SQLDatabase 列出的可保护项类型Any default SQL DBs (master, model, msdb) under sql-server-1 - protectable item type as SQLDatabase

列出备份容器时,sql-server-0、sql-server-1 也作为“AzureVMAppContainer”列出。sql-server-0, sql-server-1 will also be listed as "AzureVMAppContainer" when backup containers are listed.

只需提取相关的数据库即可启用备份按需备份 restore PS cmdlet 是相同的。Just fetch the relevant database to enable backup and the on-demand backup and restore PS cmdlets are identical.