使用自動資料庫備份來復原 Azure SQL 資料庫Recover an Azure SQL database by using automated database backups

根據預設,Azure SQL Database 備份會儲存在異地複寫的 blob 儲存體(GRS 儲存體類型)中。By default, Azure SQL Database backups are stored in geo-replicated blob storage (RA-GRS storage type). 下列選項適用于使用自動資料庫備份進行資料庫復原。The following options are available for database recovery by using automated database backups. 您可以:You can:

  • 在相同的 SQL Database 伺服器上建立新的資料庫,並復原到保留期限內的指定時間點。Create a new database on the same SQL Database server, recovered to a specified point in time within the retention period.
  • 在相同的 SQL Database 伺服器上建立資料庫,並復原到已刪除資料庫的刪除時間。Create a database on the same SQL Database server, recovered to the deletion time for a deleted database.
  • 在相同區域中的任何 SQL Database 伺服器上建立新的資料庫,並復原到最新的備份點。Create a new database on any SQL Database server in the same region, recovered to the point of the most recent backups.
  • 在任何其他區域中的任何 SQL Database 伺服器上建立新的資料庫,並復原到最新複寫的備份點。Create a new database on any SQL Database server in any other region, recovered to the point of the most recent replicated backups.

如果您已設定備份長期保留,您也可以從任何 SQL Database 伺服器上的任何長期保留備份來建立新的資料庫。If you configured backup long-term retention, you can also create a new database from any long-term retention backup on any SQL Database server.

重要

在還原期間,您無法覆寫現有的資料庫。You can't overwrite an existing database during restore.

當您使用「標準」或「高階」服務層級時,您的資料庫還原可能會產生額外的儲存成本。When you're using the Standard or Premium service tiers, your database restore might incur an extra storage cost. 當還原資料庫的大小上限大於目標資料庫的服務層級和效能等級所包含的儲存量時,就會產生額外的成本。The extra cost is incurred when the maximum size of the restored database is greater than the amount of storage included with the target database's service tier and performance level. 如需有關額外儲存體的價格詳細資訊,請參閱 SQL Database 價格頁面For pricing details of extra storage, see the SQL Database pricing page. 如果實際的已使用空間量小於內含的儲存體數量,您可以將資料庫大小上限設定為包含的數量,以避免此額外成本。If the actual amount of used space is less than the amount of storage included, you can avoid this extra cost by setting the maximum database size to the included amount.

復原時間Recovery time

使用自動資料庫備份來還原資料庫的復原時間會受到幾個因素的影響:The recovery time to restore a database by using automated database backups is affected by several factors:

  • 資料庫的大小。The size of the database.
  • 資料庫的計算大小。The compute size of the database.
  • 涉及的交易記錄數目。The number of transaction logs involved.
  • 需要重新執行才能復原到還原點的活動量。The amount of activity that needs to be replayed to recover to the restore point.
  • 還原至不同區域時的網路頻寬。The network bandwidth if the restore is to a different region.
  • 要在目標區域中處理的並行還原要求數目。The number of concurrent restore requests being processed in the target region.

對於大型或非常活躍的資料庫,還原可能需要數小時的時間。For a large or very active database, the restore might take several hours. 如果區域發生長時間中斷,可能會起始大量的異地還原要求以進行嚴重損壞修復。If there is a prolonged outage in a region, it's possible that a high number of geo-restore requests will be initiated for disaster recovery. 當有多個要求時,個別資料庫的復原時間也會增加。When there are many requests, the recovery time for individual databases can increase. 大多數資料庫還原都會在 12 小時內完成。Most database restores complete in less than 12 hours.

對於單一訂用帳戶,並行還原要求數目有一些限制。For a single subscription, there are limitations on the number of concurrent restore requests. 這些限制適用于時間點還原、異地還原和從長期保留備份還原的任何組合。These limitations apply to any combination of point-in-time restores, geo-restores, and restores from long-term retention backup.

正在處理的並行要求的最大數目Max # of concurrent requests being processed 正在提交的並行要求的最大數目Max # of concurrent requests being submitted
單一資料庫 (每個訂閱)Single database (per subscription) 1010 6060
彈性集區 (每個集區)Elastic pool (per pool) 44 200200

沒有內建方法可還原整部伺服器。There isn't a built-in method to restore the entire server. 如需如何完成這項工作的範例,請參閱Azure SQL Database:完整伺服器復原。For an example of how to accomplish this task, see Azure SQL Database: Full Server Recovery.

重要

若要使用自動備份進行復原,您必須是訂用帳戶中 SQL Server 參與者角色的成員,或者是訂用帳戶擁有者。To recover by using automated backups, you must be a member of the SQL Server contributor role in the subscription, or be the subscription owner. 如需詳細資訊,請參閱RBAC:內建角色For more information, see RBAC: Built-in roles. 您可以使用 Azure 入口網站、PowerShell 或 REST API 來進行復原。You can recover by using the Azure portal, PowerShell, or the REST API. 您無法使用 Transact-sql。You can't use Transact-SQL.

還原時間點Point-in-time restore

您可以使用 Azure 入口網站、 PowerShellREST API,將獨立、集區或實例資料庫還原到較早的時間點。You can restore a standalone, pooled, or instance database to an earlier point in time by using the Azure portal, PowerShell, or the REST API. 要求可以針對還原的資料庫指定任何服務層級或計算大小。The request can specify any service tier or compute size for the restored database. 確定您要還原資料庫的伺服器上有足夠的資源。Ensure that you have sufficient resources on the server to which you are restoring the database. 完成時,還原會在與原始資料庫相同的伺服器上建立新的資料庫。When complete, the restore creates a new database on the same server as the original database. 還原的資料庫會根據其服務層級和計算大小,以標準費率計費。The restored database is charged at normal rates, based on its service tier and compute size. 在資料庫還原完成之前,您不會產生任何費用。You don't incur charges until the database restore is complete.

基於復原目的,您通常會將資料庫還原到較早的時間點。You generally restore a database to an earlier point for recovery purposes. 您可以將還原的資料庫視為原始資料庫的取代,或使用它做為資料來源來更新原始資料庫。You can treat the restored database as a replacement for the original database, or use it as a data source to update the original database.

  • 資料庫取代Database replacement

    如果您想要將還原的資料庫取代為原始資料庫,您應該指定原始資料庫的計算大小和服務層級。If you intend the restored database to be a replacement for the original database, you should specify the original database's compute size and service tier. 接著,您可以重新命名原始資料庫,並使用 T-sql 中的ALTER database命令,為還原的資料庫提供原始名稱。You can then rename the original database, and give the restored database the original name by using the ALTER DATABASE command in T-SQL.

  • 資料復原Data recovery

    如果您打算從還原的資料庫抓取資料,以從使用者或應用程式錯誤中復原,您必須撰寫並執行資料復原腳本,以從還原的資料庫中解壓縮資料,並將其套用至原始資料庫。If you plan to retrieve data from the restored database to recover from a user or application error, you need to write and execute a data recovery script that extracts data from the restored database and applies to the original database. 雖然還原作業可能要花很長的時間才能完成,但還原中的資料庫在整個還原過程中都會顯示在資料庫清單上。Although the restore operation may take a long time to complete, the restoring database is visible in the database list throughout the restore process. 如果您在還原期間刪除資料庫,還原作業將會取消,且不會向您收取未完成還原的資料庫費用。If you delete the database during the restore, the restore operation will be canceled and you will not be charged for the database that did not complete the restore.

使用 Azure 入口網站的時間點還原Point-in-time restore by using Azure portal

您可以從 Azure 入口網站中要還原之資料庫的 總覽 分頁,將單一 SQL database 或實例資料庫復原至某個時間點。You can recover a single SQL database or instance database to a point in time from the overview blade of the database you want to restore in the Azure portal.

單一 Azure SQL 資料庫Single Azure SQL database

若要使用 Azure 入口網站將單一或集區資料庫復原到某個時間點,請開啟 [資料庫總覽] 頁面,然後選取工具列上的 [還原]。To recover a single or pooled database to a point in time by using the Azure portal, open the database overview page, and select Restore on the toolbar. 選擇 [備份來源],然後選取將建立新資料庫的時間點備份點。Choose the backup source, and select the point-in-time backup point from which a new database will be created.

資料庫還原選項的螢幕擷取畫面

受控實例資料庫Managed instance database

若要使用 Azure 入口網站將受控實例資料庫復原到某個時間點,請開啟 [資料庫總覽] 頁面,然後選取工具列上的 [還原]。To recover a managed instance database to a point in time by using the Azure portal, open the database overview page, and select Restore on the toolbar. 選擇將建立新資料庫的時間點備份點。Choose the point-in-time backup point from which a new database will be created.

資料庫還原選項的螢幕擷取畫面

提示

若要以程式設計方式從備份還原資料庫,請參閱使用自動備份以程式設計方式執行復原。To programmatically restore a database from a backup, see Programmatically performing recovery using automated backups.

還原已刪除的資料庫Deleted database restore

您可以將已刪除的資料庫還原到相同 SQL Database 伺服器或相同受控實例上的刪除時間或較早的時間點。You can restore a deleted database to the deletion time, or an earlier point in time, on the same SQL Database server or the same managed instance. 您可以透過 Azure 入口網站、 PowerShellREST (createMode = Restore)來完成此動作。You can accomplish this through the Azure portal, PowerShell, or the REST (createMode=Restore). 您可以從備份建立新的資料庫,以還原已刪除的資料庫。You restore a deleted database by creating a new database from the backup.

重要

如果您刪除 Azure SQL Database 的伺服器或受控實例,其所有資料庫也會一併刪除,而且無法復原。If you delete an Azure SQL Database server or managed instance, all its databases are also deleted, and can't be recovered. 您無法還原已刪除的伺服器或受控實例。You can't restore a deleted server or managed instance.

使用 Azure 入口網站刪除的資料庫還原Deleted database restore by using the Azure portal

您可以從伺服器和實例資源的 Azure 入口網站還原已刪除的資料庫。You restore deleted databases from the Azure portal from the server and instance resource.

單一 Azure SQL 資料庫Single Azure SQL database

若要使用 Azure 入口網站將單一或集區刪除的資料庫復原到刪除時間,請開啟 [伺服器總覽] 頁面,然後選取 [已刪除的資料庫]。To recover a single or pooled deleted database to the deletion time by using the Azure portal, open the server overview page, and select Deleted databases. 選取您想要還原的已刪除資料庫,然後輸入將使用從備份還原之資料所建立的新資料庫名稱。Select a deleted database that you want to restore, and type the name for the new database that will be created with data restored from the backup.

還原已刪除 Azure SQL database 的螢幕擷取畫面

受控實例資料庫Managed instance database

若要使用 Azure 入口網站復原受控資料庫,請開啟 [受控實例總覽] 頁面,然後選取 [已刪除的資料庫]。To recover a managed database by using the Azure portal, open the managed instance overview page, and select Deleted databases. 選取您想要還原的已刪除資料庫,然後輸入將使用從備份還原之資料所建立的新資料庫名稱。Select a deleted database that you want to restore, and type the name for the new database that will be created with data restored from the backup.

還原已刪除的 Azure SQL 實例資料庫的螢幕擷取畫面

使用 PowerShell 刪除資料庫還原Deleted database restore by using PowerShell

使用下列範例腳本,使用 PowerShell 還原 Azure SQL Database 和受控實例的已刪除資料庫。Use the following sample scripts to restore a deleted database for Azure SQL Database and a managed instance by using PowerShell.

單一 Azure SQL 資料庫Single Azure SQL database

如需示範如何還原已刪除的 Azure SQL 資料庫的 PowerShell 腳本範例,請參閱使用 PowerShell 還原 SQL 資料庫For a sample PowerShell script showing how to restore a deleted Azure SQL database, see Restore a SQL database using PowerShell.

受控實例資料庫Managed instance database

如需示範如何還原已刪除實例資料庫的 PowerShell 腳本範例,請參閱使用 PowerShell 在受控實例上還原已刪除的資料庫For a sample PowerShell script showing how to restore a deleted instance database, see Restore deleted database on managed instance using PowerShell.

提示

若要以程式設計方式還原已刪除的資料庫,請參閱使用自動備份以程式設計方式執行復原To programmatically restore a deleted database, see Programmatically performing recovery using automated backups.

異地還原Geo-restore

您可以從最新的異地複寫備份,在任何 Azure 區域中的任何伺服器上還原 SQL Database。You can restore a SQL database on any server in any Azure region from the most recent geo-replicated backups. 異地還原使用異地複寫備份作為其來源。Geo-restore uses a geo-replicated backup as its source. 即使因為中斷而無法存取資料庫或資料中心,也可以要求異地還原。You can request geo-restore even if the database or datacenter is inaccessible due to an outage.

當您的資料庫因為裝載區域中的事件而無法使用時,異地還原就是預設的復原選項。Geo-restore is the default recovery option when your database is unavailable because of an incident in the hosting region. 您可以將資料庫還原到任何其他區域中的伺服器。You can restore the database to a server in any other region. 在建立備份時以及對它進行異地複寫到不同區域中的 Azure Blob 之間會有延遲。There is a delay between when a backup is taken and when it is geo-replicated to an Azure blob in a different region. 因此,還原後的資料庫最多可達一小時的原始資料庫。As a result, the restored database can be up to one hour behind the original database. 下圖顯示從另一個區域中的最後一個可用備份來還原資料庫。The following illustration shows a database restore from the last available backup in another region.

異地還原的圖形

使用 Azure 入口網站進行異地還原Geo-restore by using the Azure portal

在 Azure 入口網站中,您可以建立新的單一或受控實例資料庫,然後選取可用的異地還原備份。From the Azure portal, you create a new single or managed instance database, and select an available geo-restore backup. 新建立的資料庫包含異地還原的備份資料。The newly created database contains the geo-restored backup data.

單一 Azure SQL 資料庫Single Azure SQL database

若要從您選擇的區域和伺服器中的 Azure 入口網站異地還原單一 SQL 資料庫,請遵循下列步驟:To geo-restore a single SQL database from the Azure portal in the region and server of your choice, follow these steps:

  1. 從 [儀表板] 中,選取 [新增] > 建立 SQL DatabaseFrom Dashboard, select Add > Create SQL Database. 在 [基本] 索引標籤上,輸入所需的資訊。On the Basics tab, enter the required information.

  2. 選取 [其他設定]。Select Additional settings.

  3. 針對 [使用現有的資料],選取 [備份]。For Use existing data, select Backup.

  4. 針對 [備份],從可用的異地還原備份清單中選取備份。For Backup, select a backup from the list of available geo-restore backups.

    建立 SQL Database 選項的螢幕擷取畫面

完成從備份建立新資料庫的程式。Complete the process of creating a new database from the backup. 當您建立單一 Azure SQL 資料庫時,它會包含還原的異地還原備份。When you create the single Azure SQL database, it contains the restored geo-restore backup.

受控實例資料庫Managed instance database

若要從 Azure 入口網站將受控實例資料庫異地還原至您所選區域中的現有受控實例,請選取您要在其上還原資料庫的受控實例。To geo-restore a managed instance database from the Azure portal to an existing managed instance in a region of your choice, select a managed instance on which you want a database to be restored. 請遵循下列步驟:Follow these steps:

  1. 選取 [新增資料庫]。Select New database.

  2. 輸入想要的資料庫名稱。Type a desired database name.

  3. 在 [使用現有資料] 底下,選取 [備份]。Under Use existing data, select Backup.

  4. 從可用的異地還原備份清單中選取備份。Select a backup from the list of available geo-restore backups.

    新資料庫選項的螢幕擷取畫面

完成建立新資料庫的程式。Complete the process of creating a new database. 當您建立實例資料庫時,它會包含還原的異地還原備份。When you create the instance database, it contains the restored geo-restore backup.

使用 PowerShell 進行異地還原Geo-restore by using PowerShell

單一 Azure SQL 資料庫Single Azure SQL database

如需說明如何針對單一 SQL 資料庫執行異地還原的 PowerShell 腳本,請參閱使用 powershell 將 AZURE SQL 單一資料庫還原至較早的時間點For a PowerShell script that shows how to perform geo-restore for a single SQL database, see Use PowerShell to restore an Azure SQL single database to an earlier point in time.

受控實例資料庫Managed instance database

如需示範如何針對受控實例資料庫執行異地還原的 PowerShell 腳本,請參閱使用 powershell 將受控實例資料庫還原到另一個地理區域For a PowerShell script that shows how to perform geo-restore for a managed instance database, see Use PowerShell to restore a managed instance database to another geo-region.

異地還原考慮Geo-restore considerations

您無法在異地次要資料庫上執行還原時間點。You can't perform a point-in-time restore on a geo-secondary database. 您只能在主資料庫上執行此動作。You can only do so on a primary database. 如需使用異地還原來從中斷復原的詳細資訊,請參閱從中斷復原For detailed information about using geo-restore to recover from an outage, see Recover from an outage.

重要

異地還原是 SQL Database 中最基本的嚴重損壞修復解決方案。Geo-restore is the most basic disaster recovery solution available in SQL Database. 它依賴自動建立的異地複寫備份,且復原點目標(RPO)等於1小時,以及最多12小時的預估復原時間。It relies on automatically created geo-replicated backups with recovery point objective (RPO) equal to 1 hour, and the estimated recovery time of up to 12 hours. 它不保證在區域中斷之後,目的地區域將會擁有還原資料庫的容量,因為需求可能會明顯增加。It doesn't guarantee that the target region will have the capacity to restore your databases after a regional outage, because a sharp increase of demand is likely. 如果您的應用程式使用相對較小的資料庫,而且對企業而言並不重要,則異地還原是適當的嚴重損壞修復解決方案。If your application uses relatively small databases and is not critical to the business, geo-restore is an appropriate disaster recovery solution. 對於需要大型資料庫且必須確保商務持續性的商務關鍵應用程式,請使用自動容錯移轉群組For business-critical applications that require large databases and must ensure business continuity, use Auto-failover groups. 它提供較低的 RPO 和復原時間目標,而且一律保證容量。It offers a much lower RPO and recovery time objective, and the capacity is always guaranteed. 如需商務持續性選項的詳細資訊,請參閱商務持續性概觀For more information on business continuity choices, see Overview of business continuity.

使用自動備份以程式設計方式執行復原Programmatically performing recovery by using automated backups

您也可以使用 Azure PowerShell 或 REST API 進行復原。You can also use Azure PowerShell or the REST API for recovery. 下表描述可用的命令集。The following tables describe the set of commands available.

PowerShellPowerShell

注意

本文已更新為使用新的 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 Database 仍然支援 PowerShell Azure Resource Manager 模組,但所有未來的開發都是針對 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,請參閱AzureRMFor these cmdlets, see AzureRM.Sql. Az 模組和 AzureRm 模組中命令的引數相當於相同的範圍。Arguments for the commands in the Az module and in AzureRm modules are to a great extent identical.

單一 Azure SQL 資料庫Single Azure SQL database

若要還原獨立或集區資料庫,請參閱restore-set-azsqldatabase 搭配To restore a standalone or pooled database, see Restore-AzSqlDatabase.

CmdletCmdlet 描述Description
Get-AzSqlDatabaseGet-AzSqlDatabase 取得一或多個資料庫。Gets one or more databases.
Get-AzSqlDeletedDatabaseBackupGet-AzSqlDeletedDatabaseBackup 取得可還原的已刪除資料庫。Gets a deleted database that you can restore.
Get-AzSqlDatabaseGeoBackupGet-AzSqlDatabaseGeoBackup 取得資料庫的異地備援備份。Gets a geo-redundant backup of a database.
Restore-AzSqlDatabaseRestore-AzSqlDatabase 還原 SQL Database。Restores a SQL database.

提示

如需示範如何執行資料庫還原時間點的範例 PowerShell 腳本,請參閱使用 PowerShell 還原 SQL 資料庫For a sample PowerShell script that shows how to perform a point-in-time restore of a database, see Restore a SQL database using PowerShell.

受控實例資料庫Managed instance database

若要還原受控實例資料庫,請參閱AzSqlInstanceDatabaseTo restore a managed instance database, see Restore-AzSqlInstanceDatabase.

CmdletCmdlet 描述Description
Get-AzSqlInstanceGet-AzSqlInstance 取得一或多個受控實例。Gets one or more managed instances.
AzSqlInstanceDatabaseGet-AzSqlInstanceDatabase 取得實例資料庫。Gets an instance database.
Restore-AzSqlInstanceDatabaseRestore-AzSqlInstanceDatabase 還原實例資料庫。Restores an instance database.

REST APIREST API

若要使用 REST API 來還原單一或集區資料庫:To restore a single or pooled database by using the REST API:

APIAPI 描述Description
REST (createMode=Recovery)REST (createMode=Recovery) 還原資料庫。Restores a database.
取得建立或更新資料庫狀態Get Create or Update Database Status 傳回還原作業期間的狀態。Returns the status during a restore operation.

Azure CLIAzure CLI

單一 Azure SQL 資料庫Single Azure SQL database

若要使用 Azure CLI 來還原單一或集區資料庫,請參閱az sql db restoreTo restore a single or pooled database by using the Azure CLI, see az sql db restore.

受控實例資料庫Managed instance database

若要使用 Azure CLI 來還原受控實例資料庫,請參閱az sql midb restoreTo restore a managed instance database by using the Azure CLI, see az sql midb restore.

SummarySummary

自動備份可在發生使用者和應用程式錯誤、意外刪除資料庫和長時間中斷時保護您的資料庫。Automatic backups protect your databases from user and application errors, accidental database deletion, and prolonged outages. 所有服務層級和計算大小都可以取得此內建功能。This built-in capability is available for all service tiers and compute sizes.

後續步驟Next steps