管理 Azure SQL 数据库长期备份保留Manage Azure SQL Database long-term backup retention

在 Azure SQL 数据库中,可以使用长期备份保留策略 (LTR) 配置单一数据库或共用数据库,以自动将备份在 Azure Blob 存储中保留最多 10 年。In Azure SQL Database, you can configure a single or a pooled database with a long-term backup retention policy (LTR) to automatically retain backups in Azure Blob storage for up to 10 years. 然后,可以通过 Azure 门户或 PowerShell 使用这些备份来恢复数据库。You can then recover a database using these backups using the Azure portal or PowerShell.

使用 Azure 门户配置长期保留策略并还原备份Use the Azure portal to configure long-term retention policies and restore backups

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

配置长期保留策略Configure long-term retention policies

可以对 SQL 数据库进行配置,使其保留自动备份的时间长于你的服务层级的保留期。You can configure SQL Database to retain automated backups for a period longer than the retention period for your service tier.

  1. 在 Azure 门户中,选择你的 SQL Server,然后单击“管理备份”。In the Azure portal, select your SQL server and then click Manage Backups. 在“配置策略”选项卡上,选中要为其设置或修改长期备份保留策略的数据库所对应的复选框。On the Configure policies tab, select the checkbox for the database on which you want to set or modify long-term backup retention policies. 如果未选中数据库旁边的复选框,则策略的更改将不会应用于该数据库。If the checkbox next to the database is not selected, the changes for the policy will not apply to that database.

    管理备份链接

  2. 在“配置策略”窗格中,选择是要保留每周、每月还是每年备份,并指定各自的保留期。In the Configure policies pane, select if want to retain weekly, monthly or yearly backups and specify the retention period for each.

    配置策略

  3. 完成后,单击“应用”。When complete, click Apply.

Important

启用长期备份保留策略时,最长可能需要 7 天以后才能查看和还原第一个备份。When you enable a long-term backup retention policy, it may take up to 7 days for the first backup to become visible and available to restore. 有关 LTR 备份频率的详细信息,请参阅长期备份保留For details of the LTR backup cadance, see long-term backup retention.

使用 Azure 门户查看备份并从备份进行还原View backups and restore from a backup using Azure portal

查看通过 LTR 策略为特定数据库保留的备份,并从这些备份进行还原。View the backups that are retained for a specific database with a LTR policy, and restore from those backups.

  1. 在 Azure 门户中,选择你的 SQL Server,然后单击“管理备份”。In the Azure portal, select your SQL server and then click Manage Backups. 在“可用备份”选项卡上,选择要查看其可用备份的数据库。On the Available backups tab, select the database for which you want to see available backups.

    选择数据库

  2. 在“可用备份”窗格中,查看可用备份。In the Available backups pane, review the available backups.

    查看备份

  3. 选择要从中进行还原的备份,然后指定新的数据库名称。Select the backup from which you want to restore, and then specify the new database name.

    还原

  4. 单击“确定”将数据库从 Azure SQL 存储中的备份还原到新数据库。Click OK to restore your database from the backup in Azure SQL storage to the new database.

  5. 在工具栏上,单击通知图标可查看还原作业的状态。On the toolbar, click the notification icon to view the status of the restore job.

    还原作业进度

  6. 完成还原作业后,打开“SQL 数据库”页面以查看新还原的数据库。When the restore job is completed, open the SQL databases page to view the newly restored database.

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.

使用 PowerShell 配置长期保留策略并还原备份Use PowerShell to configure long-term retention policies and restore backups

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.

Important

PowerShell Azure 资源管理器模块仍受 Azure SQL 数据库的支持,但所有未来的开发都是针对 Az.Sql 模块的。The PowerShell Azure Resource Manager module is still supported by Azure SQL Database, but all future development is for the Az.Sql module. 若要了解这些 cmdlet,请参阅 AzureRM.SqlFor these cmdlets, see AzureRM.Sql. Az 模块和 AzureRm 模块中的命令参数大体上是相同的。The arguments for the commands in the Az module and in the AzureRm modules are substantially identical.

以下各部分展示了如何使用 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

为了管理 LTR 备份,你需要为In order to manage LTR backups, you will need to be

  • “订阅所有者”或Subscription Owner or
  • “SQL Server 参与者”角色(在订阅范围内)或SQL Server Contributor role in Subscription scope or
  • “SQL 数据库参与者”角色(在订阅范围内)SQL Database Contributor role in Subscription scope

如果需要更精细的控制,可以创建自定义 RBAC 角色并在订阅范围内分配它们。If more granular control is required, you can create custom RBAC roles and assign them in Subscription scope.

对于 Get-AzSqlDatabaseLongTermRetentionBackupRestore-AzSqlDatabase,角色需要具有以下权限:For Get-AzSqlDatabaseLongTermRetentionBackup and Restore-AzSqlDatabase the role needs to have following permissions:

Microsoft.Sql/locations/longTermRetentionBackups/read Microsoft.Sql/locations/longTermRetentionServers/longTermRetentionBackups/read Microsoft.Sql/locations/longTermRetentionServers/longTermRetentionDatabases/longTermRetentionBackups/readMicrosoft.Sql/locations/longTermRetentionBackups/read Microsoft.Sql/locations/longTermRetentionServers/longTermRetentionBackups/read Microsoft.Sql/locations/longTermRetentionServers/longTermRetentionDatabases/longTermRetentionBackups/read

对于 Remove-AzSqlDatabaseLongTermRetentionBackup,角色需要具有以下权限:For Remove-AzSqlDatabaseLongTermRetentionBackup the role need to have following permissions:

Microsoft.Sql/locations/longTermRetentionServers/longTermRetentionDatabases/longTermRetentionBackups/deleteMicrosoft.Sql/locations/longTermRetentionServers/longTermRetentionDatabases/longTermRetentionBackups/delete

创建 LTR 策略Create an LTR policy

# Get the SQL server 
# $subId = "{subscription-id}"
# $serverName = "{server-name}"
# $resourceGroup = "{resource-group-name}" 
# $dbName = "{database-name}"

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

# get the server
$server = Get-AzSqlServer -ServerName $serverName -ResourceGroupName $resourceGroup

# create LTR policy with WeeklyRetention = 12 weeks. MonthlyRetention and YearlyRetention = 0 by default.
Set-AzSqlDatabaseBackupLongTermRetentionPolicy -ServerName $serverName -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-AzSqlDatabaseBackupLongTermRetentionPolicy -ServerName $serverName -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 a server

# Get all LTR policies within a server
$ltrPolicies = Get-AzSqlDatabase -ResourceGroupName Default-SQL-WestCentralUS -ServerName trgrie-ltr-server | Get-AzSqlDatabaseLongTermRetentionPolicy -Current 

# Get the LTR policy of a specific database 
$ltrPolicies = Get-AzSqlDatabaseBackupLongTermRetentionPolicy -ServerName $serverName -DatabaseName $dbName  -ResourceGroupName $resourceGroup -Current

清除 LTR 策略Clear an LTR policy

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

Set-AzSqlDatabaseBackupLongTermRetentionPolicy -ServerName $serverName -DatabaseName $dbName -ResourceGroupName $resourceGroup -RemovePolicy

查看 LTR 备份View LTR backups

此示例展示了如何列出服务器内的 LTR 备份。This example shows how to list the LTR backups within a server.

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

# Get the list of LTR backups from the Azure region under 
# the named server. 
$ltrBackups = Get-AzSqlDatabaseLongTermRetentionBackup -Location $server.Location -ServerName $serverName

# Get the LTR backups for a specific database from the Azure region under the named server 
$ltrBackups = Get-AzSqlDatabaseLongTermRetentionBackup -Location $server.Location -ServerName $serverName -DatabaseName $dbName

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

# Only list the latest LTR backup for each database 
$ltrBackups = Get-AzSqlDatabaseLongTermRetentionBackup -Location $server.Location -ServerName $serverName -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-AzSqlDatabaseLongTermRetentionBackup -ResourceId $ltrBackup.ResourceId

Important

删除 LTR 备份操作是不可逆的。Deleting LTR backup is non-reversible. 可以通过筛选“删除长期保留备份”操作来在 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 LTR backup as an S3 database
Restore-AzSqlDatabase -FromLongTermRetentionBackup -ResourceId $ltrBackup.ResourceId -ServerName $serverName -ResourceGroupName $resourceGroup -TargetDatabaseName $dbName -ServiceObjectiveName S3

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