在在线零敲碎打的恢复过程中，是否有可能恢复到某个时间点？

Question

我有一个包含多个文件组的数据库(完全恢复模式)，每个文件组都有完整的和不同的备份。我能够复制在线分段恢复(SQLServer2019EnterpriseEdition)，如下所述：示例:数据库的分段恢复(完全恢复模型)

但是，当我在还原上一次日志备份时添加一个STOPAT子句时，这只适用于主文件组。对于所有以下文件组，我可以得到以下消息：

除非主文件组是恢复序列的一部分，否则无法进行Msg 4342、16级、状态1、161行实时恢复。省略时间点子句或还原主文件组.

当我尝试在没有STOPAT子句的情况下恢复日志时，我得到如下结果：

所提供的备份与数据库不在同一恢复路径上，因此不适合用于联机文件还原。

除了这一部分(在分段还原(服务器)上找到)外，我无法在在线分段恢复过程中发现对时间点恢复的任何限制：

如果部分恢复序列排除任何FILESTREAM文件组，则不支持实时恢复。您可以强制恢复序列继续。但是，无法还原您的restored语句中省略的FILESTREAM文件组。若要强制进行实时还原，请与STOPAT、STOPATMARK或STOPBEFOREMARK选项一起指定CONTINUE_AFTER_ERROR选项，您还必须在后续的还原日志语句中指定该选项。如果指定CONTINUE_AFTER_ERROR，则部分恢复序列成功，FILESTREAM文件组将不可恢复。

因为数据库不包含任何filestream文件组，所以这不应该适用。有没有人知道在我的场景中是否有时间点恢复的可能？

下面是一个很小的例子(不起作用)：

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

输出：

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

一旦主文件组恢复到实时点并联机，进一步的零碎恢复将恢复到同一点。您不需要为这些还原指定STOPAT。遗憾的是，错误信息误导了你。

无论具体的恢复过程如何(零碎的，在线的，什么的)，数据库总是被带到相同的一致性点。

若要在场景中主文件组联机后恢复单个文件组，首先从任何适当的备份还原文件组：

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

您将收到如下信息：

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.

Server无法从活动日志中前滚，因此需要备份它：

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

现在使用该日志将数据库前滚(RESTORE DATABASE也能工作)：

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

目标文件组现在已联机，可供使用。其他文件组不受影响。

对任何其他RECOVERY_PENDING文件组重复此过程。

通常，您可能需要从原始数据库进行日志记录，并将还原副本前滚。最好是选择日志备份的尾部，以确保完整的链。

使用全文件备份 (不包括日志)，而不是从包含所有文件组和足够恢复日志的正常完整数据库备份开始，会使进程变得有点复杂。最好从一个完整备份开始，即使您打算稍后使用完整的文件备份。

在完全恢复模型下，一组完整的文件备份，加上足够的日志备份，可以跨越所有文件备份，相当于一个完整的数据库备份。

在任何情况下，都要使用简单的完整数据库备份作为基础，直到您的联机分段还原按您的需要工作为止，如果需要，则添加完整的文件备份复杂性。