3 added additional information
source | link

UPDATE: You added additional comments

I tried your solution, but before the restore of the database, i deleted 100 columns from the table (I had table with 1000 rows, after the delete query they remain 900). Everything else was the same. So at the end all files are accessible, but the table doesn't restored and the rows remain 900.


Have a look at SQL Server Tricks: How Can I Restore a Subset of My Tables to A Prior Point in Time? - specifically the section Why Filegroup Level Restores Don’t Solve This Problem.

Each filegroup-restore sequence restores and recovers one or more filegroups to a point consistent with the database.

Why does it have to be this way? It’s because SQL Server doesn’t track transaction status by filegroups, and its mission is to ensure transactional consistency within a database. If we could restore individual filegroups to different points in time and bring everything online, we wouldn’t have a way to ensure consistency.


UPDATE: You added additional comments

I tried your solution, but before the restore of the database, i deleted 100 columns from the table (I had table with 1000 rows, after the delete query they remain 900). Everything else was the same. So at the end all files are accessible, but the table doesn't restored and the rows remain 900.


Have a look at SQL Server Tricks: How Can I Restore a Subset of My Tables to A Prior Point in Time? - specifically the section Why Filegroup Level Restores Don’t Solve This Problem.

Each filegroup-restore sequence restores and recovers one or more filegroups to a point consistent with the database.

Why does it have to be this way? It’s because SQL Server doesn’t track transaction status by filegroups, and its mission is to ensure transactional consistency within a database. If we could restore individual filegroups to different points in time and bring everything online, we wouldn’t have a way to ensure consistency.

2 Corrected demo code
source | link

I think you might need to backup the transaction log (taillog) again at the end of your restore sequence and then restore that taillog backup.

The following demo worked for me:

USE [master]
GO

ALTER database [FGRestoreTEST] set offline with ROLLBACK IMMEDIATE;
DROP DATABASE [FGRestoreTEST]
GO


--Create sample database
CREATE DATABASE [FGRestoreTEST] ON PRIMARY (
    NAME = N'FGRestoreTEST'
    ,FILENAME = N'C:\SQLServer\FGRestoreTEST.mdf'
    ,SIZE = 5120 KB
    ,MAXSIZE = UNLIMITED
    ,FILEGROWTH = 1024 KB
    )
    ,FILEGROUP [secondaryFilegroup] (
    NAME = N'secondaryFilegroup'
    ,FILENAME = N'C:\SQLServer\secondaryFilegroup.ndf'
    ,SIZE = 5120 KB
    ,MAXSIZE = UNLIMITED
    ,FILEGROWTH = 1024 KB
    ) LOG ON (
    NAME = N'FGRestoreTEST_log'
    ,FILENAME = N'C:\SQLServer\FGRestoreTEST_log.ldf'
    ,SIZE = 2048 KB
    ,MAXSIZE = 2048 GB
    ,FILEGROWTH = 10 %
    )
GO


--Insert some test data
use [FGRestoreTEST]
go
create table TestTable (id int) on secondaryFilegroup 
insert into TestTable values(1),(2)

--Backup the secondary filegroup and then backup the transactionlog
USE [master]
GO

BACKUP DATABASE FGRestoreTEST FILEGROUP = 'secondaryFilegroup' TO DISK = N'C:\SQLServer\secondaryFilegroupBackup.bak'
WITH INIT

BACKUP LOG FGRestoreTEST TO DISK = N'C:\SQLServer\\tlog\SQLServer\tlog.trn'
WITH INIT;

--Restore the secondary filegroup and then restore the transactionlog
--taken in the previous step
use [master]
go
RESTORE DATABASE FGRestoreTEST FILEGROUP = 'secondaryFilegroup'
FROM DISK = N'C:\SQLServer\secondaryFilegroupBackup.bak'
WITH recoverynorecovery

RESTORE LOG FGRestoreTEST
FROM DISK = N'C:\SQLServer\\tlog\SQLServer\tlog.trn'
WITH recovery;

--At this point, you should have seen the messages about 
--Additional roll forward past LSN ... is required to complete the restore sequence
--Take an additional log backup and then restore with recovery
BACKUP LOG FGRestoreTEST TO DISK = N'C:\SQLServer\\Taillog\SQLServer\Taillog.trn'
WITH INIT;

RESTORE LOG FGRestoreTEST
FROM DISK = N'C:\SQLServer\\Taillog\SQLServer\Taillog.trn'
WITH recovery;

--Verify you can select from TestTable
use [FGRestoreTEST]
go

SELECT *
FROM TestTable

I think you might need to backup the transaction log (taillog) again at the end of your restore sequence and then restore that taillog backup.

The following demo worked for me:

USE [master]
GO

--Create sample database
CREATE DATABASE [FGRestoreTEST] ON PRIMARY (
    NAME = N'FGRestoreTEST'
    ,FILENAME = N'C:\SQLServer\FGRestoreTEST.mdf'
    ,SIZE = 5120 KB
    ,MAXSIZE = UNLIMITED
    ,FILEGROWTH = 1024 KB
    )
    ,FILEGROUP [secondaryFilegroup] (
    NAME = N'secondaryFilegroup'
    ,FILENAME = N'C:\SQLServer\secondaryFilegroup.ndf'
    ,SIZE = 5120 KB
    ,MAXSIZE = UNLIMITED
    ,FILEGROWTH = 1024 KB
    ) LOG ON (
    NAME = N'FGRestoreTEST_log'
    ,FILENAME = N'C:\SQLServer\FGRestoreTEST_log.ldf'
    ,SIZE = 2048 KB
    ,MAXSIZE = 2048 GB
    ,FILEGROWTH = 10 %
    )
GO


--Insert some test data
use [FGRestoreTEST]
go
create table TestTable (id int) on secondaryFilegroup 
insert into TestTable values(1),(2)

--Backup the secondary filegroup and then backup the transactionlog
USE [master]
GO

BACKUP DATABASE FGRestoreTEST FILEGROUP = 'secondaryFilegroup' TO DISK = N'C:\SQLServer\secondaryFilegroupBackup.bak'
WITH INIT

BACKUP LOG FGRestoreTEST TO DISK = N'C:\SQLServer\\tlog.trn'
WITH INIT;

--Restore the secondary filegroup and then restore the transactionlog
--taken in the previous step
use [master]
go
RESTORE DATABASE FGRestoreTEST FILEGROUP = 'secondaryFilegroup'
FROM DISK = N'C:\SQLServer\secondaryFilegroupBackup.bak'
WITH recovery

RESTORE LOG FGRestoreTEST
FROM DISK = N'C:\SQLServer\\tlog.trn'
WITH recovery;

--At this point, you should have seen the messages about 
--Additional roll forward past LSN ... is required to complete the restore sequence
--Take an additional log backup and then restore with recovery
BACKUP LOG FGRestoreTEST TO DISK = N'C:\SQLServer\\Taillog.trn'
WITH INIT;

RESTORE LOG FGRestoreTEST
FROM DISK = N'C:\SQLServer\\Taillog.trn'
WITH recovery;

--Verify you can select from TestTable
use [FGRestoreTEST]
go

SELECT *
FROM TestTable

I think you might need to backup the transaction log (taillog) again at the end of your restore sequence and then restore that taillog backup.

The following demo worked for me:

USE [master]
GO

ALTER database [FGRestoreTEST] set offline with ROLLBACK IMMEDIATE;
DROP DATABASE [FGRestoreTEST]
GO


--Create sample database
CREATE DATABASE [FGRestoreTEST] ON PRIMARY (
    NAME = N'FGRestoreTEST'
    ,FILENAME = N'C:\SQLServer\FGRestoreTEST.mdf'
    ,SIZE = 5120 KB
    ,MAXSIZE = UNLIMITED
    ,FILEGROWTH = 1024 KB
    )
    ,FILEGROUP [secondaryFilegroup] (
    NAME = N'secondaryFilegroup'
    ,FILENAME = N'C:\SQLServer\secondaryFilegroup.ndf'
    ,SIZE = 5120 KB
    ,MAXSIZE = UNLIMITED
    ,FILEGROWTH = 1024 KB
    ) LOG ON (
    NAME = N'FGRestoreTEST_log'
    ,FILENAME = N'C:\SQLServer\FGRestoreTEST_log.ldf'
    ,SIZE = 2048 KB
    ,MAXSIZE = 2048 GB
    ,FILEGROWTH = 10 %
    )
GO


--Insert some test data
use [FGRestoreTEST]
go
create table TestTable (id int) on secondaryFilegroup 
insert into TestTable values(1),(2)

--Backup the secondary filegroup and then backup the transactionlog
USE [master]
GO

BACKUP DATABASE FGRestoreTEST FILEGROUP = 'secondaryFilegroup' TO DISK = N'C:\SQLServer\secondaryFilegroupBackup.bak'
WITH INIT

BACKUP LOG FGRestoreTEST TO DISK = N'C:\SQLServer\tlog.trn'
WITH INIT;

--Restore the secondary filegroup and then restore the transactionlog
--taken in the previous step
use [master]
go
RESTORE DATABASE FGRestoreTEST FILEGROUP = 'secondaryFilegroup'
FROM DISK = N'C:\SQLServer\secondaryFilegroupBackup.bak'
WITH norecovery

RESTORE LOG FGRestoreTEST
FROM DISK = N'C:\SQLServer\tlog.trn'
WITH recovery;

--At this point, you should have seen the messages about 
--Additional roll forward past LSN ... is required to complete the restore sequence
--Take an additional log backup and then restore with recovery
BACKUP LOG FGRestoreTEST TO DISK = N'C:\SQLServer\Taillog.trn'
WITH INIT;

RESTORE LOG FGRestoreTEST
FROM DISK = N'C:\SQLServer\Taillog.trn'
WITH recovery;

--Verify you can select from TestTable
use [FGRestoreTEST]
go

SELECT *
FROM TestTable
1
source | link

I think you might need to backup the transaction log (taillog) again at the end of your restore sequence and then restore that taillog backup.

The following demo worked for me:

USE [master]
GO

--Create sample database
CREATE DATABASE [FGRestoreTEST] ON PRIMARY (
    NAME = N'FGRestoreTEST'
    ,FILENAME = N'C:\SQLServer\FGRestoreTEST.mdf'
    ,SIZE = 5120 KB
    ,MAXSIZE = UNLIMITED
    ,FILEGROWTH = 1024 KB
    )
    ,FILEGROUP [secondaryFilegroup] (
    NAME = N'secondaryFilegroup'
    ,FILENAME = N'C:\SQLServer\secondaryFilegroup.ndf'
    ,SIZE = 5120 KB
    ,MAXSIZE = UNLIMITED
    ,FILEGROWTH = 1024 KB
    ) LOG ON (
    NAME = N'FGRestoreTEST_log'
    ,FILENAME = N'C:\SQLServer\FGRestoreTEST_log.ldf'
    ,SIZE = 2048 KB
    ,MAXSIZE = 2048 GB
    ,FILEGROWTH = 10 %
    )
GO


--Insert some test data
use [FGRestoreTEST]
go
create table TestTable (id int) on secondaryFilegroup 
insert into TestTable values(1),(2)

--Backup the secondary filegroup and then backup the transactionlog
USE [master]
GO

BACKUP DATABASE FGRestoreTEST FILEGROUP = 'secondaryFilegroup' TO DISK = N'C:\SQLServer\secondaryFilegroupBackup.bak'
WITH INIT

BACKUP LOG FGRestoreTEST TO DISK = N'C:\SQLServer\\tlog.trn'
WITH INIT;

--Restore the secondary filegroup and then restore the transactionlog
--taken in the previous step
use [master]
go
RESTORE DATABASE FGRestoreTEST FILEGROUP = 'secondaryFilegroup'
FROM DISK = N'C:\SQLServer\secondaryFilegroupBackup.bak'
WITH recovery

RESTORE LOG FGRestoreTEST
FROM DISK = N'C:\SQLServer\\tlog.trn'
WITH recovery;

--At this point, you should have seen the messages about 
--Additional roll forward past LSN ... is required to complete the restore sequence
--Take an additional log backup and then restore with recovery
BACKUP LOG FGRestoreTEST TO DISK = N'C:\SQLServer\\Taillog.trn'
WITH INIT;

RESTORE LOG FGRestoreTEST
FROM DISK = N'C:\SQLServer\\Taillog.trn'
WITH recovery;

--Verify you can select from TestTable
use [FGRestoreTEST]
go

SELECT *
FROM TestTable