FILEGROUP Backup in SQL Server Step by Step Tutorial with Examples

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 FULL, Backup Component as Files and FileGroups and click on “...” and Select Files and Filegroups and Click OK

• 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.

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

Related Articles

• SQL Server WHERE Clause T-SQL Tutorial with Examples
• SQL Server ORDER BY Clause T-SQL Tutorial with Examples
• SQL Server SELECT TOP Clause T-SQL Tutorial with Examples
• SQL SELECT | SQL QUERY | SQL SELECT Statement T-SQL Tutorial with Examples
• SERVERPROPERTY Function T-SQL Enhancement in SQL Server 2016
• FORMATMESSAGE Statement T-SQL Enhancement in SQL Server 2016
• STRING_SPLIT Function and STRING_ESCAPE Function in SQL Server 2016
• COMPRESS and DECOMPRESS T-SQL Enhancement in SQL Server 2016
• ALTER DATABASE SET AUTOGROW_ALL_FILES T-SQL Enhancement in SQL Server 2016
• ALTER DATABASE SET AUTOGROW_SINGLE_FILE T-SQL Enhancement in SQL Server 2016
• ALTER TABLE WITH (ONLINE=ON | OFF) T-SQL Enhancement in SQL Server 2016
• DROP IF EXISTS SQL Server T-SQL Enhancement in SQL Server 2016
• TRUNCATE TABLE WITH PARTITIONS T-SQL Enhancement in SQL Server 2016
• T-SQL Enhancements in SQL Server 2016 for Developers and DBAs
• TAIL-LOG Backup in SQL Server Step by Step Tutorial with Examples
• PARTIAL Backup in SQL Server Step by Step Tutorial with Examples