分次還原具有記憶體最佳化資料表的資料庫Piecemeal Restore of Databases With Memory-Optimized Tables

• 03/06/2017
• • 
  • 
  • 
  • 

適用於： SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) 平行處理資料倉儲 APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse

具有記憶體最佳化資料表的資料庫支援分次還原，但受到下列一項限制。Piecemeal restore is supported on databases with memory-optimized tables except for one restriction described below. 如需分次備份和還原的詳細資訊，請參閱 RESTORE (Transact-SQL) 和分次還原 (SQL Server)。For more information about piecemeal backup and restore, see RESTORE (Transact-SQL) and Piecemeal Restores (SQL Server).

記憶體最佳化的檔案群組必須與主要檔案群組同時備份和還原：A memory-optimized filegroup must be backed up and restored together with the primary filegroup:

• 如果您備份 (或還原) 主要檔案群組，您必須指定記憶體最佳化的檔案群組。If you back up (or restore) the primary filegroup you must specify the memory-optimized filegroup.

• 若您備份 (或還原) 記憶體最佳化檔案群組，您必須指定主要檔案群組。If you back up (or restore) the memory-optimized filegroup you must specify the primary filegroup.

分次備份和還原的主要狀況包括：Key scenarios for piecemeal backup and restore are,

• 分次備份可縮小備份大小。Piecemeal backup allows you to reduce the size of backup. 以下是一些範例：Some examples:

  • 設定在不同的時間或日期進行資料庫備份，可降低對工作負載的影響。Configure the database backup to occur at different times or days to minimize the impact on the workload. 例如，非常大型的資料庫 (大於 1 TB) 無法在配置給資料庫維護的時間內完成完整資料庫備份。One example is a very large database (greater than 1 TB) where a full database backup cannot complete in the time allocated for database maintenance. 在此情況下，您可以使用分次備份將整個資料庫分成多次備份。In that situation, you can use piecemeal backup to backup the full database in multiple piecemeal backups.

  • 如果檔案群組標示為唯讀，在標示為唯讀之後，便不需要交易記錄備份。If a filegroup is marked read-only, it does not require a transaction log backup after it was marked read-only. 您可以選擇在標示為唯讀之後只備份檔案群組一次。You can choose to back up the filegroup only once after marking it read-only.

• 分次還原。Piecemeal restore.

  • 分次還原的目標是要讓資料庫的重要部分恢復連線狀態，而不需要等候所有資料。The goal of a piecemeal restore is to bring the critical parts of database online without waiting for all the data. 例如，如果資料庫具有分割資料，會很少使用這類舊的分割區。One example is if a database has partitioned data, such that older partitions are only used rarely. 您可以僅在必要時才進行還原。You can restore them only on an as-needed basis. 類似的範例為包含歷程記錄資料的檔案群組。Similar for filegroups that contain, for example, historical data.

  • 透過頁面修復，您可以還原特定頁面來修正損毀的頁面。Using page repair, you can fix page corruption by specifically restoring the page. 如需詳細資訊，請參閱還原頁面 (SQL Server)。For more information, see Restore Pages (SQL Server).

範例Samples

這些範例使用下列結構描述：The examples use the following schema:

CREATE DATABASE imoltp
    ON PRIMARY (
        name = imoltp_primary1,
        filename = 'c:\data\imoltp_data1.mdf')
    LOG ON (
        name = imoltp_log,
        filename = 'c:\data\imoltp_log.ldf');
    GO  
  
ALTER DATABASE imoltp
    ADD FILE (
        name = imoltp_primary2,
        filename = 'c:\data\imoltp_data2.ndf');
GO  
  
ALTER DATABASE imoltp
    ADD FILEGROUP imoltp_secondary;

ALTER DATABASE imoltp
    ADD FILE (
        name = imoltp_secondary,
        filename = 'c:\data\imoltp_secondary.ndf')
            TO FILEGROUP imoltp_secondary;
GO  
  
ALTER DATABASE imoltp
    ADD FILEGROUP imoltp_mod
    CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE imoltp
    ADD FILE (
        name = 'imoltp_mod1',
        filename = 'c:\data\imoltp_mod1')
            TO FILEGROUP imoltp_mod;

ALTER DATABASE imoltp
    ADD FILE (
        name = 'imoltp_mod2',
        filename = 'c:\data\imoltp_mod2')
            TO FILEGROUP imoltp_mod;
GO  

BackupBackup

此範例會示範如何備份主要檔案群組和記憶體最佳化的檔案群組。This sample shows how to back up the primary filegroup and the memory-optimized filegroup. 您必須同時指定主要檔案群組和記憶體最佳化的檔案群組。You must specify both primary and memory-optimized filegroup together.

BACKUP database imoltp
    filegroup = 'primary',
    filegroup = 'imoltp_mod'
    to disk = 'c:\data\imoltp.dmp'
    with init;

下列範例顯示主要檔案群組和記憶體最佳化檔案群組之外的檔案群組備份，其運作方式與沒有記憶體最佳化資料表的資料庫相似。The following sample shows that a backup of a filegroup other than primary, and memory-optimized filegroup, works similar to the databases without memory-optimized tables. 下列命令會備份次要檔案群組The following command backs up the secondary filegroup

BACKUP database imoltp
    filegroup = 'imoltp_secondary'
    to disk = 'c:\data\imoltp_secondary.dmp'
    with init;

RestoreRestore

下列範例顯示如何同時還原主要檔案群組和記憶體最佳化的檔案群組。The following sample shows how to restore the primary filegroup and memory-optimized filegroup together.

RESTORE database imoltp
    filegroup = 'primary',
    filegroup = 'imoltp_mod'
    from disk = 'c:\data\imoltp.dmp'
    with
        partial,
        norecovery;

-- Restore the transaction log.

RESTORE LOG [imoltp]
    FROM DISK = N'c:\data\imoltp_log.dmp'
    WITH
        FILE = 1,
        NOUNLOAD,
        STATS = 10;
GO

下一個範例顯示還原主要檔案群組和記憶體最佳化的檔案群組之外的檔案群組，類似於還原不具有記憶體最佳化資料表的資料庫The next sample shows that restoring filegroup(s) other than the primary and memory-optimized filegroup works similar to the databases without memory-optimized tables

RESTORE DATABASE [imoltp]
    FILE = N'imoltp_secondary'
    FROM DISK = N'c:\data\imoltp_secondary.dmp'
    WITH
        FILE = 1,
        RECOVERY,
        NOUNLOAD,
        STATS = 10;
GO

另請參閱See Also

備份、還原及復原記憶體最佳化資料表Backup, Restore, and Recovery of Memory-Optimized Tables