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

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

  • 在同一部 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 伺服器上任何 LTR 備份建立新的資料庫。If you configured backup long-term retention, you can also create a new database from any LTR backup on any SQL Database server.

重要

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

使用標準或進階服務層級時,還原的資料庫會在下列情況產生額外的儲存體成本:When using Standard or Premium service tier, a restored database incurs an extra storage cost under the following conditions:

  • 如果資料庫大小上限大於 500 GB,會將 P11–P15 還原至 S4-S12 或 P1–P6。Restore of P11–P15 to S4-S12 or P1–P6 if the database max size is greater than 500 GB.
  • 如果資料庫大小上限大於 250 GB,會將 P1–P6 還原至 S4-S12。Restore of P1–P6 to S4-S12 if the database max size is greater than 250 GB.

額外的成本是 icurred 時還原之資料庫的大小上限大於包含目標資料庫的服務層和效能層級的儲存體數量。The extra cost is icurred when the max size of the restored database is greater than the amount of storage included with the target database's service tier and performance level. 額外的儲存體佈建超過包含的數量會產生額外費用。The extra storage provisioned above the included amount is charged extra. 如需有關額外儲存體的價格詳細資訊,請參閱 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 max database size to the included amount.

注意

當您建立資料庫複本時,會使用自動資料庫備份Automated database backups are used when you create a database copy.

復原時間Recovery time

使用自動資料庫備份還原資料庫的復原時間會受到一些因素所影響:The recovery time to restore a database using automated database backups is impacted 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 and/or very active database, the restore may take several hours. 如果某區域中的中斷延長,其他區域可能要處理大量的異地還原要求。If there is prolonged outage in a region, it is possible that there are large numbers of geo-restore requests being processed by other regions. 如果有許多要求,則該區域中資料庫的復原時間可能會增加。When there are many requests, the recovery time may increase for databases in that region. 大多數資料庫還原都會在 12 小時內完成。Most database restores complete in less than 12 hours.

單一訂用帳戶中,有限制的並行還原要求數目。For a single subscription, there are limitations on 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

目前沒有內建方法來還原整部伺服器。Currently there isn't a built-in method to restore the entire server. Azure SQL Database:完整伺服器復原指令碼是如何完成這項工作的範例。The Azure SQL Database: Full Server Recovery script is an example of how you can accomplish this task.

重要

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

還原時間點Point-in-time restore

您可以還原獨立、 集區,或執行個體中使用 Azure 入口網站中,時間的較早時間點的資料庫PowerShell,或有REST APIYou can restore a standalone, pooled, or instance database to an earlier point in time using the Azure portal, PowerShell, or the REST API. 要求可以指定任何服務層,或計算已還原的資料庫的大小。The request can specify any service tier or compute size for the restored database. 請確定在您要還原資料庫的目標伺服器上具有足夠的資源。Ensure you have sufficient resources on the server to which you are restoring the database. 完成後,就會建立新的資料庫與原始資料庫相同的伺服器上。Once complete, a new database will be created on the same server as the original database. 還原的資料庫將會收費根據其服務層和計算大小的一般費率計費。The restored database will be charged at normal rates based on its service tier and compute size. 在完成資料庫還原之前,不會產生任何費用。You do not 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 source data to update the original database.

  • 資料庫取代Database replacement

    如果還原的資料庫要做為原始資料庫的替代品,您應該指定 orinal 資料庫的計算大小和服務層級。If the restored database is intended as a replacement for the original database, you should specify the orinal database's compute size and service tier. 您可以重新命名原始資料庫並讓原來的名稱使用的已還原的資料庫ALTER DATABASE T-SQL 命令。You can then rename the original database and give the restored database the original name 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 入口網站來將單一、集區或執行個體資料庫復原至某個時間點,請開啟資料庫頁面,然後按一下工具列上的 [還原] 。To recover a single, pooled, or instance database to a point in time using the Azure portal, open the page for your database and click Restore on the toolbar.

point-in-time-restore

重要

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

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

您可以刪除時間或較早時間點還原已刪除的資料庫,在使用 Azure 入口網站中,相同的 SQL Database 伺服器上的時間內PowerShell,或有REST (createMode = Restore)You can restore a deleted database to the deletion time or an earlier point in time on the same SQL Database server using the Azure portal, PowerShell, or the REST (createMode=Restore). 您可以使用 PowerShell 在受控執行個體上還原已刪除的資料庫You can restore deleted database on Managed Instance using PowerShell.

提示

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

重要

如果您刪除 Azure SQL Database 伺服器執行個體,其所有資料庫也會一併刪除且無法加以復原。If you delete an Azure SQL Database server instance, all its databases are also deleted and cannot be recovered. 目前不支援還原已刪除的伺服器。There is currently no support for restoring a deleted server.

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

若要復原已刪除的資料庫,使用 Azure 入口網站中,開啟您的伺服器,並在 [作業] 區域中的頁面中,按一下已刪除的資料庫To recover a deleted database using the Azure portal, open the page for your server and in the Operations area, click Deleted databases.

deleted-database-restore-1

deleted-database-restore-2

重要

若要以程式設計方式從備份還原已刪除的資料庫,請參閱使用自動備份以程式設計方式執行復原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. 它可以在即使資料庫或資料中心是因為中斷而無法存取要求。It can be requested 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 orignal database. 下圖顯示從另一個區域中的上次可用備份來還原資料庫。The following illustration shows restore of the database from the last available backup in another region.

異地還原

提示

如需示範如何執行異地還原的 PowerShell 指令碼範例,請參閱使用 PowerShell 還原 SQL 資料庫For a sample PowerShell script showing how to perform a geo-restore, see Restore a SQL database using PowerShell.

目前不支援異地次要資料庫上的時間點復原。Point-in-time restore on a geo-secondary is not currently supported. 只有在主要資料庫上才可進行時間點復原。Point-in-time restore can be done only 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 RPO = 1 hour and the estimated recovery time of up to 12 hours. 它並不保證目標區域,必須在區域 ourage 之後還原資料庫,因為有可能會有清晰的增加需求的容量。It does not guarantee that the target region will have the capacity to restore your database(s) after a regional ourage because a sharp increase of demand will be likely. 對於非商務關鍵應用程式使用相對較小的資料庫,異地還原會是適當的災害復原解決方案。For non business critical application that use relatively small databases, geo-restore is an appropriate disaster recovery solution. Busniess 重要應用程式,使用大型資料庫,且必須確保商務持續性,您應該使用自動容錯移轉群組For busniess critical applications that use large databases and must ensure business continuity, you should use Auto-failover groups. 它提供較低的 RPO 和 RTO,,而且一律保證容量。It offers a much lower RPO and RTO, and the capacity is always guaranteed. 如需商務持續性選項的詳細資訊,請參閱商務持續性概觀For more information on business continuity choices, see Overview of business continuity.

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

若要使用 Azure 入口網站異地還原處於其以 DTU 為基礎的模型保留期限以虛擬核心為基礎的模型保留期限的資料庫,請開啟 [SQL Database] 頁面,然後按一下 [新增] 。To geo-restore a database during its DTU-based model retention period or vCore-based model retention period using the Azure portal, open the SQL Databases page and then click Add. 在 [選取來源] 文字方塊中,選取 [備份] 。In the Select source text box, select Backup. 指定要在區域和您選擇的伺服器上,執行復原的來源備份。Specify the backup from which to perform the recovery in the region and on the server of your choice.

注意

使用 Azure 入口網站進行異地還原不適用於受控執行個體。Geo-restore using the Azure portal is not available in Managed Instance. 請改用 PowerShell。Please use PowerShell instead.

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

如先前所述,除了 Azure 入口網站之外,還可使用 Azure PowerShell 或 REST API,以程式設計方式執行資料庫復原。As previously discussed, in addition to the Azure portal, database recovery can be performed programmatically using Azure PowerShell or the REST API. 下表描述可用的命令集。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.

重要

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.

REST APIREST API

使用 REST API 來還原單一或集區資料庫:To restore a single or pooled database 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

總結Summary

自動備份可在發生使用者和應用程式錯誤、意外刪除資料庫和長時間中斷時保護您的資料庫。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