FileGroup Backup – Simple Recovery

–Create a Test Database; Add a Secondary FG and add file to the FG.
Create Database FooBarDB
GO
Alter Database FooBarDB Set Recovery Simple
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.Since it is Read only FileGroup, it can be backed up and restored by itself unlike Read_Write FileGroups.
Backup Database FooBarDB FILEGROUP = N'Secondary' to Disk ='E:\DATA\FooBarDB_Secondary_FG.bak'with init
–Create table in Primary FG and In simple Recovery, all the Read_Write_FileGroups have to be backed up together.
use FooBarDB
Go
Create Table StestPrimary(sno int,Sname varchar(20)) on [Primary]
Insert into StestPrimary(sno,sname) Values(1,'FGPrime')
–In Simple Recovery, While taking FileGroup backup, all Read Write FG should be backed up.Individual Read_write Filegroups cannot be backed up.
Use Master
GO
Backup Database FooBarDB Read_Write_FileGroups to Disk ='E:\DATA\FooBarDB_RWFG_FG.bak' with init

—Restore the Read Write FileGroups Backup. Read Write Filegroups needs to be restored together in Simple Recovery.

Restore Database FooBarDB_RS From Disk='E:\DATA\FooBarDB_RWFG_FG.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',recovery

–can access data in tables present on Primary FG.
select * from FooBarDB_RS.dbo.StestPrimary
–cannot access data in tables present on Secondary FG as it has not been yet restored.
select * from FooBarDB_RS.dbo.Stest
–Restore the Secondary FileGroup
Restore Database FooBarDB_RS From Disk='E:\DATA\FooBarDB_Secondary_FG.bak'

–can access data in tables present on Secondary FG now.
select * from FooBarDB_RS.dbo.Stest

Advertisement

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

Restore Log FooBarDB_RS From Disk =’E:\DATA\FooBarDB_log.bak’
–can access data in tables present on Primary FG.
select * from FooBarDB_RS.dbo.StestPrimary
–cannot access data in tables present on Secondary FG as it has not been yet restored.
select * from FooBarDB_RS.dbo.Stest
–Restore the Secondary FileGroup
Restore Database FooBarDB_RS From Disk=’E:\DATA\FooBarDB_Secondary_FG.bak’

–can access data in tables present on Secondary FG now.
select * from FooBarDB_RS.dbo.Stest