Backing up file groups with restore to earlier backup

I have a task: I have created a database with a filegroup lamb that has a few tables.

I need to extract a csv into a table in a filegroup named lamb and back up that filegroup.

Then extract another csv file again and back up again.

But I need to be able to restore the first backup.

What is the backup and restore method? Is it possible to restore only the lamb filegroup?

What is the backup and restore method?
It is called Piece Meal Restore and unrestored filegroups can be restored at a later time.

Is it possible to restore only the lamb filegroup?
Yes it is possible to restore only the lamb filegroup.

Below will show you - how you can do it.

-
Create a database called "FGTest"

create database [FGTest] on primary (
name = N'FGTest'
,FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\FGTest.mdf'
,SIZE = 3072 KB
,FILEGROWTH = 1024 KB
)
,FILEGROUP [lamb] (
name = N'lamb'
,-- fileGroup1
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\FGTest_2.ndf'
,SIZE = 3072 KB
,FILEGROWTH = 1024 KB
)
,FILEGROUP [lamb1] (
name = N'lamb1'
,-- fileGroup2
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\FGTest_3.ndf'
,SIZE = 3072 KB
,FILEGROWTH = 1024 KB
) LOG on (
name = N'FGTest_log'
,FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\FGTest_log.ldf'
,SIZE = 1024 KB
,FILEGROWTH = 10 %
)

go

-
Now create tables on different file groups - lamb and lamb1

create table TAB1 (
TAB1_ID int IDENTITY(1, 1)
,TAB1_NAME varchar(100)
,constraint PK_TAB1 primary key (TAB1_ID)
) on lamb -- Filegroup we created.
go

create table TAB1_lamb1 (
TAB1_ID int IDENTITY(1, 1)
,TAB1_NAME varchar(100)
,constraint PK_TAB1_lamb1 primary key (TAB1_ID)
) on lamb1 -- 2nd Filegroup we created.
go

-
Take a Base backup

-- Take a base backup
BACKUP DATABASE [FGTest] to 
DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_basebackup.bak'
with init, stats = 10
go

-
Now insert some values .... You can load a CSV at this point

INSERT INTO FGTest..TAB1(TAB1_NAME)
select ('TAB1')
union all
select ('TAB2')

-
backup filegroup lamb -- with 2 records

-- backup filegroup lamb - with 2 records

BACKUP DATABASE [FGTest] FILEGROUP = N'lamb' 
TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_2Records.bak' 
WITH  INIT,  NAME = N'FGTest-Full Filegroup Backup', 
STATS = 10
GO

-- log backup filegroup lamb - with 2 records

BACKUP LOG [FGTest] TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_2records.trn' WITH INIT

-
Now insert few more values in lamb and lamb1 file groups

INSERT INTO FGTest..TAB1(TAB1_NAME)
select ('TAB3')
union all
select ('TAB4')

INSERT INTO FGTest..TAB1_lamb1(TAB1_NAME)
select ('TAB3')
union all
select ('TAB4')

-
Now backup lamb and lamb1 filegroups along with the T-logs

-- -- backup filegroup lamb - with 4 records

BACKUP DATABASE [FGTest] FILEGROUP = N'lamb' 
TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_4Records.bak' 
WITH  INIT,  NAME = N'FGTest-Full Filegroup Backup', 
STATS = 10
GO

-- log backup filegroup lamb - with 4 records

BACKUP LOG [FGTest] TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_4records.trn' WITH INIT 

-- -- backup filegroup lamb1 - with 2 records

BACKUP DATABASE [FGTest] FILEGROUP = N'lamb1' 
TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_lamb1.bak' 
WITH  INIT,  NAME = N'FGTest-Full Filegroup Backup', 
STATS = 10
GO

-- log backup filegroup lamb1 - with 2 records

BACKUP LOG [FGTest] TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_lamb1_log.trn' WITH INIT

Now comes the restore Part :

-
Restore the database with PARTIAL and NORECOVERY

use master
 go

 -- restore the base backup with PARTIAL and NORECOVERY

 restore database [FGTest]
 filegroup = 'Primary'
 from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_basebackup.bak'
 with REPLACE, PARTIAL, NORECOVERY
 go

-
Now Restore the lamb filegroup along with the T-log

RESTORE DATABASE [FGTest] FILE = N'lamb' FROM  
 DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_2Records.bak' 
 WITH  FILE = 1, REPLACE,  STATS = 10
 GO

 RESTORE LOG [FGTest] FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_2records.trn'
 WITH FILE = 1, NORECOVERY

-- since you want to restore the database to 1st backup and you already restored the sequence, its time to bring the database back online with 2 records.

restore database [FGTest] with recovery

Since you have not brought the other file group online, when you try to query TAB1_lamb1 table residing on lamb1 filegroup, the query processor will throw an error.

StackExchange Database Administrators Q#40650, answer score: 9