Backup and Restore FileGroup

Step1. Create a db with two file groups

CREATE DATABASE [FGBackup]
 CONTAINMENT = NONE
 ON PRIMARY
( NAME = N'FGBackup', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup.mdf' , SIZE = 4096KB , FILEGROWTH = 1024KB ),
 FILEGROUP [Secondary]
( NAME = N'FGBackup_Sec', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_Sec.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB ),
 FILEGROUP [FGB]
( NAME = N'FGBackup_FGB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_FGB.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'FGBackup_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

Step2. Create two tables, one on each FG

USE [FGBackup]
GO
Create Table FGTesting_Primary
(ID int Identity(1,1),
TestingData Varchar(50),
CreatedDate DateTime ) ON [Primary]

Create Table FGTesting_Secondary
(ID int Identity(1,1),
TestingData Varchar(50),
CreatedDate DateTime ) ON [Secondary]

Create Table FGTesting_FGB
(ID int Identity(1,1),
TestingData Varchar(50),
CreatedDate DateTime ) ON [FGB]

Step3. Insert a row into each table

Insert into FGTesting_Primary values('TestingBefore1',GetDate())
Insert into FGTesting_Primary values('TestingBefore2',GetDate())
Insert into FGTesting_Primary values('TestingBefore3',GetDate())
Insert into FGTesting_Secondary values('TestingBefore1',GetDate())
Insert into FGTesting_Secondary values('TestingBefore2',GetDate())
Insert into FGTesting_Secondary values('TestingBefore3',GetDate())
Insert into FGTesting_FGB values('TestingBefore1',GetDate())
Insert into FGTesting_FGB values('TestingBefore2',GetDate())
Insert into FGTesting_FGB values('TestingBefore3',GetDate())

Select * From FGTesting_Primary
Select * From FGTesting_Secondary
Select * From FGTesting_FGB

Step4. Backup FG 1

Backup Database FGBackup
FileGroup = 'Primary'
To Disk = 'D:\FG\Priamry.bck'

Backup Database FGBackup
FileGroup = 'Primary',
FileGroup = 'Secondary'
To Disk = 'D:\FG\Secondary.bck'

Backup Database FGBackup
FileGroup = 'Primary',
FileGroup = 'FGB'
To Disk = 'D:\FG\FGB.bck'

Step5. Restore as a new DB

RESTORE DATABASE [FGBackup_Primary]
FILE = N'FGBackup'
FROM DISK = N'D:\FG\Priamry.bck'
WITH FILE = 1,
MOVE N'FGBackup' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_Primary.mdf',
MOVE N'FGBackup_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_Primary_1.ldf',
NOUNLOAD, STATS = 10
GO
RESTORE DATABASE [FGBackup_Secondary]
FILE = N'FGBackup',
FILE = N'FGBackup_Sec'
FROM DISK = N'D:\FG\Secondary.bck'
WITH FILE = 1,
MOVE N'FGBackup' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_Primary1.mdf',
MOVE N'FGBackup_Sec' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_Secondary.mdf',
MOVE N'FGBackup_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_Secondary_1.ldf',
NOUNLOAD, STATS = 10
GO

RESTORE DATABASE [FGBackup_FGB]
FILE = N'FGBackup',
FILE = N'FGBackup_FGB'
FROM DISK = N'D:\FG\FGB.bck'
WITH FILE = 1,
MOVE N'FGBackup' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_Primary2.mdf',
MOVE N'FGBackup_FGB' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_FGB.mdf',
MOVE N'FGBackup_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_Secondary_2.ldf',
NOUNLOAD, STATS = 10
GO

Step6. One table should be accessible. Other should be down

use [FGBackup_Primary]
select * from FGTesting_Primary
select * from FGTesting_Secondary Step Not available
select * from FGTesting_FGB Step Not available

use [FGBackup_Secondary]
select * from FGTesting_Primary
select * from FGTesting_Secondary
select * from FGTesting_FGB Step Not available
use [FGBackup_FGB]
select * from FGTesting_Primary
select * from FGTesting_Secondary Step Not available
select * from FGTesting_FGB