Piecemeal Restore of Databases With Memory-Optimized Tables
Applies To: SQL Server 2016
Piecemeal restore is supported on databases with memory-optimized tables except for one restriction described below. 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 backup (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. 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.
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. For more information, see Restore Pages (SQL Server).
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
This sample shows how to backup 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 back up of filegroup(s) other than primary and memory-optimized filegroup works similar to the databases without memory-optimized tables. The following command backups up the secondary filegroup
backup database imoltp filegroup='imoltp_secondary' to disk='c:\data\imoltp_secondary.dmp' with init
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