Azure 中資料庫檔案的檔案快照集備份File-Snapshot Backups for Database Files in Azure

適用於: 是SQL Server 否Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse 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. 如需即時的示範,請參閱 File-Snapshot Backups Demo (檔案快照集備份示範)For a live demo, see Demo of Point in Time Restore. 如需使用 Azure Blog 儲存體服務儲存資料庫檔案的詳細資訊,請參閱 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),請前往 Evaluation CenterTo download SQL Server 2016 (13.x)SQL Server 2016 (13.x), go to Evaluation Center.

  • 有 Azure 帳戶嗎?Have an Azure account? 接著前往 這裡 ,來加速已安裝 SQL Server 2017SQL Server 2017 的虛擬機器。Then go Here to spin up a Virtual Machine with SQL Server 2017SQL Server 2017 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.

注意

如需搭配使用 SQL Server 2016 和 Microsoft Azure Blob 儲存體服務的教學課程,請參閱教學課程:搭配使用 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). 若要這麼做,您需要使用較早時間點的交易記錄檔案快照集備份組,來執行 RESTORE DATABASE 作業 WITH NORECOVERY,並使用較晚時間點的交易記錄檔案快照集備份組,來執行來執行 RESTORE LOG 作業 WITH RECOVERY,並使用 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. 如需即時的示範,請參閱 File-Snapshot Backups Demo (檔案快照集備份示範)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. 如需詳細資訊,請參閱 Understanding How Snapshots Accrue Charges(了解快照集的計費方式)。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。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 Storage Viewer) 刪除檔案快照集備份組,則備份組中的檔案快照集將不會被刪除。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. 指定之備份 URL 是使用 sys.fn_db_backup_file_snapshots 系統函數取得。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