Piecemeal Restore: Bringing the Primary Filegroup Online First

A large database can keep years of archive rows in separate filegroups. A piecemeal restore can bring PRIMARY and current data online before the archive is ready, reducing the time until critical work resumes. The sequence needs a valid backup and log chain, and online restore requires Enterprise edition.

Design Filegroups for a Piecemeal Restore

Piecemeal restore only helps if the schema already separates critical and less critical data into filegroups. PRIMARY holds essential metadata and system objects; a current-data filegroup holds active application tables. Historical partitions or archive tables live in another filegroup. A table that the application needs at login cannot be left offline simply because its name says archive.

I start with a map of objects to filegroups and the application's required queries. Which screens and jobs must work before archive data returns? If one critical query joins to an offline archive table, the database being online will not make that workflow usable.

Check Edition, Recovery Model, and Backups

Online piecemeal restore is an Enterprise edition feature. Other editions can run the same sequence offline, but users cannot keep working while later filegroups are restored. Under the full recovery model, maintain an unbroken transaction-log chain and take a tail-log backup when possible. Under the simple recovery model, rules for read/write and read-only filegroups differ, so use a separate tested plan.

SELECT SERVERPROPERTY('Edition') AS edition_name,
       name, recovery_model_desc
FROM sys.databases
WHERE name = N'ArchiveDemo';
SELECT fg.name AS filegroup_name, fg.type_desc,
       fg.is_read_only, df.name AS logical_file_name,
       df.physical_name
FROM ArchiveDemo.sys.filegroups AS fg
JOIN ArchiveDemo.sys.database_files AS df
  ON df.data_space_id = fg.data_space_id;

The second query must run with access to ArchiveDemo; substitute the real database. Inventory full, differential, filegroup, and log backups with their first and last LSNs. A missing log backup can stop a later filegroup from reaching the same recovery point. Test the exact backup set, not an idealized sequence.

Restore the Critical Filegroups First

On a test instance, use a full backup containing the filegroups and restore PRIMARY plus CurrentData with PARTIAL and NORECOVERY. Apply each required log backup in order, ending with RECOVERY. The example paths and logical names are placeholders. Use RESTORE FILELISTONLY and MOVE for a different test server layout.

RESTORE FILELISTONLY
FROM DISK = 'D:\RestoreLab\ArchiveDemo_full.bak';
GO
RESTORE DATABASE ArchiveDemo
    FILEGROUP = 'PRIMARY', FILEGROUP = 'CurrentData'
FROM DISK = 'D:\RestoreLab\ArchiveDemo_full.bak'
WITH PARTIAL, NORECOVERY, STATS = 5;
GO
RESTORE LOG ArchiveDemo
FROM DISK = 'D:\RestoreLab\ArchiveDemo_log_01.trn'
WITH NORECOVERY;
GO
RESTORE LOG ArchiveDemo
FROM DISK = 'D:\RestoreLab\ArchiveDemo_log_02.trn'
WITH RECOVERY;

The actual chain can contain many log backups and a tail-log backup. Do not skip one. On a different path, add MOVE clauses for every file being restored. The initial partial restore marks omitted filegroups recovery pending; it does not copy their data yet. The database can become online when PRIMARY is recovered, while those files remain unavailable.

Verify What Users Can Reach

Query sys.database_files and filegroup metadata after stage one. Run representative application reads against current tables and one deliberate read against an archive object in the lab. The latter should show that archive data is unavailable until its filegroup returns; in my lab it failed with error 8653, filegroup not online. Check log reuse and disk space; deferred transactions involving an offline filegroup can hold locks and delay log truncation.

USE ArchiveDemo;
GO
SELECT name, state_desc, physical_name
FROM sys.database_files
ORDER BY file_id;

I make an explicit list of functions allowed during this interval. Reporting jobs that require archive data should be paused or routed to a known fallback. A database ONLINE status is not a promise that every query can run.

Restore Archive Later

On Enterprise edition, restore the archive filegroup while the database serves current-data work. Run it from master; a window still in ArchiveDemo after the check above fails with error 3102, database in use by this session. Apply the log backups needed to bring that filegroup to the database's recovery point, then finish with RECOVERY. A read-only filegroup backed up after it became read-only can have a simpler sequence. The actual commands depend on the backup history; this illustrates a read/write archive requiring roll-forward.

USE master;
GO
RESTORE DATABASE ArchiveDemo FILEGROUP = 'Archive'
FROM DISK = 'D:\RestoreLab\ArchiveDemo_full.bak'
WITH NORECOVERY, STATS = 5;
GO
RESTORE LOG ArchiveDemo
FROM DISK = 'D:\RestoreLab\ArchiveDemo_log_01.trn'
WITH NORECOVERY;
GO
RESTORE LOG ArchiveDemo
FROM DISK = 'D:\RestoreLab\ArchiveDemo_log_02.trn'
WITH RECOVERY;

Check the filegroup state and run archive queries after recovery. A failed late restore still leaves the system in a degraded state; monitor it like an active incident. Do not assume that a filegroup copied from a different backup can be reconciled without the matching log chain.

Time the Piecemeal Restore and Keep the Map Current

Measure stage-one time to usable current data, total time to all filegroups, log application time, and application errors during the gap. Run DBCC CHECKDB according to the recovery process after the full set is available. Update the object-to-filegroup map when tables or partitions move. A plan based on last year's layout can miss a new critical table.

I keep the tested RESTORE commands with the exact backup labels and recovery point. The useful outcome is not merely a successful syntax check. It is a measured answer to how long users can work without archive data and what operations remain unavailable until the last filegroup is back.

Protect the Log Chain Behind a Piecemeal Restore

Under full recovery, the transaction logs bring each restored filegroup to a consistent point. Save backup LSNs and inspect the sequence before the outage. A tail-log backup can capture work after the last scheduled log backup when the source still permits it. If the source is damaged, the exact backup options depend on its state. Do not improvise during the restore; rehearse the branch for a reachable source and the branch where the tail cannot be taken.

Backups of read-only filegroups can be retained longer because their contents no longer change, but the metadata and recovery sequence still need testing. If an archive filegroup was switched back to read/write, its old read-only assumption no longer applies. Include filegroup read-only state and backup dates in the restore inventory.

Check Degraded-Service Behavior

While Archive remains offline, queries that touch its objects fail even though current-data queries succeed. Application connection pools, ORM metadata checks, and reporting jobs can touch more objects than a single smoke-test SELECT. Run a small suite of realistic workflows after stage one. Publish a clear degraded-service window to operations and keep an alert on the offline files until the final restore passes.

The log can remain under pressure while deferred transactions involving offline filegroups wait for recovery. Monitor log space and reuse wait descriptions during the gap. A faster stage one is valuable only if the instance can safely sustain the interval before stage two. I include that interval in the recovery-time objective, not just the first successful connection.

Related reading on this blog: Backup and Restore Behavior of ReadOnly Filegroup Databases and Making Filegroup Read Only.

Piecemeal restore is not permission to forget archives, it is an ordered recovery to one point.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.