I Use Sql Server 2019 And need to backUp & Restore the FileGroup.
Execution steps :
1.Create The Database With Two FilesGroup and [TestFileGroup] name.
2.Insert data in two tables, one in First FileGroup and the other in the second FileGroup .
3.First FullBackup [TestFileGroup].
4.Then re-enter the data for changes
5.BackUP FileGroup From [FG2] .
6.Restore First FullBackup With [TestFileGroup_New] name.
7.For Restore Partial Backup Need Tail Backup . and Tail Log Backup From [TestFileGroup_New].
8.Restore FILEGROUP Backup .
9.restore Database [TestFileGroup_New] with RECOVERY
10. raise error !
Error Text :
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.
Sql Server Query :
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.
*/
