来自FileGroup的Sql server备份和还原

Question

我使用SQLServer2019，需要backUp &还原FileGroup。

执行步骤：

1.使用Two FilesGroup和[TestFileGroup]名称创建数据库。

2.在两个表中插入数据，一个在第一个FileGroup中，另一个在第二个FileGroup中。

3.First FullBackup [TestFileGroup].

4.然后用re-enter the data表示更改5.从FG2返回FileGroup。

6.使用FullBackup名称还原第一个[TestFileGroup_New]。

7.为了恢复，部分备份需要尾部备份。和来自TestFileGroup_新的的尾日志备份。

8.恢复FILEGROUP备份。

9.用[TestFileGroup_New]还原数据库RECOVERY

1. 引起错误！

错误文本：

Msg 4303，16级，状态1，行100前滚开始点现在在日志序列号(LSN) 37000000056800001。需要通过LSN 37000000058400001进行额外的前滚以完成恢复序列。Msg 3013、级别16、状态1、第100行还原数据库正在异常终止。

Server查询：

USE master
GO
DROP DATABASE IF EXISTS TestFileGroup
GO 
DROP DATABASE IF EXISTS TestFileGroup_New
GO 
--================================================================= Create DataBase
CREATE DATABASE TestFileGroup
ON PRIMARY 
    ( NAME = N'PRIMARY',            FILENAME = N'D:\Temp\TestDB\TestFileGroup_PRIMARY.mdf'), 
 FILEGROUP FG2 
    ( NAME = N'secondary',          FILENAME = N'D:\Temp\TestDB\TestFileGroup_secondary.ndf')
 LOG ON 
    ( NAME = N'TestFileGroup_log',  FILENAME = N'D:\Temp\TestDB\FGRestoreTEST_log.ldf')
GO
--================================================================= Insert Data(Level 1)
USE TestFileGroup
GO 
CREATE TABLE T_primary
(
    ID INT PRIMARY KEY,
    Fname NVARCHAR(100),
    Lname NVARCHAR(100)
) ON [PRIMARY]
GO
CREATE TABLE T_fg2
(
    ID INT PRIMARY KEY,
    Fname NVARCHAR(100),
    Lname NVARCHAR(100)
) ON FG2
GO
--==================================================================
USE TestFileGroup
GO 
INSERT INTO T_primary (ID,Fname,Lname) VALUES 
    (1,N'Json',N'JJ'),
    (2,N'C#',N'CC'),
    (3,N'Python',N'PP'),
    (4,N'Test1',N'TT'),
    (5,N'C++',N'C')
GO
INSERT INTO T_fg2 (ID,Fname,Lname) VALUES 
    (10,N'MMMM',N'M'),
    (20,N'SSSS',N'S'),
    (30,N'PPPPP',N'P')
GO
SELECT * FROM T_primary
SELECT * FROM T_fg2
GO
--================================================================== First Full Backup IN First year 
BACKUP DATABASE TestFileGroup  TO  DISK = N'D:\Temp\TestDB\Backup\TestFileGroup_Full.bak' 
    WITH FORMAT,COMPRESSION
GO 
RESTORE FILELISTONLY FROM DISK = N'D:\Temp\TestDB\Backup\TestFileGroup_Full.bak' 

--================================================================== Change Data
USE TestFileGroup
GO 
INSERT T_primary 
SELECT 6 ID,'Java'Fname,'jv' Lname

INSERT T_fg2 
SELECT 40 ID,'HHHH'Fname,'H' Lname

SELECT * FROM T_primary
SELECT * FROM T_fg2
--================================================================== First FileGroup Backup
USE master
GO
BACKUP DATABASE TestFileGroup FILEGROUP = N'FG2' TO  DISK = N'D:\Temp\TestDB\Backup\TestFileGroup_FG2.bak' 
    WITH FORMAT,COMPRESSION
GO
RESTORE LABELONLY FROM DISK=N'D:\Temp\TestDB\Backup\TestFileGroup_FG2.bak' 
GO
RESTORE HEADERONLY FROM DISK=N'D:\Temp\TestDB\Backup\TestFileGroup_FG2.bak' 
GO
RESTORE FILELISTONLY FROM DISK=N'D:\Temp\TestDB\Backup\TestFileGroup_FG2.bak' 
GO


--================================================================== Restore Full Backup --->TestFileGroup_New
RESTORE DATABASE TestFileGroup_New FROM DISK=N'D:\Temp\TestDB\Backup\TestFileGroup_Full.bak' WITH
MOVE 'PRIMARY'              TO N'D:\Temp\TestDB\New\TestFileGroupNew_PRIMARY.mdf',
MOVE 'secondary'            TO N'D:\Temp\TestDB\New\TestFileGroupNew_secondary.ndf',
MOVE 'TestFileGroup_log'    TO N'D:\Temp\TestDB\New\TestFileGroupNew_log.ldf',
STATS=10 ,RECOVERY

--================================================================== For Restore Partial Backup Need Tail Backup
BACKUP LOG TestFileGroup_New 
    to  DISK = N'D:\Temp\TestDB\Backup\TestFileGroup_New_Tail.trn' ---- Not Real
WITH NORECOVERY,FORMAT,COMPRESSION
GO
--================================================================== Restore FileGroup Backup
RESTORE DATABASE TestFileGroup_New FILEGROUP = N'FG2' 
    FROM  DISK = N'D:\Temp\TestDB\BackUP\TestFileGroup_FG2.bak' WITH 
NORECOVERY,REPLACE
GO 
--================================================================== 
RESTORE DATABASE TestFileGroup_New  WITH RECOVERY
/*
Msg 4303, Level 16, State 1, Line 100
The roll forward start point is now at log sequence number (LSN) 37000000056800001. Additional roll forward past LSN 37000000058400001 is required to complete the restore sequence.
Msg 3013, Level 16, State 1, Line 100
RESTORE DATABASE is terminating abnormally.
*/

Answer 1

除非还原日志文件备份，否则无法恢复。整个数据库必须通过恢复来达到一致的时点。

从医生那里：

如果文件组是读/写的，则必须将未中断的日志备份链应用于最后一次完整还原或差异还原，以便将文件组转发到当前日志文件。

分段恢复-应用日志备份

所以，就像：

RESTORE log TestFileGroup_New from  DISK = N'D:\Temp\TestDB\Backup\TestFileGroup.trn' WITH RECOVERY

此外，您也不能通过恢复将TestFileGroup的完全备份还原到TestFileGroup_new，因为如果这样做，就永远不能从TestFileGroup应用日志备份。