Filegroups are basically divided into two parts which are known as primary and secondary filegroups. And today in this article we are going to discuss that how you can add filegroups in a database at the time of creation or after creating the database. In this I would not be using any GUI platform, all the steps will be done through coding only. First I will give a brief intro about filegroups and their uses in SQL servers.
What are Filegroups in a Database?
filegroups are used to create logical groups of tablespaces that can be assigned to different disks. They are often used to isolate transaction logs for a given database. Filegroups also provide a way to specify the location of data files for specific databases, which may be replicated to separate servers or storage devices.
A database can have any number of filegroups. Each filegroup has one or more data files and zero or more log files associated with it. A given table can only belong to one file group at a time. Tablespace data files are assigned within the file group.
Filegroups are a way of logically grouping data within a database. They’re useful for organizing your data and for allowing you to have finer-grained control over how data is stored in your database. For example, you might have a filegroup for each of your domains so that you can have data associated with a specific website in one filegroup, and data for all of your other websites in a different filegroup. This way, you can keep related data together and avoid creating multiple tables for the same information.
How to add Filegroups on a Database using Queries?
To add a filegroup in a database using SQL Server (MSSQL) you have to first create a database and you can also add different filegroups to the existing database. I will now tell you that how to add filegroups in a database using queries which is a professional and most recommended way to do so.
1- Adding Filegroups while creating Database
create database setarning1
ON Primary --- On primary filegroup/default filegroup
(
Name=MyDb1, -- you can give any logical name here
Filename='D:\Se Training\mydb1.mdf',
size=5MB
),
(
Name=MyDb3, -----------In Filegroup1
Filename='D:\Se Training\mydb3.ndf',
size=5MB
),
LOG ON -----------Log cannot be in Filegroup
(
Name=MyDBLog1,
Filename='D:\Se Training\mydbLog1.ldf',
size=5MB
)
It will add 3 filegroups to your database and it can be used for different types of files that you will be going to save on your database. Remember that .mdf files are meant to be kept on primary filegroups and .ndf files are meant to be stored in the secondary filegroup. Hence you can also store .ndf files in primary but it is not recommended method by Microsoft as in the future it will create a mess for you to distinguish a file from a filegroup.
2- Adding Filegroups in an existing Database
Alter Database myDb
Add file
(
Name=MyDB4,
Filename='D:\Se Training\mydb4.ndf',
Size=5MB
)
TO Filegroup [Filegroupname]
If you want to add the filegroup to an existing database you have to alter the database to create those filegroups to distinguish the files later on.
How to check filegroups of a Database using Queries?
Now to confirm that the filegroups have been successfully created or not, you have to write a simple stored procedure in your query windows which are given below-
sp_helpdb [setarning1]
.png)
0 Comments