During an online piecemeal restore, is it possible to restore to a point-in-time?

I have a database (full recovery model) with multiple filegroups, that each have full and diff backups. I am able to replicate an online piecemeal restore (SQL Server 2019 Enterprise Edition) as it is described here: Example: Piecemeal Restore of Database (Full Recovery Model)

However, when I add a STOPAT clause when restoring the last log backup, this only works for the primary filegroup. For all following filegroups I get either this message:

Msg 4342, Level 16, State 1, Line 161
Point-in-time recovery is not possible unless the primary filegroup is part of the restore sequence. Omit the point-in-time clause or restore the primary filegroup.

When I try to restore the log without the STOPAT clause, I get this:

The supplied backup is not on the same recovery path as the database, and is ineligible for use for an online file restore.

I was not able to find any restrictions about point-in-time recovery during online piecemeal restores, except for this part (found on Piecemeal Restores (SQL Server)):

If a partial restore sequence excludes any FILESTREAM filegroup, point-in-time restore is not supported. You can force the restore sequence to continue. However the FILESTREAM filegroups that are omitted from your RESTORE statement can never be restored. To force a point-in-time restore, specify the CONTINUE_AFTER_ERROR option together with the STOPAT, STOPATMARK, or STOPBEFOREMARK option, which you must also specify in your subsequent RESTORE LOG statements. If you specify CONTINUE_AFTER_ERROR, the partial restore sequence succeeds and the FILESTREAM filegroup becomes unrecoverable.

Since the database does not contain any filestream filegroups, this should not apply. Does anyone know if point-in-time recovery is possible in my scenario?

Here is a minimal example to play with (not working):

```
------------------------------------------------------------------------
-- ONLINE PIECEMEAL RESTORE WITH POINT-IN-TIME RECOVERY
--------------------------------------

Once the primary filegroup is restored to a point-in-time and brought online, further piecemeal restores will recover to the same point. You don't need to specify STOPAT for those restores. It's a shame the error messages misled you.

A database is always brought to the same consistency point, regardless of the specific recovery process (piecemeal, online, whatever).

To recover a single filegroup after the primary is online in your scenario, first restore the filegroup from any suitable backup:

RESTORE DATABASE [RestoreTest]
    FILEGROUP = 'A'
    FROM DISK = '';

You'll get an informational message like:
Processed 32 pages for database 'RestoreTest', file 'RestoreTest_A' on file 1.
The roll forward start point is now at log sequence number (LSN) xxx.
Additional roll forward past LSN yyy is required to complete the restore sequence.
RESTORE DATABASE ... FILE= successfully processed 32 pages in 0.012 seconds.


SQL Server can't roll forward from active log, so you need to back it up:

BACKUP LOG [RestoreTest]
    TO DISK= '\Log2.bak'
    WITH INIT;

Now use that log to roll the database forward (RESTORE DATABASE works too):

RESTORE LOG RestoreTest
FROM DISK = '\Log2.bak';

The target filegroup is now online and ready for use. Other filegroups are unaffected.

Repeat this process for any other RECOVERY_PENDING filegroups.

In general, you might need log from the original database and the restoring copy to roll forward. It's best to take a tail of the log backup to ensure a complete chain.

You're complicating the process a little by using a full file backup (without covering log) instead of starting with a normal full database backup containing all filegroups and enough log to recover. It's best to start with a single full backup even if you intend to use full file backups later on.

Under the full recovery model, a complete set of full file backups, together with enough log backups to span all the file backups, is the equivalent of a full database backup.

In any case, use the simpler full database backup as a base until you have your online piecemeal restore working as you want, then add the full file backup complexity if you need to.

StackExchange Database Administrators Q#323368, answer score: 3