Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

Piecemeal Restore of Databases With Memory-Optimized Tables


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.

  • 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. For more information, see Restore Pages (SQL Server).

The examples use the following schema:

ON PRIMARY (name = imoltp_primary1, filename = 'c:\data\imoltp_data1.mdf')
LOG ON (name = imoltp_log, filename = 'c:\data\imoltp_log.ldf')

ALTER DATABASE imoltp ADD FILE (name = imoltp_primary2, filename = 'c:\data\imoltp_data2.ndf')

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

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 

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

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

Community Additions

© 2015 Microsoft