Restoring large databases can be extremely time-consuming. That time feels like it’s slipping away even faster when you’re racing to recover a crucial VLDB lost to corruption or mishap.

Fortunately, if you’re using SQL Server Enterprise and require quicker access to only a subset of data, piecemeal restores may be your answer to faster recovery.

Piecemeal restores are available in both Standard and Enterprise editions. However, with Enterprise, you can perform these online.

In this post, I’ll cover:

• What is an online piecemeal restore?
• Requirements for performing an online piecemeal restore.
• Best practices for online piecemeal restores.

What is an online piecemeal restore?

An online piecemeal restore is a multi-stage process that allows partial restoration of a database while keeping the primary filegroup available for access. This is particularly useful for:

• Minimizing downtime: The primary filegroup remains online while secondary filegroups are restored.
• High Availability: Users can continue working with critical data while less frequently accessed data is recovered.
• Efficient Disaster Recovery: Restoring large databases in phases instead of performing a full database restore.

Requirements for performing an online piecemeal restore

Before you can perform an online piecemeal restore, ensure that

1. Enterprise edition of SQL Server: Online piecemeal restores are only supported in SQL Server Enterprise edition.
2. Database uses multiple filegroups: The database must utilize multiple filegroups.
3. Database uses full recovery model: Ensure the database is using the full recovery model.
4. Primary filegroup remains online: The primary filegroup must be online before restoring secondary filegroups.
5. Log backups are available: Transaction logs must cover the period between the full backup and the latest (differential or filegroup) backup.
6. No cross-filegroup constraints: Foreign key constraints referencing objects in different filegroups can block an online restore. However, FKs that reference objects in the primary filegroup will not cause an issue (the primary filegroup must be restored first and online before all secondary filegroups can be restored).

Step-by-step guide to performing an online piecemeal restore

Step 1: Verify database filegroups

Before initiating a restore, check the current filegroups in the database:

select name, type_desc from sys.filegroups;

Step 2: Restore the primary filegroup

Restore the primary filegroup and bring the database online. If you are restoring additional differential or log backups, include “NORECOVERY” in your restore statement. However, in this example, we’re only restoring the FULL backup and then bringing the database online. Take note of the “PARTIAL” keyword:

RESTORE DATABASE MyDatabase FILEGROUP = 'PRIMARY' FROM DISK = '\\myshare\mydatabase\full\MyDatabase_FULL.bak' 
WITH PARTIAL, STATS = 10;

Step 3: Restore additional filegroups

Each additional filegroup must be restored. If a query attempts to access an object that is in the secondary filegroup, an error will be returned stating the filegroup is offline.

RESTORE DATABASE MyDatabase FILEGROUP = 'MyFileGroup' FROM DISK = '\\myshare\mydatabase\full\MyDatabase_FULL.bak'
WITH STATS = 10;

At this point, the database is fully available for use.

Best practices for online piecemeal restores

• To support online piecemeal restores, Enterprise edition must be used.
• Ensure no foreign keys or indexed views span multiple filegroups that may block restores.

Want to work with The SERO Group?

Want to learn more about how The SERO Group helps organizations take the guesswork out of managing their SQL Servers? Schedule a no-obligation discovery call with us to get started.