Azure Synapse SQL 池中的备份和还原Backup and restore in Azure Synapse SQL pool

了解如何在 Azure Synapse SQL 池中使用备份和还原。Learn how to use backup and restore in Azure Synapse SQL pool. 使用 SQL 池还原点可以恢复或复制主要区域中旧状态的数据仓库。Use SQL pool restore points to recover or copy your data warehouse to a previous state in the primary region. 使用数据仓库异地冗余备份可还原到不同的地理区域。Use data warehouse geo-redundant backups to restore to a different geographical region.

什么是数据仓库快照What is a data warehouse snapshot

数据仓库快照会创建一个还原点,利用该还原点可将数据仓库恢复或复制到以前的状态。A data warehouse snapshot creates a restore point you can leverage to recover or copy your data warehouse to a previous state. 由于 SQL 池属于分布式系统,因此数据仓库快照包含许多位于 Azure 存储中的文件。Since SQL pool is a distributed system, a data warehouse snapshot consists of many files that are located in Azure storage. 快照捕获数据仓库中存储的数据的增量更改。Snapshots capture incremental changes from the data stored in your data warehouse.

数据仓库还原是基于现有数据仓库或已删除数据仓库的还原点创建的新数据仓库。A data warehouse restore is a new data warehouse that is created from a restore point of an existing or deleted data warehouse. 还原数据仓库是任何业务连续性和灾难恢复策略的基本组成部分,因为数据库还原可以在意外损坏或删除数据后重新创建数据。Restoring your data warehouse is an essential part of any business continuity and disaster recovery strategy because it re-creates your data after accidental corruption or deletion. 此外,数据仓库是出于测试或开发目的创建数据仓库副本的强大机制。Data warehouse is also a powerful mechanism to create copies of your data warehouse for test or development purposes. SQL 池还原速度因数据库大小以及源和目标数据仓库的位置而异。SQL pool restore rates can vary depending on the database size and location of the source and target data warehouse.

自动还原点Automatic Restore Points

快照是用于创建还原点的内置功能。Snapshots are a built-in feature that creates restore points. 无需启用此功能。You do not have to enable this capability. 但是,SQL 池应处于活动状态才能创建还原点。However, the SQL pool should be in an active state for restore point creation. 如果 SQL 池经常暂停,则可能不会创建自动还原点,因此请确保在暂停 SQL 池之前创建用户定义的还原点。If the SQL pool is paused frequently, automatic restore points may not be created so make sure to create user-defined restore point before pausing the SQL pool. 用户目前无法删除自动还原点,因为服务使用这些还原点来维护 SLA 以进行恢复。Automatic restore points currently cannot be deleted by users as the service uses these restore points to maintain SLAs for recovery.

系统会全天捕获数据仓库的快照,创建可以使用七天的还原点。Snapshots of your data warehouse are taken throughout the day creating restore points that are available for seven days. 无法更改此保留期。This retention period cannot be changed. SQL 池支持八小时恢复点目标 (RPO)。SQL pool supports an eight-hour recovery point objective (RPO). 可以根据过去七天捕获的任意一个快照,还原主要区域中的数据仓库。You can restore your data warehouse in the primary region from any one of the snapshots taken in the past seven days.

若要查看上一个快照的启动时间,可对联机 SQL 池运行此查询。To see when the last snapshot started, run this query on your online SQL pool.

select   top 1 *
from     sys.pdw_loader_backup_runs
order by run_id desc

用户定义的还原点User-Defined Restore Points

使用此功能,可以在大型修改之前和之后手动触发快照,以便创建数据仓库的还原点。This feature enables you to manually trigger snapshots to create restore points of your data warehouse before and after large modifications. 此功能可确保在出现工作负荷中断或用户错误的情况下,还原点在逻辑上是一致的,这样可以提供额外的数据保护,缩短恢复时间。This capability ensures that restore points are logically consistent, which provides additional data protection in case of any workload interruptions or user errors for quick recovery time. 用户定义的还原点可以使用七天,然后系统会替你将它自动删除。User-defined restore points are available for seven days and are automatically deleted on your behalf. 无法更改用户定义的还原点的保留期。You cannot change the retention period of user-defined restore points. 无论在任何时间点,均会保证 42 个用户定义的还原点,因此,它们必须在创建另一个还原点之前删除42 user-defined restore points are guaranteed at any point in time so they must be deleted before creating another restore point. 可以通过 PowerShell 或 Azure 门户触发快照来创建用户定义的还原点。You can trigger snapshots to create user-defined restore points through PowerShell or the Azure portal.

还原点保留期Restore point retention

下面列出了还原点保留期的详细信息:The following lists details for restore point retention periods:

  1. SQL 池会在达到 7 天保留期并且总共至少有 42 个还原点(包括用户定义的还原点和自动还原点)时删除还原点。SQL pool deletes a restore point when it hits the 7-day retention period and when there are at least 42 total restore points (including both user-defined and automatic).
  2. 暂停 SQL 池时不会创建快照。Snapshots are not taken when a SQL pool is paused.
  3. 还原点的存在时长是从创建还原点的时间算起的绝对日历天数(包括 SQL 池暂停的时间)。The age of a restore point is measured by the absolute calendar days from the time the restore point is taken including when the SQL pool is paused.
  4. 在任何时间点,SQL 池均保证能够存储最多 42 个用户定义的还原点和 42 个自动还原点,只要这些还原点尚未达到 7 天保留期的限制At any point in time, a SQL pool is guaranteed to be able to store up to 42 user-defined restore points and 42 automatic restore points as long as these restore points have not reached the 7-day retention period
  5. 如果创建了快照,然后 SQL 池暂停 7 天以上的时间,然后进行了恢复,那么还原点会持续存在,直到总共有 42 个还原点(包括用户定义的还原点和自动还原点)If a snapshot is taken, the SQL pool is then paused for greater than 7 days, and then resumes, the restore point will persist until there are 42 total restore points (including both user-defined and automatic)

删除 SQL 池时的快照保留Snapshot retention when a SQL pool is dropped

删除 SQL 池时,系统会创建最终的快照并将其保存七天。When you drop a SQL pool, a final snapshot is created and saved for seven days. 可以将 SQL 池还原至删除时所创建的最终还原点。You can restore the SQL pool to the final restore point created at deletion. 如果 SQL 池是在暂停状态下删除的,则不会创建快照。If the SQL pool is dropped in a paused state, no snapshot is taken. 在这种情况下,请确保在删除 SQL 池之前创建用户定义的还原点。In that scenario, make sure to create a user-defined restore point before dropping the SQL pool.


如果删除托管 SQL 池的服务器,则属于该服务器的所有数据库也会被删除且不可恢复。If you delete the server hosting a SQL pool, all databases that belong to the server are also deleted and cannot be recovered. 无法还原已删除的服务器。You cannot restore a deleted server.

异地备份和灾难恢复Geo-backups and disaster recovery

每日在配对的数据中心创建一次异地备份。A geo-backup is created once per day to a paired data center. 异地还原的 RPO 为 24 小时。The RPO for a geo-restore is 24 hours. 可以将异地备份还原到支持 SQL 池的任何其他区域的服务器。You can restore the geo-backup to a server in any other region where SQL pool is supported. 使用异地备份可在无法访问主要区域中的还原点时还原数据仓库。A geo-backup ensures you can restore data warehouse in case you cannot access the restore points in your primary region.


如果希望异地备份的 RPO 更短,请在此处为此功能投票。If you require a shorter RPO for geo-backups, vote for this capability here. 此外,可以创建用户定义的还原点,然后从新建的还原点还原到其他区域中的新数据仓库。You can also create a user-defined restore point and restore from the newly created restore point to a new data warehouse in a different region. 还原后,数据仓库将会联机,可以无限期将其暂停,以节省计算成本。Once you have restored, you have the data warehouse online and can pause it indefinitely to save compute costs. 暂停的数据库按 Azure 高级存储费率收取存储费用。The paused database incurs storage charges at the Azure Premium Storage rate. 如需数据仓库的活动副本,可以执行恢复,只需花费几分钟时间。Should you need an active copy of the data warehouse, you can resume which should take only a few minutes.

备份和还原成本Backup and restore costs

Azure 帐单上将列出存储的明细项目,以及灾难恢复存储的明细项目。You will notice the Azure bill has a line item for Storage and a line item for Disaster Recovery Storage. 存储费用是在主要区域中存储数据的费用加上快照捕获增量更改的费用。The storage charge is the total cost for storing your data in the primary region along with the incremental changes captured by snapshots. 有关快照收费方式的更详细说明,请参阅了解快照如何收取费用For a more detailed explanation of how snapshots are charged, refer to Understanding how Snapshots Accrue Charges. 异地冗余费用是指存储异地备份的费用。The geo-redundant charge covers the cost for storing the geo-backups.

主数据仓库和 7 天快照更改的总费用根据 TB 数的舍入近似值计算。The total cost for your primary data warehouse and seven days of snapshot changes is rounded to the nearest TB. 例如,如果数据仓库为 1.5 TB,快照捕获了 100 GB,则会根据 Azure 高级存储费率计收 2 TB 数据的费用。For example, if your data warehouse is 1.5 TB and the snapshots captures 100 GB, you are billed for 2 TB of data at Azure Premium Storage rates.

如果使用的是异地冗余存储,则会单独收取异地存储费。If you are using geo-redundant storage, you receive a separate storage charge. 异地冗余存储按标准的读取访问异地冗余存储 (RA-GRS) 费率计费。The geo-redundant storage is billed at the standard Read-Access Geographically Redundant Storage (RA-GRS) rate.

有关 Azure Synapse 定价的详细信息,请参阅 Azure Synapse 定价For more information about Azure Synapse pricing, see Azure Synapse pricing. 跨区域还原时,不会对数据流出量收费。You are not charged for data egress when restoring across regions.

从还原点还原Restoring from restore points

每个快照创建一个代表快照开始时间的还原点。Each snapshot creates a restore point that represents the time the snapshot started. 如果要还原数据仓库,请选择一个还原点,并发出还原命令。To restore a data warehouse, you choose a restore point and issue a restore command.

可以保留还原的数据仓库和当前的数据仓库,也可以删除其中一个。You can either keep the restored data warehouse and the current one, or delete one of them. 如果希望使用已还原数据仓库替换当前数据仓库,可使用 ALTER DATABASE(SQL 池)的 MODIFY NAME 选项将其重命名。If you want to replace the current data warehouse with the restored data warehouse, you can rename it using ALTER DATABASE (SQL pool) with the MODIFY NAME option.

若要还原数据仓库,请参阅还原 SQL 池To restore a data warehouse, see Restore a SQL pool.

若要还原已删除或已暂停的数据仓库,则可以创建支持票证To restore a deleted or paused data warehouse, you can create a support ticket.

异地冗余还原Geo-redundant restore

可以将 SQL 池还原到支持所选性能级别的 SQL 池的任何区域。You can restore your SQL pool to any region supporting SQL pool at your chosen performance level.


若要执行异地冗余还原,不能选择退出此功能。To perform a geo-redundant restore you must not have opted out of this feature.

后续步骤Next steps

有关还原点的详细信息,请参阅用户定义的还原点For more information about restore points, see User-defined restore points