Friday 9 December 2016

Create an user define file group and take backup for it :

There are two types of SQL Server filegroups: primary and user-defined.
Primary filegroup includes all pages for system tables, primary data file and all other files that are not part of any other filegroup.
A filegroup that is created using FILEGROUP keyword in CREATE DATABASE or ALTER DATABASE statement belongs to user-defined filegroup.

create database Test
go

To add a new filegroup to a database use the following command:
ALTER DATABASE Test
ADD FILEGROUP Group1;
GO
ALTER DATABASE Test
ADD FILE (NAME = 'DataFile',
FILENAME = 'C:\DataFile.ndf', SIZE = 2GB, FILEGROWTH = 10GB) TO FILEGROUP Group1;
BACKUP DATABASE Test FILEGROUP = 'Group1'
TO DISK = 'C:\Group1.FLG'
we can apply another method to backup my file group 
The second way to backup database filegroup is to use SQL Server Management Studio (SSMS): 
  • Right click on the database where the filegroup you want to backup is located
  • Select “Tasks”, then “Back Up…”
  • Select  backup type (“Full” or “Differential”)
  • Select “Files and filegroups”
  • Choose filegroup and click “OK”
  • Add backup destination
  • Click “OK”

No comments:

Post a Comment