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

Question

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
------------------------------------------------------------------------

---------------------------------
-- CREATE DB
-- log, 2 filegroups (primary + A) and one table each
---------------------------------
USE [master]
GO

CREATE DATABASE [RestoreTest]
ON PRIMARY(
    NAME = 'PRIMARY',
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\RestoreTest.mdf'
),
FILEGROUP A(
    NAME = 'RestoreTest_A',
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\RestoreTest_A.ndf'
)
LOG ON(
    NAME = 'RestoreTest_log',
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\RestoreTest_Log.ldf'
)
GO

USE [RestoreTest]
GO

CREATE TABLE [Table1](
    [X] INT
)
ON [PRIMARY];

CREATE TABLE [Table2](
    [X] INT
)
ON [A];
GO


---------------------------------
-- Backups
---------------------------------

-- full backups of each filegroup
BACKUP DATABASE [RestoreTest]
    FILEGROUP = 'PRIMARY'
    TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreTest_Full.bak'
    WITH INIT

BACKUP DATABASE [RestoreTest]
    FILEGROUP = 'A'
    TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreTest_A_Full.bak'
    WITH INIT
GO

-- store current time for later point-in-time recovery
WAITFOR DELAY '00:00:01'
DECLARE @now DATETIME = (SELECT GETDATE())
EXEC sp_set_session_context 'stopat', @now; 
WAITFOR DELAY '00:00:01'

--insert some data
INSERT INTO [Table1]
VALUES (1)

INSERT INTO [Table2]
VALUES (1)
GO

-- then take log backup
BACKUP LOG [RestoreTest]
    TO DISK= 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreTest_Log.bak'
    WITH INIT
GO


---------------------------------
-- RESTORE (point-in-time)
---------------------------------

-- drop database
USE [master]
DROP DATABASE [RestoreTest]
GO

-- restore primary filegroup
DECLARE @stopat DATETIME = (SELECT CAST(SESSION_CONTEXT(N'stopat') AS DATETIME))

RESTORE DATABASE [RestoreTest]
    FILEGROUP = 'PRIMARY'
    FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreTest_Full.bak'
    WITH PARTIAL, NORECOVERY

RESTORE LOG [RestoreTest]
    FILEGROUP = 'PRIMARY'
    FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreTest_Log.bak'
    WITH RECOVERY, STOPAT = @stopat
GO

-- test
SELECT [name], [state_desc]
FROM sys.master_files
WHERE [database_id] = DB_ID('RestoreTest')
GO

-- restore filegroup A (with STOPAT)
DECLARE @stopat DATETIME = (SELECT CAST(SESSION_CONTEXT(N'stopat') AS DATETIME))

RESTORE DATABASE [RestoreTest]
    FILEGROUP = 'A'
    FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreTest_A_Full.bak'
    WITH NORECOVERY

RESTORE LOG [RestoreTest]
    FILEGROUP = 'A'
    FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreTest_Log.bak'
    WITH RECOVERY, STOPAT = @stopat

-- test
SELECT [name], [state_desc]
FROM sys.master_files
WHERE [database_id] = DB_ID('RestoreTest')
GO


-- try again, restore filegroup A (without STOPAT)
RESTORE DATABASE [RestoreTest]
    FILEGROUP = 'A'
    FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreTest_A_Full.bak'
    WITH NORECOVERY

RESTORE LOG [RestoreTest]
    FILEGROUP = 'A'
    FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreTest_Log.bak'
    WITH RECOVERY

-- test
SELECT [name], [state_desc]
FROM sys.master_files
WHERE [database_id] = DB_ID('RestoreTest')
GO

USE [master]
DROP DATABASE [RestoreTest]
GO

Output:

Processed 360 pages for database 'RestoreTest', file 'PRIMARY' on file 1.
Processed 7 pages for database 'RestoreTest', file 'RestoreTest_log' on file 1.
BACKUP DATABASE...FILE=<name> successfully processed 367 pages in 0.021 seconds (136.346 MB/sec).
Processed 8 pages for database 'RestoreTest', file 'RestoreTest_A' on file 1.
Processed 3 pages for database 'RestoreTest', file 'RestoreTest_log' on file 1.
BACKUP DATABASE...FILE=<name> successfully processed 11 pages in 0.011 seconds (7.457 MB/sec).

(1 row affected)

(1 row affected)
Processed 14 pages for database 'RestoreTest', file 'RestoreTest_log' on file 1.
BACKUP LOG successfully processed 14 pages in 0.005 seconds (21.093 MB/sec).
Processed 360 pages for database 'RestoreTest', file 'PRIMARY' on file 1.
Processed 7 pages for database 'RestoreTest', file 'RestoreTest_log' on file 1.
RESTORE DATABASE ... FILE=<name> successfully processed 367 pages in 0.020 seconds (143.164 MB/sec).
Processed 0 pages for database 'RestoreTest', file 'PRIMARY' on file 1.
Processed 14 pages for database 'RestoreTest', file 'RestoreTest_log' on file 1.
RESTORE LOG successfully processed 14 pages in 0.006 seconds (17.578 MB/sec).

(3 rows affected)
Processed 8 pages for database 'RestoreTest', file 'RestoreTest_A' on file 1.
RESTORE DATABASE ... FILE=<name> successfully processed 8 pages in 0.006 seconds (10.416 MB/sec).
Msg 4342, Level 16, State 1, Line 116
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.
Msg 3013, Level 16, State 1, Line 116
RESTORE LOG is terminating abnormally.

(3 rows affected)
Processed 8 pages for database 'RestoreTest', file 'RestoreTest_A' on file 1.
RESTORE DATABASE ... FILE=<name> successfully processed 8 pages in 0.005 seconds (12.500 MB/sec).
Msg 3116, Level 16, State 1, Line 134
The supplied backup is not on the same recovery path as the database, and is ineligible for use for an online file restore.
Msg 3013, Level 16, State 1, Line 134
RESTORE LOG is terminating abnormally.

(3 rows affected)

Answer 1

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 = '<source>';

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=<name> 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= '<path>\Log2.bak'
    WITH INIT;

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

RESTORE LOG RestoreTest
FROM DISK = '<path>\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.