Backup & Recovery from First Principles, Part 6: Piecemeal Restores – Online Before It’s All Back
A 4 TB database is down. The application needs the 200 GB of hot transactional data; the 3.8 TB of archived history can wait until tomorrow. A standard restore makes everyone wait for all 4 TB. A piecemeal restore brings the database online as soon as the primary filegroup and the filegroups you choose are ready, and lets the rest restore later, while users are already working.[1]
The prerequisite is architectural: the data you want online first must live in its own filegroup. If everything is in PRIMARY, there are no pieces to restore piecemeal. This is one of the better arguments for filegroup design in large databases.
This is part 6 of the backup and recovery series (part 1, part 5). Demo ran on SQL Server 2019 (CU32) and behaves identically on SQL Server 2025 (RTM); note that online piecemeal restore (database available while later pieces restore) is an Enterprise Edition feature – Standard Edition supports the same sequence but the database is only usable once recovery completes offline stages.
The Setup
A database with three filegroups: PRIMARY, FG_HOT (100,000 rows of current data), and FG_COLD (100,000 rows of archive):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE DATABASE [bkrec_fg] ON PRIMARY (NAME = N'bkrec_fg', FILENAME = N'C:\temp\bkrec\bkrec_fg.mdf') , FILEGROUP [FG_HOT] (NAME = N'bkrec_fg_hot', FILENAME = N'C:\temp\bkrec\bkrec_fg_hot.ndf') , FILEGROUP [FG_COLD] (NAME = N'bkrec_fg_cold', FILENAME = N'C:\temp\bkrec\bkrec_fg_cold.ndf') LOG ON (NAME = N'bkrec_fg_log', FILENAME = N'C:\temp\bkrec\bkrec_fg_log.ldf'); /* hot_rows ON [FG_HOT], cold_rows ON [FG_COLD], 100,000 rows each, then filegroup-level backups plus a log backup: */ BACKUP DATABASE [bkrec_fg] FILEGROUP = N'PRIMARY', FILEGROUP = N'FG_HOT' TO DISK = N'C:\temp\bkrec\fg_primary_hot.bak' WITH INIT, COMPRESSION; BACKUP DATABASE [bkrec_fg] FILEGROUP = N'FG_COLD' TO DISK = N'C:\temp\bkrec\fg_cold.bak' WITH INIT, COMPRESSION; BACKUP LOG [bkrec_fg] TO DISK = N'C:\temp\bkrec\fg_log1.trn' WITH INIT, COMPRESSION; |
Filegroup backups plus log backups are the backup strategy for very large databases where a nightly full is impossible; piecemeal restore is how you put them back together. (The database must be in FULL or BULK_LOGGED recovery for this – the log backups are what stitch differently-aged filegroup backups into one consistent database.)
Stage 1: PRIMARY and the Hot Filegroup
Disaster strikes; we restore what the application needs first. The magic words are WITH PARTIAL on the first restore:
|
1 2 3 4 5 6 7 8 |
RESTORE DATABASE [bkrec_fg] FILEGROUP = N'PRIMARY', FILEGROUP = N'FG_HOT' FROM DISK = N'C:\temp\bkrec\fg_primary_hot.bak' WITH PARTIAL, NORECOVERY, REPLACE; RESTORE LOG [bkrec_fg] FROM DISK = N'C:\temp\bkrec\fg_log1.trn' WITH RECOVERY; SELECT COUNT(*) AS [hot_rows] FROM [bkrec_fg].[dbo].[hot_rows]; |
|
1 2 3 |
hot_rows -------- 100000 |
The database is online. Hot data is fully queryable. Total restore time was proportional to the hot data, not the whole database. But ask for the cold data:
|
1 |
SELECT COUNT(*) FROM [bkrec_fg].[dbo].[cold_rows]; |
|
1 2 3 |
Msg 8653, Level 16, State 1 The query processor is unable to produce a plan for the table or view 'cold_rows' because the table resides in a filegroup that is not online. |
That error is not damage; it is the deal we made. sys.database_files shows the state honestly:
|
1 2 3 4 5 6 |
name state_desc ------------- ---------------- bkrec_fg ONLINE bkrec_fg_hot ONLINE bkrec_fg_cold RECOVERY_PENDING bkrec_fg_log ONLINE |
If your application hits Msg 8653 unexpectedly during a staged recovery, I have written about diagnosing that error.
Stage 2: The Cold Filegroup, Later
Hours later, at leisure, with users online the whole time:
|
1 2 3 4 5 6 7 8 |
RESTORE DATABASE [bkrec_fg] FILEGROUP = N'FG_COLD' FROM DISK = N'C:\temp\bkrec\fg_cold.bak' WITH NORECOVERY; RESTORE LOG [bkrec_fg] FROM DISK = N'C:\temp\bkrec\fg_log1.trn' WITH RECOVERY; SELECT COUNT(*) AS [cold_rows] FROM [bkrec_fg].[dbo].[cold_rows]; |
|
1 2 3 |
cold_rows --------- 100000 |
All files now show ONLINE. The log restore at the end of stage 2 is mandatory: it rolls the cold filegroup forward to the same point the rest of the database already reached, which is how SQL Server guarantees transactional consistency across pieces restored hours apart. Keep every log backup taken since the oldest filegroup backup you plan to use – the roll-forward needs an unbroken chain (part 3), and the same all-the-log-backups rule bites even harder in part 7’s page-level restores.
Design Notes
- Piecemeal restore is designed backward from the outage. Decide what must be online in the first hour, put it in dedicated filegroups, and make PRIMARY tiny (system objects only) so stage 1 is fast. I once answered a question about restoring only specific filegroups where the underlying need was exactly this pattern.
- Read-only filegroups sweeten the deal. A read-only archive filegroup needs backing up only when it changes; restore skips the roll-forward for it entirely.
- Rehearse with the real sequence. The
WITH PARTIALkeyword on the first restore is easy to forget and cannot be added retroactively – without it, the restore is a normal filegroup restore and the database stays offline until everything is back.
Next: part 7 shrinks the restore unit from a filegroup to a single 8 KB page.
Have you designed a database for staged recovery, or been saved by one? Comments below, or find me on Bluesky or LinkedIn.
References
- Piecemeal Restores (SQL Server) – Microsoft Learn. Restore stages, WITH PARTIAL, and edition differences for online piecemeal restore. ↩