0

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.
*/


1 Answer 1

3

You can't RECOVER until you restore the log file backup. The whole database must be brought up to a consistent point-in-time by WITH RECOVERY.

After the File Group restore FG2 is ahead of FG1, and restoring the log backup will replay the missing transactions into FG1.

From the docs:

If the filegroup is read/write, an unbroken chain of log backups must be applied to the last full or differential restore to bring the filegroup forward to the current log file.

Piecemeal Restores - Applying Log Backups

So something like:

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

Also you can't restore full backup of TestFileGroup to TestFileGroup_new WITH RECOVERY, because if you do, you can never apply log backups from TestFileGroup.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.