SQL Server Articles, SQL Server Tips, SQL Server Tutorials, SQL Server Tuning, SQL Server DBA, SQL Server Basics, Training, etc - MyTechMantra.com

FILEGROUP Backup in SQL Server Step by Step Tutorial with Examples

Previous Page.. Begin Tutorial.. Next Page..

Using FILEGROUP backup one can backup all the data files within the SQL Server FILEGROUP individually. While backing up the database you can specify whole of FILEGROUP (i.e., PRIMARY or SECONDARY) instead of specifying each database file individually within the FILEGROUP.

This is Part 14 of 16 Part SQL Server Database Backup Tutorial. Click here to read it from the beginning….

TSQL Script to Create Database with Multiple Data Files in Primary and Read Only File in Secondary File Group

USE [master]
GO

CREATE DATABASE [MyTechMantra]
CONTAINMENT = PARTIAL
ON PRIMARY 
( 
NAME = N'MyTechMantra', 
FILENAME = N'F:\MSSQL\DATA\MyTechMantra.mdf', 
SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB 
), 
( 
NAME = N'MyTechMantra_Data2', 
FILENAME = N'F:\MSSQL\DATA\MyTechMantra_Data2.ndf', 
SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB 
), 
FILEGROUP [ReadOnly] 
( 
NAME = N'MyTechMantra_ReadOnly', 
FILENAME = N'F:\MSSQL\DATA\MyTechMantra_ReadOnly.ndf',
SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB 
)
LOG ON 
( 
NAME = N'MyTechMantra_log', 
FILENAME = N'F:\MSSQL\DATA\MyTechMantra_log.ldf', 
SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%
)
GO

Create a FULL FILEGROUP backup Using TSQL command

In this example we will backup Read Only Data File which is available in Secondary File Group.

BACKUP DATABASE [MyTechMantra] 
FILEGROUP = N'ReadOnly' 
TO DISK = N'C:\DBBackups\MyTechMantra_MyTechMantra_ReadOnly.bck' 
WITH NOFORMAT, NOINIT, COMPRESSION, STATS = 25
GO

Create a FULL FILEGROUP Database Backup Using SQL Server Management Studio

  • Open SQL Server Management Studio, connect to the appropriate SQL Server Database Instance, in Object Explorer, click the SERVER NAME to expand the server tree.
  • Expand Databases node and then right click the database which you want to take a FULL FILE Database backup.
  • Select Tasks, and then click Back Up… option as shown to open up Back Up Database dialog box
  • Select Backup Type as FULLBackup Component as Files and FileGroups and click on “...” and Select Files and Filegroups and Click OK
Select File and FileGroups in SQL Server to Backup ReadOnly FileGroup
  • Click on Add button to add a backup file and specify the backup file name and click OK to save the changes.
  • Finally to take FULL FILEGROUP  Backup of ReadOnly FileGroup click OK.
Create a FULL FILEGROUP Database Backup Using SQL Server Management Studio

Clicking Next Page button to continue reading the topics and click on the Previous Page button to revisit the previous topic.

Previous Page.. Begin Tutorial.. Next Page..

Related Articles

Chetna Bhalla

LESS ME MORE WE

Chetna Bhalla, the founder of MyTechMantra.com, believes that by sharing knowledge and building communities, we can make this world a better place to live in. Chetna is a Graduate in Social Sciences and a Masters in Human Resources and International Business. She is an alumnus of Vignana Jyothi Institute of Management, Hyderabad, India. After graduation, Chetna founded this website, which has since then become quite a favorite in the tech world. Her vision is to make this website the favorite place for seeking information on Databases and other Information Technology areas. She believes that companies which can organize and deploy their data to frame strategies are going to have a competitive edge over others. Her interest areas include Microsoft SQL Server and overall Database Management. Apart from her work, Chetna enjoys spending time with her friends, painting, gardening, playing the violin, and spending time with her son.

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Advertisement