How to restore a file group from file group backup?

Question

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;

Answer 1

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.

Answer 2

I upvoted Scott Hodgin's answer but I want to clarify what happens here and give an alternative solution.

The problem here is that your filegroup is not readonly so it can be changed between the time of your last log backup and the beginning of the restore.

And you can make infinite number of log backups, but there will not be one that guarantees that it comprends all the changes until the moment when you begin to restore.

That's why you should take this last log backup in such a way that server knows that after it there is no change to any file of the filegroup in question. So the first obvious solution is to take this log backup when filegroup's files are no more accessible, they are in the middle of restore.

And this is how Scott Hodgin's solution works: he takes last log backup when filegroup files are in restoring state.

But you can do it in other way: you can set your filegroup's files offline just before you are taking last log backup, doing so you no more need to take a log backup after you began to restore.

Here is the complete solution using Scott Hodgin's code:

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

alter database [FGRestoreTEST] set recovery full
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

/***************************** SET FILES OF YOUR FILEGROUP OFFLINE *****************************/
alter database [FGRestoreTEST]
modify file (name = N'secondaryFilegroup', offline);

/***************************** NOW TAKE A LOG BACKUP *****************************/

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;

use [FGRestoreTEST]
select name, state_desc
from sys.database_files;

--name  state_desc
--FGRestoreTEST ONLINE
--FGRestoreTEST_log ONLINE
--secondaryFilegroup    ONLINE