Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

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:

create database imoltp
on primary 
(   name = imoltp_primary1,
   filename = 'c:\data\imoltp_data1.mdf'
),
(   name = imoltp_primary2,
   filename = 'c:\data\imoltp_data2.ndf'
),
filegroup imoltp_mod contains MEMORY_OPTIMIZED_DATA
(   name = imoltp_mod1,
   filename = 'c:\data\imoltp_mod1'
),
(   name = imoltp_mod2,
   filename = 'c:\data\imoltp_mod2'
),
filegroup imoltp_secondary
(   name = imoltp_secondary,
   filename = 'c:\data\imoltp_secondary.ndf')
LOG on
(   name = imoltp_log,
   filename = 'c:\data\imoltp_log.ldf');

Backup

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

Restore

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
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.