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:
- If primary group is restored or backed up, the memory-optimized filegroup must be specified
- 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 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.
January 12, 2016