Sql-server – How to restore a file group from file group backup

filegroupsrestoresql server

I want to restore a specific file group from file group backup. I found examples how to restore it from full backup, but I want to do it from file group backup. Every time when I try, all of the files in the file group stuck in 'Restoring' state and cannot be accessed any more.
The way I'm trying is:

Creating Backups:

BACKUP DATABASE myDataBase FILEGROUP = 'secondaryFilegroup' TO DISK = N'D:\DBBackups\secondaryFilegroupBackup.bak' WITH INIT
BACKUP LOG myDataBase TO DISK = N'D:\DBBackups\tlog.trn';

Restoring:

RESTORE DATABASE myDataBase FILEGROUP = 'secondaryFilegroup' FROM DISK = N'D:\DBBackups\secondaryFilegroupBackup.bak'
RESTORE LOG myDataBase FROM DISK = N'D:\DBBackups\tlog.trn';

Error: The log in this backup set terminates at LSN 4323000025691200001, which is too early to apply to the database. A more recent log backup that includes LSN 4323000025741600001 can be restored.

Can someone explain me what are the correct steps?

UPDATE
It looks like you misunderstood me. Sorry for my bad explanation and my late response. My task is to create a database with a file group with 3 files. Then I have to create a table in this file group. I have to make backup of the file group, then delete some rows and then have to restore the file group and the table must be as the beginning.
So i create a File group named "secondaryFilegroup" and a table in it named "testTable" with 1000 rows. Then I deleted 100 rows(so at the table remain 900) and restored the file group (so I expect to have again 1000 rows). But with this solution that you gave me I still have 900 rows.
My solution is based on answer from @Scott Hodgin.
PS: Im not sure is this even possible. 

BACKUP DATABASE myDatabase FILEGROUP = 'secondaryFilegroup' TO DISK = N'E:\DBBackups\secondaryFilegroupBackup.bak' WITH INIT
BACKUP LOG myDatabase TO DISK = N'E:\DBBackups\tlog.trn' WITH INIT;

DELETE TOP(100) FROM testTable;

use master;

RESTORE DATABASE myDatabase FILEGROUP = 'secondaryFilegroup' FROM DISK = N'E:\DBBackups\secondaryFilegroupBackup.bak' WITH norecovery

RESTORE LOG myDatabase 
FROM DISK = N'E:\DBBackups\tlog.trn'
WITH recovery;

BACKUP LOG myDatabase TO DISK = N'E:\DBBackups\ttomov_test\SZH\tlog2.trn' WITH INIT;

RESTORE LOG myDatabase 
FROM DISK = N'E:\DBBackups\ttomov_test\SZH\tlog2.trn'
WITH recovery;

Best Answer

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

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.

Related Question