Azure SQL 数据仓库中的备份和还原Backup and restore in Azure SQL Data Warehouse

了解如何在 Azure SQL 数据仓库中使用备份和还原。Learn how to use backup and restore in Azure SQL Data Warehouse. 使用数据仓库还原点来恢复或复制数据仓库到主要区域中的之前的状态。Use data warehouse 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 Data Warehouse 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 Data Warehouse restore rates can vary depending on the database size and location of the source and target data warehouse. 就同一区域的平均还原速度来说,通常需要大约 20 分钟才能完成还原。On average within the same region, restore rates typically take around 20 minutes.

自动还原点Automatic Restore Points

快照是创建还原点的服务的内置功能。Snapshots are a built-in feature of the service that creates restore points. 无需启用此功能。You do not have to enable this capability. 目前用户无法删除自动还原点,因为服务使用这些还原点来维持恢复 SLA。Automatic restore points currently cannot be deleted by users where the service uses these restore points to maintain SLAs for recovery.

SQL 数据仓库为数据仓库创建全天快照,并创建可用 7 天的还原点。SQL Data Warehouse takes snapshots of your data warehouse throughout the day creating restore points that are available for seven days. 无法更改此保留期。This retention period cannot be changed. SQL 数据仓库支持八小时恢复点目标 (RPO)。SQL Data Warehouse 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 Data Warehouse.

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 Data Warehouse 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. 数据仓库暂停时不会创建快照Snapshots are not taken when a data warehouse is paused
  3. 还原点的存在时长是从创建还原点的时间算起的绝对日历天数(包括数据仓库暂停的时间)The age of a restore point is measured by the absolute calendar days from the time the restore point is taken including when the data warehouse is paused
  4. 在任何时间点,数据仓库均保证能够存储最多 42 个用户定义的还原点和 42 个自动还原点,只要这些还原点尚未达到 7 天保留期At any point in time, a data warehouse 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. 如果创建了快照,然后数据仓库暂停 7 天以上的时间,然后进行了恢复,那么还原点可能持续存在,直到总共有 42 个还原点(包括用户定义的还原点和自动还原点)If a snapshot is taken, the data warehouse is then paused for greater than 7 days, and then resumes, it is possible for restore point to persist until there are 42 total restore points (including both user-defined and automatic)

删除数据仓库时的快照保留期Snapshot retention when a data warehouse is dropped

删除数据仓库时,SQL 数据仓库将创建一个最终快照并保存七天。When you drop a data warehouse, SQL Data Warehouse creates a final snapshot and saves it for seven days. 可以将数据仓库还原至删除时所创建的最终还原点。You can restore the data warehouse to the final restore point created at deletion.


如果删除某个逻辑 SQL Server 实例,则属于该实例的所有数据库也会删除,且无法恢复。If you delete a logical SQL server instance, all databases that belong to the instance are also deleted and cannot be recovered. 无法还原已删除的服务器。You cannot restore a deleted server.

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

SQL 数据仓库每天执行一次异地备份,将内容备份到配对的数据中心。SQL Data Warehouse performs a geo-backup 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 Data Warehouse is supported. 使用异地备份可在无法访问主要区域中的还原点时还原数据仓库。A geo-backup ensures you can restore data warehouse in case you cannot access the restore points in your primary region.

默认情况下,异地备份处于启用状态。Geo-backups are on by default. 如果数据仓库为 Gen1,则可按需选择退出If your data warehouse is Gen1, you can opt out if you wish. 不能为 Gen2 禁用异地备份,因为数据保护是固有的保证。You cannot opt out of geo-backups for Gen2 as data protection is a built-in guaranteed.

备份和还原成本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.

有关 SQL 数据仓库定价的详细信息,请参阅 SQL 数据仓库定价For more information about SQL Data Warehouse pricing, see SQL Data Warehouse 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(Azure SQL 数据仓库)的“修改名称”选项对其进行重命名。If you want to replace the current data warehouse with the restored data warehouse, you can rename it using ALTER DATABASE (Azure SQL Data Warehouse) with the MODIFY NAME option.

若要还原数据仓库,请参阅使用 Azure 门户还原数据仓库使用 PowerShell 还原数据仓库使用 REST API 还原数据仓库To restore a data warehouse, see Restore a data warehouse using the Azure portal, Restore a data warehouse using PowerShell, or Restore a data warehouse using REST APIs.

异地冗余还原Geo-redundant restore

将数据仓库还原到支持所选性能级别的 SQL 数据仓库的任何区域。You can restore your data warehouse to any region supporting SQL Data Warehouse 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 disaster planning, see Business continuity overview