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