管理 Azure SQL Database 長期備份保留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 the database backups in separate Azure Blob storage containers for up to 10 years. 然後,您可以使用 Azure 入口網站或 PowerShell 來復原資料庫。You can then recover a database using these backups using the Azure portal or PowerShell.

重要

Azure SQL 資料庫託管實例當前不支援長期備份保留。Azure SQL database managed instance does not currently support long-term backup retention.

使用 Azure 入口網站Using Azure portal

下列各節說明如何使用 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

您可以將 Azure SQL Database 設定為保留自動備份的期間比您服務層級的保留期限還要長。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.

重要

啟用長期備份保留原則時,第一個備份最多可能需要 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.

查看備份並從備份還原View backups and restore from a backup

檢視使用 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 Database]**** 頁面,以檢視剛還原的資料庫。When the restore job is completed, open the SQL databases page to view the newly restored database.

注意

從這裡開始,您可以使用 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.

使用 PowerShellUsing PowerShell

注意

本文已更新為使用新的 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.

重要

Azure SQL 資料庫仍然支援 PowerShell Azure 資源管理器模組,但所有後續開發都針對 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

對於獲取 AzSql 資料庫長期保留備份還原-AzSql 資料庫,您需要具有以下角色之一:For Get-AzSqlDatabaseLongTermRetentionBackup and Restore-AzSqlDatabase, you will need to have one of the following roles:

  • 訂閱擁有者角色或Subscription Owner role or

  • SQL 伺服器參與者角色或SQL Server Contributor role or

  • 具有以下許可權的自訂角色:Custom role with the following permissions:

    微軟.Sql/位置/長期保留備份/讀取 Microsoft.Sql/位置/長期保留伺服器/長期保留備份/讀取 Microsoft.Sql/位置/長期保留伺服器/長期保留資料庫/長期保留備份/讀取Microsoft.Sql/locations/longTermRetentionBackups/read Microsoft.Sql/locations/longTermRetentionServers/longTermRetentionBackups/read Microsoft.Sql/locations/longTermRetentionServers/longTermRetentionDatabases/longTermRetentionBackups/read

對於刪除 AzSql 資料庫長期保留備份,您需要具有以下角色之一:For Remove-AzSqlDatabaseLongTermRetentionBackup, you will need to have one of the following roles:

  • 訂閱擁有者角色或Subscription Owner role or

  • 具有以下許可權的自訂角色:Custom role with the following permission:

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

注意

SQL 伺服器參與者角色無權刪除 LTR 備份。The SQL Server 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 server, the permission must be granted in the subscription scope of that server.

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

建立 LTR 原則Create an LTR policy

# get the SQL server
$subId = "<subscriptionId>"
$serverName = "<serverName>"
$resourceGroup = "<resourceGroupName>"
$dbName = "<databaseName>"

Connect-AzAccount
Select-AzSubscription -SubscriptionId $subId

$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
# 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

重要

刪除 LTR 備份,便無法回復。Deleting LTR backup is non-reversible. 要在伺服器被刪除後刪除 LTR 備份,您必須具有訂閱範圍許可權。To delete an LTR backup after the server has been deleted you must have Subscription scope permission. 您可以篩選「刪除長期保留備份」作業,以在 Azure 監視器中設定每項刪除的通知。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. 請注意,這個介面並未變更,但是資源識別碼參數現在需要 LTR 備份資源識別碼。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 server $serverName of the resource group $resourceGroup
Restore-AzSqlDatabase -FromLongTermRetentionBackup -ResourceId $ltrBackup.ResourceId -ServerName $serverName -ResourceGroupName $resourceGroup `
    -TargetDatabaseName $dbName -ServiceObjectiveName P1

重要

要在伺服器刪除後從 LTR 備份進行還原,您必須具有伺服器訂閱的許可權範圍,並且該訂閱必須處於活動狀態。To restore from an LTR backup after the server has been deleted, you must have permissions scoped to the server's subscription and that subscription must be active. 您還必須省略可選的 -資源組名稱參數。You must also omit the optional -ResourceGroupName parameter.

注意

從這裡開始,您可以使用 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