Azure 中数据库文件的文件快照备份File-Snapshot Backups for Database Files in Azure

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

SQL ServerSQL Server 文件快照备份使用 Azure 快照提供近乎即时的备份,并且更快速地还原使用 Azure Blob 存储服务存储的数据库文件。File-snapshot backup uses Azure snapshots to provide nearly instantaneous backups and quicker restores for database files stored using the Azure Blob storage service. 此功能可用于简化备份和还原策略。This capability enables you to simplify your backup and restore policies. 有关实时演示,请参阅 时间点还原的演示For a live demo, see Demo of Point in Time Restore. 有关使用 Azure Blob 存储服务存储数据库文件的详细信息,请参阅 Microsoft Azure 中的 SQL Server 数据文件For more information on storing database files using the Azure Blob storage service, see SQL Server Data Files in Microsoft Azure.

快照备份体系结构关系图snapshot backup architectural diagram

下载Download

  • 若要下载 SQL Server 2016 (13.x)SQL Server 2016 (13.x),请转到 评估中心To download SQL Server 2016 (13.x)SQL Server 2016 (13.x), go to Evaluation Center.

  • 已经拥有 Azure 帐户?Have an Azure account? 然后转到 此处 启动装有 SQL ServerSQL Server 的虚拟机。Then go Here to spin up a Virtual Machine with SQL ServerSQL Server already installed.

使用 Azure 快照备份 Azure 中存储的数据库文件Using Azure snapshots to back up database files stored in Azure

什么是 SQL ServerSQL Server 文件快照备份What is a SQL ServerSQL Server file-snapshot backup

文件快照备份包括一组 blob 的 Azure 快照(包含数据库文件)以及备份文件(包含指向这些文件快照的指针)。A file-snapshot backup consists of a set of Azure snapshots of the blobs containing the database files plus a backup file containing pointers to these file-snapshots. 每个文件快照都会与基本 blob 一起存储在容器中。Each file-snapshot is stored in the container with the base blob. 你可以指定要将备份文件本身写入到 URL、磁盘还是磁带。You can specify that the backup file itself to be written to URL, disk or tape. 建议备份到 URL。Backup to URL is recommended. 有关备份的详细信息,请参阅 BACKUP (Transact-SQL),有关备份到 URL 的详细信息,请参阅 SQL Server 备份到 URLFor more information on backing up, see BACKUP (Transact-SQL) and on backing up to URL, see SQL Server Backup to URL.

快照功能的体系结构architecture of snapshot feature

删除基本 blob 会使备份集失效,系统将阻止你删除包含文件快照的 blob(除非你明确选择要删除 blob 及其所有文件快照)。Deleting the base blob will invalidate the backup set and you are prevented from dropping a blob that contains file-snapshots (unless you expressly choose to delete a blob with all of its file-snapshots). 而且,删除数据库或数据文件不会删除基本 blob 或其任何文件快照。Furthermore, dropping a database or a data file does not delete the base blob or any of its file-snapshots. 另外,删除备份文件不会删除备份集中的任何文件快照。Also, deleting the backup file does not delete any of the file-snapshots in the backup set. 若要删除文件快照备份集,请使用 sys.sp_delete_backup 系统存储过程。To delete a file-snapshot backup set, use the sys.sp_delete_backup system stored procedure.

完整数据库备份: 使用文件快照备份执行完整数据库备份将创建构成该数据库的每个数据和日志文件的 Azure 快照、建立事务日志备份链,并将文件快照的位置写入到备份文件。Full database backup: Performing a full database backup using file-snapshot backup creates an Azure snapshot of each data and log file comprising the database, establishes the transaction log backup chain, and writes the location of the file-snapshots into the backup file.

事务日志备份: 使用文件快照备份执行事务日志备份将创建每个数据库文件(不仅仅是事务日志)的文件快照、将文件快照位置信息记录到备份文件,并截断事务日志文件。Transaction log backup: Performing a transaction log backup using file-snapshot backup creates a file-snapshot of each database file (not just the transaction log), records the file-snapshot location information into the backup file, and truncates the transaction log file.

重要

在建立事务日志备份链所需的初始完整备份(可以是文件快照备份)后,你只需执行事务日志备份,因为每个事务日志文件快照备份集均包含所有数据库文件的文件快照,并可用于执行数据库还原或日志还原。After the initial full backup that is required to establish the transaction log backup chain (which can be a file-snapshot backup), you only need to perform transaction log backups because each transaction log file-snapshot backup set contains file-snapshots of all database files and can be used to perform a database restore or a log restore. 在初始完整数据库备份之后,不需要执行其他完整或差异备份,因为 Azure Blob 存储服务可处理每个文件快照和每个数据库文件的基本 blob 的当前状态之间的差异。After the initial full database backup, you do not need additional full or differential backups because the Azure Blob storage service handles the differences between each file-snapshot and the current state of the base blob for each database file.

备注

有关将 Microsoft Azure Blob 存储服务和 SQL Server 2016 配合使用的教程,请参见教程:将 Microsoft Azure Blob 存储服务用于 SQL Server 2016 数据库For a tutorial on using SQL Server 2016 with the Microsoft Azure Blob storage service, see Tutorial: Using the Microsoft Azure Blob storage service with SQL Server 2016 databases

使用文件快照备份还原Restore using file-snapshot backups

由于每个文件快照备份集均包含每个数据库文件的文件快照,因此还原过程最多需要相邻的两个文件快照备份集。Because each file-snapshot backup set contains a file-snapshot of each database file, a restore process requires at most adjacent two file-snapshot backup sets. 无论备份集是来自完整数据库备份还是来自日志备份,都是如此。This is true regardless of whether the backup set is from a full database backup or a log backup. 在使用传统流备份文件执行还原过程时,这与还原过程大不相同。This is very different than the restore process when using traditional streaming backup files to perform the restore process. 使用传统流备份时,还原过程需要使用整个备份集链:完整备份、差异备份以及一个或多个事务日志备份。With traditional streaming backup, the restore process requires the use of an entire chain of backup sets: the full backup, a differential backup and one or more transaction log backups. 无论还原是使用文件快照备份还是使用流备份集,还原过程的恢复部分将保持不变。The recovery portion of the restore process remains the same regardless of whether the restore is using a file-snapshot backup or a streaming backup set.

还原到任何备份集的时间: 要执行 RESTORE DATABASE 操作将数据库还原到特定文件快照备份集的时间,只需要特定备份集加上基本 blob 本身。To the time of any backup set: In order to perform a RESTORE DATABASE operation to restore a database to the time of a specific file-snapshot backup set, only the specific backup set is required, plus the base blobs themselves. 因为你可以使用事务日志文件快照备份集执行 RESTORE DATABASE 操作,你通常将使用事务日志备份集来执行此类型的 RESTORE DATABASE 操作,而很少使用完整数据库备份集。Because you can use a transaction log file-snapshot backup set to perform a RESTORE DATABASE operation, you will typically use a transaction log backup set to perform this type of RESTORE DATABASE operation and rarely use a full database backup set. 本主题的末尾显示了演示此技术的示例。An example appears at the end of this topic demonstrating this technique.

还原到两个文件快照备份集之间的时间点: 要执行 RESTORE DATABASE 操作将数据库还原到两个相邻的事务日志备份集的时间之间的特定时间点,只需要两个事务日志备份集(在你想要将数据库还原到的时间点之前一个,在此时间点之后一个)。To a point in time between two file-snapshot backup sets: In order to perform a RESTORE DATABASE operation to restore a database to a specific point in time between the time of two adjacent transaction log backup sets, only two transaction log backup sets are required (one before and one after the point in time to which you wish to restore the database). 若要实现此目的,应使用事务日志文件快照备份集从之前的时间点执行带 NORECOVERY 的 RESTORE DATABASE 操作,并使用事务日志文件快照备份集从之后的时间点执行带 RECOVERY 的 RESTORE LOG 操作,使用 STOPAT 参数指定要停止从事务日志备份恢复的时间点。To accomplish this, you would perform a RESTORE DATABASE operation WITH NORECOVERY using the transactional log file-snapshot backup set from the earlier point in time and perform a RESTORE LOG operation WITH RECOVERY using the transaction log file-snapshot backup set from the later point in time and using the STOPAT argument to specify the point in time at which to stop the recovery from the transaction log backup. 本主题的末尾显示了演示此技术的示例。An example appears at the end of this topic demonstrating this technique. 有关实时演示,请参阅 时间点还原的演示For a live demo, see Demo of Point in Time Restore.

文件备份集维护File-backup set maintenance

删除文件快照备份集: 不能使用 FORMAT 参数覆盖文件快照备份集。Deleting a file-snapshot backup set: You cannot overwrite a file-snapshot backup set using the FORMAT argument. 不允许使用 FORMAT 参数以避免保留使用原始文件快照备份创建的孤立文件快照。The FORMAT argument is not permitted to avoid leaving orphaned file-snapshots that were created with the original file-snapshot backup. 若要删除文件快照备份集,请使用 sys.sp_delete_backup 系统存储过程。To delete a file-snapshot backup set, use the sys.sp_delete_backup system stored procedure. 此存储过程删除组成备份集的备份文件和文件快照。This stored procedure deletes the backup file and the file-snapshots that comprise the backup set. 使用另一种方法删除文件快照备份集可以删除备份文件,而不删除备份集中的文件快照。Using another method to delete a file-snapshot backup set may delete the backup file without deleting the file-snapshots in the backup set.

删除孤立的备份文件快照: 在以下情况下,你可能有孤立的文件快照:未使用“sys.sp_delete_backup”系统存储过程删除备份文件,或者在包含数据库或数据库文件的 blob 有关联的备份文件快照时,删除了该数据库或数据库文件 。Deleting orphaned backup file-snapshots: You may have orphaned file-snapshots if the backup file was deleted without using the sys.sp_delete_backup system stored procedure or if a database or database file was dropped while the blob(s) containing the database or database file had backup file-snapshots associated with them. 若要确定可能会孤立的文件快照,请使用 sys.fn_db_backup_file_snapshots 系统函数列出数据库文件的所有文件快照。To identify file-snapshots that may be orphaned, use the sys.fn_db_backup_file_snapshots system function to list all file-snapshots of the database files. 若要确定属于特定文件快照备份集的文件快照,请使用 RESTORE FILELISTONLY 系统存储过程。To identify the file-snapshots that are part of a specific file-snapshot backup set, use the RESTORE FILELISTONLY system stored procedure. 然后,可以使用 sys.sp_delete_backup_file_snapshot 系统存储过程删除已孤立的单个备份文件快照。You can then use the sys.sp_delete_backup_file_snapshot system stored procedure to delete an individual backup file-snapshot that was orphaned. 可在本主题的末尾找到使用此系统函数和这些系统存储过程的示例。Examples using this system function and these system stored procedures are at the end of this topic. 有关详细信息,请参阅 sp_delete_backup (Transact-SQL)sys.fn_db_backup_file_snapshots (Transact-SQL)sp_delete_backup_file_snapshot (Transact-SQL)RESTORE FILELISTONLY (Transact-SQL)For more information, see sp_delete_backup (Transact-SQL), sys.fn_db_backup_file_snapshots (Transact-SQL), sp_delete_backup_file_snapshot (Transact-SQL), and RESTORE FILELISTONLY (Transact-SQL).

注意事项和限制Considerations and Limitations

高级存储: 使用高级存储时,适用以下限制:Premium storage: When using premium storage, the following limitations apply:

单个存储帐户: 文件快照和目标 blob 必须使用相同的存储帐户。Single storage account: The file-snapshot and destination blobs must use the same storage account.

大容量恢复模式: 使用大容量日志恢复模式并处理事务日志备份(包含以最低限度记录的事务)时,不能使用该事务日志备份执行日志还原(包括时间点恢复)。Bulk recovery model: When using bulk-logged recovery mode and working with a transaction log backup containing minimally logged transactions, you cannot do a log restore (including point in time recovery) using the transaction log backup. 而是应执行到文件快照备份集的时间的数据库还原。Rather, you perform a database restore to time of the file-snapshot backup set. 此限制与流备份的限制相同。This limitation is identical to the limitation with streaming backup.

联机还原: 使用文件快照备份时,不能执行联机还原。Online Restore: When using file-snapshot backups, you cannot perform an Online Restore. 有关联机还原的详细信息,请参阅联机还原 (SQL Server)For more information about Online Restore, see Online Restore (SQL Server).

计费: 使用 SQL Server 文件快照备份时,如果数据发生更改,将会产生额外的费用。Billing: When using SQL Server file-snapshot backup, additional charges will be incurred as data changes. 有关详细信息,请参阅 了解快照如何产生费用For more information, see Understanding How Snapshots Accrue Charges.

存档: 如果你想要将文件快照备份存档,则可以存档到 blob 存储或流备份。Archival: If you wish to archive a file-snapshot backup, you can archive to blob storage or to streaming backup. 若要存档到 blob 存储,请将文件快照备份集中的 blob 复制到单独的 blob。To archive to blob storage, copy the snapshots in the file-snapshot backup set into separate blobs. 若要存档到流备份,请将文件快照备份还原为新数据库,然后使用压缩和/或加密执行标准流备份,并根据需要将其独立于基本 blob 进行存档。To archive to streaming backup, restore the file-snapshot backup as a new database and then perform a normal streaming backup with compression and/or encryption and archive it for as long as desired, independent of the base blobs.

重要

维护多个文件快照备份只有较小的性能开销。Maintaining multiple file-snapshot backups has only a small performance overhead. 但是,维护过多文件快照备份会影响数据库的 I/O 性能。However, maintaining an excessive number of file-snapshot backups can have an I/O performance impact on the database. 我们建议你仅维护支持恢复点目标所需的这些文件快照备份。We recommend you maintain only those file-snapshot backups necessary to support your recovery point objective.

使用文件快照备份备份数据库和日志Backing up the database and log using a file-snapshot backup

下面的示例使用文件快照备份将 AdventureWorks2016 示例数据库备份到 URL。The example below uses file-snapshot backup to back up the AdventureWorks2016 sample database to URL.

-- To permit log backups, before the full database backup, modify the database   
-- to use the full recovery model.  
USE master;  
GO  
ALTER DATABASE AdventureWorks2016  
   SET RECOVERY FULL;  
GO  
-- Back up the full AdventureWorks2016 database.  
BACKUP DATABASE AdventureWorks2016   
TO URL = 'https://<mystorageaccountname>.blob.core.windows.net/<mycontainername>/AdventureWorks2016.bak'   
WITH FILE_SNAPSHOT;  
GO  
-- Back up the AdventureWorks2016 log using a time stamp in the backup file name.  
DECLARE @Log_Filename AS VARCHAR (300);  
SET @Log_Filename = 'https://<mystorageaccountname>.blob.core.windows.net/<mycontainername>/AdventureWorks2016_Log_'+   
REPLACE (REPLACE (REPLACE (CONVERT (VARCHAR (40), GETDATE (), 120), '-','_'),':', '_'),' ', '_') + '.trn';  
BACKUP LOG AdventureWorks2016  
 TO URL = @Log_Filename WITH FILE_SNAPSHOT;  
GO  

SQL ServerSQL Server 文件快照备份还原Restoring from a SQL ServerSQL Server file-snapshot backup

下面的示例使用事务日志文件快照备份集还原 AdventureWorks2016 数据库,并显示恢复操作。The following example restores the AdventureWorks2016 database using a transaction log file-snapshot backup set, and shows a recovery operation. 请注意,你可以从单个事务日志文件快照备份集还原数据库。Notice that you can restore a database from a single transactional log file-snapshot backup set.

RESTORE DATABASE AdventureWorks2016 FROM URL = 'https://<mystorageaccountname>.blob.core.windows.net/<mycontainername>/AdventureWorks2016_2015_05_18_16_00_00.trn'   
WITH RECOVERY, REPLACE;  
GO  

SQL ServerSQL Server 文件快照备份还原到某个时间点Restoring from a SQL ServerSQL Server file-snapshot backup to a point in time

下面的示例使用两个事务日志文件快照备份集将 AdventureWorks2016 还原到其指定时间点的状态,并显示恢复操作。The following example restores the AdventureWorks2016 to its state at a specified point in time using two transaction log file-snapshot backup sets, and shows a recovery operation.

RESTORE DATABASE AdventureWorks2016 FROM URL = 'https://<mystorageaccountname>.blob.core.windows.net/<mycontainername>/AdventureWorks2016_2015_05_18_16_00_00.trn'   
WITH NORECOVERY,REPLACE;  
GO   
  
RESTORE LOG AdventureWorks2016 FROM URL = 'https://<mystorageaccountname>.blob.core.windows.net/<mycontainername>/AdventureWorks2016_2015_05_18_18_00_00.trn'   
WITH RECOVERY,STOPAT = 'May 18, 2015 5:35 PM';  
GO  

删除数据库文件快照备份集Deleting a database file-snapshot backup set

若要删除文件快照备份集,请使用 sys.sp_delete_backup 系统存储过程。To delete a file-snapshot backup set, use the sys.sp_delete_backup system stored procedure. 指定数据库名称,以便让系统验证指定的文件快照备份集是否确实为指定数据库的备份。Specify the database name to have the system verify that the specified file-snapshot backup set is a indeed a backup for the database specified. 如果未指定数据库名称,则将删除指定的备份集及其文件快照而不进行此类验证。If no database name is specified, the specified backup set with its file-snapshots will be deleted without such a validation. 有关详细信息,请参阅 sp_delete_backup (Transact-SQL)For more information, see sp_delete_backup (Transact-SQL).

警告

如果尝试使用其他方法(如 Microsoft Azure 管理门户或 SQL Server Management Studio 中的 Azure 存储查看器)删除文件快照备份集,则不会删除备份集中的文件快照。Attempting to delete a file-snapshot backup set using another method, such as the Microsoft Azure Management Portal or the Azure Storage viewer in SQL Server Management Studio will not delete the file-snapshots in the backup set. 这些工具将仅删除备份文件本身(其中包含指向文件快照备份集中的文件快照的指针)。These tools will only delete the backup file itself that contains the pointers to the file-snapshots in the file-snapshot backup set. 若要找到不正确地删除备份文件后保留的备份文件快照,请使用 sys.fn_db_backup_file_snapshots 系统函数,然后使用 sys.sp_delete_backup_file_snapshot 系统存储过程来删除单个备份文件快照。To identify backup file-snapshots that remain after a backup file was improperly deleted, use the sys.fn_db_backup_file_snapshots system function and then use the sys.sp_delete_backup_file_snapshot system stored procedure to delete an individual backup file-snapshot.

以下示例删除指定的文件快照备份集,包括构成指定的备份集的备份文件和文件快照。The following example deletes the specified file-snapshot backup set, including the backup file and the file-snapshots comprising the specified backup set.

sys.sp_delete_backup 'https://<mystorageaccountname>.blob.core.windows.net/<mycontainername>/AdventureWorks2016.bak', 'adventureworks2016' ;  
GO  
  

查看数据库备份文件快照Viewing database backup file-snapshots

若要查看每个数据库文件的基本 blob 的文件快照,请使用 sys.fn_db_backup_file_snapshots 系统函数。To view file-snapshots of the base blob for each database file, use the sys.fn_db_backup_file_snapshots system function. 使用此系统函数可以查看使用 Azure Blob 存储服务存储的数据库的每个基本 blob 的所有备份文件快照。This system function enables you to view all backup file-snapshots of each base blob for a database stored using the Azure Blob storage service. 此函数的主要用例是列出使用 sys.sp_delete_backup 系统存储过程以外的机制删除了文件快照备份集的备份文件时保留的数据库备份文件快照。A primary use case for this function is to identify backup file-snapshots of a database that remain when the backup file for a file-snapshot backup set is deleted using a mechanism other than the sys.sp_delete_backup system stored procedure. 若要确定属于完整备份集的备份文件快照,和不属于完整备份集的备份文件快照,请使用 RESTORE FILELISTONLY 系统存储过程列出属于每个备份文件的文件快照。To determine the backup file-snapshots that are part of intact backup sets and the ones that are not part of intact backup sets, use the RESTORE FILELISTONLY system stored procedure to list the file-snapshots belonging to each backup file. 有关详细信息,请参阅 sys.fn_db_backup_file_snapshots (Transact-SQL)RESTORE FILELISTONLY (Transact-SQL)For more information, see sys.fn_db_backup_file_snapshots (Transact-SQL) and RESTORE FILELISTONLY (Transact-SQL).

下面的示例返回指定数据库的所有备份文件快照的列表。The following example returns the list of all backup file-snapshots for the specified database.

--Either specify the database name or set the database context  
USE AdventureWorks2016  
select * from sys.fn_db_backup_file_snapshots (null) ;  
GO  
select * from sys.fn_db_backup_file_snapshots ('AdventureWorks2016') ;  
GO  
  

删除单个数据库备份文件快照Deleting an individual database backup file-snapshot

若要删除数据库基本 blob 的单个备份文件快照,请使用 sys.sp_delete_backup_file_snapshot 系统存储过程。To delete an individual backup file-snapshot of a database base blob, use the sys.sp_delete_backup_file_snapshot system stored procedure. 此系统存储过程的主要用例是删除在使用 sys.sp_delete_backup 系统存储过程以外的方法删除备份文件后保留的孤立的文件快照文件。A primary use case for this system stored procedure is to delete orphaned file-snapshot files that remain after a backup file was deleted using a method other than the sys.sp_delete_backup system stored procedure. 有关详细信息,请参阅 sp_delete_backup_file_snapshot (Transact-SQL)For more information, see sp_delete_backup_file_snapshot (Transact-SQL).

警告

删除属于文件快照备份集的单个文件快照将使备份集失效。Deleting an individual file-snapshot that is part of a file-snapshot backup set will invalidate the backup set.

下面的示例将删除指定的备份文件快照。The following example deletes the specified backup file-snapshot. 使用 sys.fn_db_backup_file_snapshots 系统函数获取指定备份的 URL。The URL for the specified backup was obtained using the sys.fn_db_backup_file_snapshots system function.

sys.sp_delete_backup_file_snapshot N'adventureworks2016', N'https://<mystorageaccountname>.blob.core.windows.net/<mycontainername>/AdventureWorks2016Data.mdf?snapshot=2015-05-29T21:31:31.6502195Z';  
GO  

另请参阅See Also

教程:将 Microsoft Azure Blob 存储服务用于 SQL Server 2016 数据库Tutorial: Using the Microsoft Azure Blob storage service with SQL Server 2016 databases