我有一个包含多个文件组的数据库(完整恢复模型),每个文件组都有完整备份和差异备份。我能够按照此处所述复制在线分段还原(SQl Server 2019 企业版):示例:数据库的分段还原(完整恢复模型)
但是,当我在还原最后一个日志备份时添加一个 STOPAT 子句时,这仅适用于主文件组。对于以下所有文件组,我收到此消息:
消息 4342,级别 16,状态 1,第 161 行 除非主文件组是还原序列的一部分,否则无法进行时间点恢复。省略时间点子句或恢复主文件组。
当我尝试在不使用 STOPAT 子句的情况下恢复日志时,我得到以下信息:
提供的备份与数据库不在同一个恢复路径上,并且不符合在线文件恢复的条件。
除了这部分(在Piecemeal Restores (SQL Server)上找到)之外,我无法在在线分段还原期间找到任何关于时间点恢复的限制:
如果部分还原序列排除任何 FILESTREAM 文件组,则不支持时间点还原。您可以强制恢复序列继续。但是,从 RESTORE 语句中省略的 FILESTREAM 文件组永远无法恢复。要强制执行时间点恢复,请指定 CONTINUE_AFTER_ERROR 选项以及 STOPAT、STOPATMARK 或 STOPBEFOREMARK 选项,您还必须在后续的 RESTORE LOG 语句中指定这些选项。如果您指定 CONTINUE_AFTER_ERROR,则部分恢复序列成功并且 FILESTREAM 文件组变得不可恢复。
由于数据库不包含任何文件流文件组,因此这不适用。有谁知道在我的场景中是否可以进行时间点恢复?
谢谢
编辑 3:感谢Paul White的解决方案:在将主文件组恢复到某个时间点后进行日志备份,并使用它来前滚剩余的文件组:
----------------------------------------------------------------------
-- 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 filegorup
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 filegorup
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
BACKUP LOG [RestoreTest]
TO DISK= 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreTest_Redo_Log.bak'
WITH INIT
GO
-- test
SELECT [name], [state_desc]
FROM sys.master_files
WHERE [database_id] = DB_ID('RestoreTest')
GO
-- restore filegorup A
RESTORE DATABASE [RestoreTest]
FILEGROUP = 'A'
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreTest_A_Full.bak'
WITH NORECOVERY
GO
RESTORE LOG [RestoreTest]
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreTest_Redo_Log.bak'
WITH RECOVERY
-- test
SELECT [name], [state_desc]
FROM sys.master_files
WHERE [database_id] = DB_ID('RestoreTest')
GO
编辑 1:这是一个可以玩的最小示例(不工作):
------------------------------------------------------------------------
-- 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)
一旦主文件组恢复到某个时间点并联机,进一步的零碎恢复将恢复到同一时间点。您不需要
STOPAT
为这些恢复指定。很遗憾错误消息误导了您。无论具体的恢复过程如何(零碎的、在线的,等等),数据库总是被带到相同的一致性点。
要在您的方案中主文件联机后恢复单个文件组,请首先从任何合适的备份还原文件组:
您会收到一条信息性消息,例如:
SQL Server 不能从活动日志前滚,所以你需要备份它:
现在使用该日志向前滚动数据库(
RESTORE DATABASE
也有效):目标文件组现已联机并可供使用。其他文件组不受影响。
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.
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.
唔。我似乎也无法在部分还原(即仅在主文件组上运行 RECOVERY)后获得辅助文件组的时间点还原。
这当然有效。但是主 fg 在恢复序列结束之前不会联机。对于大多数实际情况,这可能没问题,但根据文档,部分时间点恢复是可能的。