How to restore only a specific filegroup – A piecemeal restore

What is a piecemeal restore?

A piecemeal restores allow the user to restore only a specific filegroup from a database instead of restoring a whole database. In cases of emergency, the important thing is to get the needed data as quickly as possible and restore the rest of the data later while having the most significant data online. This approach minimizes the downtime when a disaster occurs.

A piecemeal restore scenarios can be:

• Online piecemeal restore

  When the partial restore is performed, the database is online, and the primary filegroup and all secondary filegroups that have been recovered are available. Filegroups that have not been included in the restore process will remain offline but can be easily restored when needed, without the need to take a database offline

• Offline piecemeal restore

  Similar to the online piecemeal restore, the database remains online after the partial restore is performed, and all filegroups that have not been restored remain offline, but can still be restored as needed when database is taken offline

• Piecemeal restore for databases with Memory optimized tables

  When performing this kind of piecemeal restore, it is important to be aware of the fact that there is an important restriction for this case – a memory-optimized filegroup must be backed up and restored together with the primary filegroup:

  1. If primary group is restored or backed up, the memory-optimized filegroup must be specified
  2. If the memory-optimized filegroup is restored or backed up, primary group must be specified

An offline piecemeal restore is supported in all versions of SQL Server but an online option is available only in the Enterprise edition.

A piecemeal restore sequence begins with restoring the primary filegroup. With this step, a database is brought online. After this step as each secondary filegroup is restored their data is online and available for querying regularly.

Piecemeal restores in different recovery models

A piecemeal restores can be performed for both databases in simple and full, or bulk-logged recovery models with some differences and the requirements for performing a piecemeal restore depend on a database recovery model. However, a piecemeal restore is more flexible for databases in full and bulk-logged models.

When a database is in a Simple recovery mode a piecemeal restore recovers a database in stages and begins with the restore of the primary filegroup and all read/write, secondary filegroups in sequence. For databases in a simple recovery mode before a piecemeal restore, a database has to have a full or partial database backup.

For databases in a Full recovery mode, before the piecemeal restore is started, a DBA has to take a tail-log backup a database. After that, a piecemeal restore is the same as in a simple recovery mode starting with restoring the primary filegroup.

For all recovery models, a piecemeal restore starts with a partial-restore sequence which is an initial restore sequence. That sequence restores the primary filegroup. For databases in the simple recovery model, the partial restore will also restore all read/write filegroups along with the primary filegroup. During the initial restore sequence, a database has to go offline. After the initial restore, the database is online and all filegroups restored during the initial restore are available.

In our previous article we wrote about how to backup a specific filegroup, as well as scenarios about how to perform a partitioning on several filegroups to improve performance and administration.

In this article, we will explain how to restore a specific filegroup and perform a piecemeal restore by using T-SQL.

Piecemeal restores are not supported in either SQL Server Management Studio nor by Maintenance Plans.

For the purpose of this article we’ve created the new database, FilegroupRestores, with two filegroups and performed the full and transaction log backups.

Piecemeal restore using T-SQL

The partial-restore sequence for every recovery model starts with the RESTORE DATABASE statement to restore a full database backup, and the WITH PARTIAL option has to be specified. In the first partial restore, as many as needed filegroups can be restored, but the first sequence has to include the PRIMARY filegroup.

In this example our database is in a full recovery mode so we will also need to restore the transaction logs:

RESTORE DATABASE FilegroupRestores FILEGROUP = 'Primary'
FROM DISK = N'E:\FGBackups\BackupFG_full.bak'
WITH PARTIAL
	,NORECOVERY

RESTORE LOG FilegroupRestores
FROM DISK = N'E:\FGBackups\BackupFG_tlog1.trn'
WITH NORECOVERY

RESTORE LOG FilegroupRestores
FROM DISK = N'E:\FGBackups\BackupFG_tlogtail.trn'
WITH RECOVERY

With this query we have recovered only a PRIMARY filegroup and performed a piecemeal restore. To check the state of the other filegroup we will run the following query:

SELECT NAME AS FilegroupName
	,state_desc AS RecoveryStatus
FROM FilegroupRestores.sys.database_files;
GO

This query gives us the state of our filegoups:

As we can see first the PRIMARY filegroup is restored and online, and we can continue recovering the rest of the desired filegroups.