管理 Azure SQL 数据库托管实例长期备份保留 (PowerShell)Manage Azure SQL Database managed instance long-term backup retention (PowerShell)

在 Azure SQL 数据库托管实例中,可以配置长期备份保留策略 (LTR),这是一项有限的公共预览版功能。In Azure SQL Database managed instance, you can configure a long-term backup retention policy (LTR) as a limited public preview feature. 这样就可以在单独的 Azure Blob 存储容器中自动将数据库备份保留长达 10 年的时间。This allows you to to automatically retain database backups in separate Azure Blob storage containers for up to 10 years. 然后,可以通过 PowerShell 使用这些备份来恢复数据库。You can then recover a database using these backups with PowerShell.

Important

对于托管实例,LTR 目前为功能有限的预览版,可根据具体情况用于 EA 和 CSP 订阅。LTR for managed instances is currently in limited preview and available for EA and CSP subscriptions on a case by case basis. 若要请求注册,请创建 Azure 支持票证To request enrollment, please create an Azure support ticket.

以下各部分展示了如何使用 PowerShell 配置长期备份保留、查看 Azure SQL 存储中的备份,以及从 Azure SQL 存储中的备份进行还原。The following sections show you how to use PowerShell to configure the long-term backup retention, view backups in Azure SQL storage, and restore from a backup in Azure SQL storage.

用于管理长期保留的 RBAC 角色RBAC roles to manage long-term retention

对于 Get-AzSqlInstanceDatabaseLongTermRetentionBackup 和 Restore-AzSqlInstanceDatabase ,你需要有以下角色之一:For Get-AzSqlInstanceDatabaseLongTermRetentionBackup and Restore-AzSqlInstanceDatabase, you will need to have one of the following roles:

  • “订阅所有者”角色或Subscription Owner role or
  • “托管实例参与者”角色或Managed Instance Contributor role or
  • 具有以下权限的自定义角色:Custom role with the following permissions:
    • Microsoft.Sql/locations/longTermRetentionManagedInstanceBackups/read
    • Microsoft.Sql/locations/longTermRetentionManagedInstances/longTermRetentionManagedInstanceBackups/read
    • Microsoft.Sql/locations/longTermRetentionManagedInstances/longTermRetentionDatabases/longTermRetentionManagedInstanceBackups/read

对于 Remove-AzSqlInstanceDatabaseLongTermRetentionBackup ,你需要有以下角色之一:For Remove-AzSqlInstanceDatabaseLongTermRetentionBackup, you will need to have one of the following roles:

  • “订阅所有者”角色或Subscription Owner role or
  • 具有以下权限的自定义角色:Custom role with the following permission:
    • Microsoft.Sql/locations/longTermRetentionManagedInstances/longTermRetentionDatabases/longTermRetentionManagedInstanceBackups/delete

Note

“托管实例参与者”角色没有删除 LTR 备份的权限。The Managed Instance Contributor role does not have permission to delete LTR backups.

可以在“订阅” 或“资源组” 范围内授予 RBAC 权限。RBAC permissions could be granted in either subscription or resource group scope. 但是,若要访问属于已删除实例的 LTR 备份,必须在该实例的“订阅” 范围内授予此权限。However, to access LTR backups that belong to a dropped instance, the permission must be granted in the subscription scope of that instance.

  • Microsoft.Sql/locations/longTermRetentionManagedInstances/longTermRetentionDatabases/longTermRetentionManagedInstanceBackups/delete

创建 LTR 策略Create an LTR policy

# get the Managed Instance
$subId = "<subscriptionId>"
$instanceName = "<instanceName>"
$resourceGroup = "<resourceGroupName>"
$dbName = "<databaseName>"

Connect-AzAccount -Environment AzureChinaCloud
Select-AzSubscription -SubscriptionId $subId

$instance = Get-AzSqlInstance -Name $instanceName -ResourceGroupName $resourceGroup

# create LTR policy with WeeklyRetention = 12 weeks. MonthlyRetention and YearlyRetention = 0 by default.
Set-AzSqlInstanceDatabaseBackupLongTermRetentionPolicy -InstanceName $instanceName `
   -DatabaseName $dbName -ResourceGroupName $resourceGroup -WeeklyRetention P12W

# create LTR policy with WeeklyRetention = 12 weeks, YearlyRetention = 5 years and WeekOfYear = 16 (week of April 15). MonthlyRetention = 0 by default.
Set-AzSqlInstanceDatabaseBackupLongTermRetentionPolicy -InstanceName $instanceName `
    -DatabaseName $dbName -ResourceGroupName $resourceGroup -WeeklyRetention P12W -YearlyRetention P5Y -WeekOfYear 16

查看 LTR 策略View LTR policies

此示例展示了如何列出实例内的 LTR 策略This example shows how to list the LTR policies within an instance

# gets the current version of LTR policy for the database
$ltrPolicies = Get-AzSqlInstanceDatabaseBackupLongTermRetentionPolicy -InstanceName $instanceName `
    -DatabaseName $dbName -ResourceGroupName $resourceGroup

清除 LTR 策略Clear an LTR policy

此示例展示了如何从数据库中清除 LTR 策略This example shows how to clear an LTR policy from a database

Set-AzSqlInstanceDatabaseBackupLongTermRetentionPolicy -InstanceName $instanceName `
   -DatabaseName $dbName -ResourceGroupName $resourceGroup -RemovePolicy

查看 LTR 备份View LTR backups

此示例展示了如何列出实例内的 LTR 备份。This example shows how to list the LTR backups within an instance.

# get the list of all LTR backups in a specific Azure region
# backups are grouped by the logical database id, within each group they are ordered by the timestamp, the earliest backup first
$ltrBackups = Get-AzSqlInstanceDatabaseLongTermRetentionBackup -Location $instance.Location

# get the list of LTR backups from the Azure region under the given managed instance
$ltrBackups = Get-AzSqlInstanceDatabaseLongTermRetentionBackup -Location $instance.Location -InstanceName $instanceName

# get the LTR backups for a specific database from the Azure region under the given managed instance
$ltrBackups = Get-AzSqlInstanceDatabaseLongTermRetentionBackup -Location $instance.Location -InstanceName $instanceName -DatabaseName $dbName

# list LTR backups only from live databases (you have option to choose All/Live/Deleted)
$ltrBackups = Get-AzSqlInstanceDatabaseLongTermRetentionBackup -Location $instance.Location -DatabaseState Live

# only list the latest LTR backup for each database
$ltrBackups = Get-AzSqlInstanceDatabaseLongTermRetentionBackup -Location $instance.Location -InstanceName $instanceName -OnlyLatestPerDatabase

删除 LTR 备份Delete LTR backups

此示例展示了如何从备份列表中删除 LTR 备份。This example shows how to delete an LTR backup from the list of backups.

# remove the earliest backup
$ltrBackup = $ltrBackups[0]
Remove-AzSqlInstanceDatabaseLongTermRetentionBackup -ResourceId $ltrBackup.ResourceId

Important

删除 LTR 备份操作是不可逆的。Deleting LTR backup is non-reversible. 若要在删除实例后删除 LTR 备份,必须有“订阅”范围权限。To delete an LTR backup after the instance has been deleted you must have Subscription scope permission. 可以通过筛选“删除长期保留备份”操作来在 Azure Monitor 中设置有关每次删除的通知。You can set up notifications about each delete in Azure Monitor by filtering for operation 'Deletes a long term retention backup'. 活动日志包含有关发出请求的人员和时间的信息。The activity log contains information on who and when made the request. 有关详细说明,请参阅创建活动日志警报See Create activity log alerts for detailed instructions.

从 LTR 备份进行还原Restore from LTR backups

此示例展示了如何从 LTR 备份进行还原。This example shows how to restore from an LTR backup. 请注意,此接口没有更改,但是资源 ID 参数现在要求提供 LTR 备份资源 ID。Note, this interface did not change but the resource id parameter now requires the LTR backup resource id.

# restore a specific LTR backup as an P1 database on the instance $instanceName of the resource group $resourceGroup
Restore-AzSqlInstanceDatabase -FromLongTermRetentionBackup -ResourceId $ltrBackup.ResourceId `
   -TargetInstanceName $instanceName -TargetResourceGroupName $resourceGroup -TargetInstanceDatabaseName $dbName

Important

若要在删除实例后从 LTR 备份进行还原,必须有实例订阅范围内的权限,并且该订阅必须处于活动状态。To restore from an LTR backup after the instance has been deleted, you must have permissions scoped to the subscription of the instance and that subscription must be active. 还必须省略可选的 -ResourceGroupName 参数。You must also omit the optional -ResourceGroupName parameter.

Note

从此处,可使用 SQL Server Management Studio 连接到已还原的数据库,执行所需任务,例如从恢复的数据库中提取一部分数据,复制到现有数据库或删除现有数据库,并将已还原的数据库重命名为现有数据库名。From here, you can connect to the restored database using SQL Server Management Studio to perform needed tasks, such as to extract a bit of data from the restored database to copy into the existing database or to delete the existing database and rename the restored database to the existing database name. 请参阅时间点还原See point in time restore.

后续步骤Next steps