What is a piecemeal restore?
A piecemeal restores allow the user to restore only a specific filegroups 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, 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 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 depends 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 the 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 and ApexSQL Backup.
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.
Piecemeal restore using ApexSQL Backup
To avoid writing T-SQL scripts and restoring all the filegroups as SQL Server Management Studio option doesn’t allow a specific filegroup to be restored as it requires restoring all filegroups at once, use ApexSQL Backup a SQL Server tool specifically designed to manage SQL backups and restores including files and filegroups backups and restores.
To perform a piecemeal restore in ApexSQL Backup:
From the Servers pane select a database from which you want to restore the filegroups and select the History button from the main ribbon:
Select a filegroup backup and select the Restore Files and Filegroups option:
In the Restore Files and Filegroups dialog select the desired filegroups to restore:
Click on the ‘Options’ tab in the left pane an in the Tail-log backup section check the Take a tail log backup before restore check-box for databases in full recovery mode:
The Leave database in the restoring state is checked when the user want’s a database to remain unavailable and no further transactions can be performed at this point.
Selecting the Script option from the dialog will show a T-SQL script created by ApexSQL Backup:
Under the Restore options from the Recovery state drop down select one of the following options:
The RESTORE WITH RECOVERY option is the default setting and puts a database in online state and available to users. This option is commonly used in the following scenarios:
- Database was not recovered when restoring last backup in a restore sequence, and now it needs to be brought online
- Database is in the standby mode, and there is a need to make it available for updating without applying another log backup
The RESTORE WITH NORECOVERY option is used to prevent the database to be available to the users. This option is used in data mirroring and on the secondary server in log shipping.
The RESTORE WITH STANDBY option leaves a database (after restoring a backup image) in a state which allows restoring the additional backup images and allows the read-only access to the users.
When all settings are specified, click the Restore button and a success message will pop up:
As we previously did in an example with T-SQL we can check the state of our filegroups:
January 12, 2016