FileGroup Backup – Full Recovery

–Create a Test Database; Add a Secondary FG and add file to the FG.
Create Database FooBarDB
GO
Alter Database FooBarDB Set Recovery Full
Alter Database FooBarDB Add FileGroup Secondary
GO
ALTER DATABASE [FooBarDB] ADD FILE
( NAME = N'FooBarDB2', FILENAME = N'E:\DATA\FooBarDB2.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Secondary]
GO

–Create a table on the Secondary FG;Insert some data and Switch it Read only mode.
use FooBarDB
Go
Create Table Stest(sno int,Sname varchar(20)) on [Secondary]
Insert into STest(sno,sname) Values(1,’Filegroup’)
GO
Use Master
ALTER DATABASE [FooBarDB] MODIFY FILEGROUP [Secondary] READONLY

–Backup Secondary FG
Backup Database FooBarDB FILEGROUP = N’Secondary’ to Disk =’E:\DATA\FooBarDB_Secondary_FG.bak’
–Create table in Primary FG and Backup Primary FG and Log. Log Backup needed for FG level restoration in Full recovery.
use FooBarDB
Go
Create Table StestPrimary(sno int,Sname varchar(20)) on [Primary]
Insert into StestPrimary(sno,sname) Values(1,’FGPrime’)

Use Master
GO
Backup Database FooBarDB FILEGROUP = N’PRIMARY’ to Disk =’E:\DATA\FooBarDB_Primary_FG2.bak’
Backup Log FooBarDB to Disk =’E:\DATA\FooBarDB_log.bak’

–Restore the Primary FG. Keyword “PARTIAL” makes the data in the Primary FG accessible.

Restore Database FooBarDB_RS From Disk=’E:\DATA\FooBarDB_Primary_FG2.bak’ WITH FILE = 1,
MOVE N’FooBarDB’ TO N’E:\DATA\FooBarDB_RS.mdf’,
MOVE N’FooBarDB_log’ TO N’E:\DATA\FooBarDB_RS_0.LDF’,
MOVE N’FooBarDB2′ TO N’E:\DATA\FooBarDB_RS_1.ndf’,
Norecovery,Partial