I am elated to receive hundreds of emails every day from my readers. My tight work schedule refrains me from answering all your questions, but I do try my best to entertain them whenever I can. Today’s post revolves around a question I received a number of times last year but never blogged on it. On positive side, you are reading about that interesting subject today.
The question is – How to create multiple filegroup for any database?
To find solution to this query, we will go through the following four cases.
1) Creating New Database
a) Using T-SQL
b) Using SSMS
2) Updating Existing Database and Adding Filegroup
a) Using T-SQL
b) Using SSMS
Let us go over each of the cases one by one.
1) Creating New Database
a) Using T-SQL
CREATEÂ DATABASE [FGTest] ONÂ Â PRIMARY
( NAME = N'FGTest',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\FGTest.mdf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ),
FILEGROUPÂ [Secondary]
( NAME = N'FGTest_2',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\FGTest_2.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'FGTest_log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\FGTest_log.ldf' ,
SIZE = 1024KB , FILEGROWTH = 10%)
GO
b) Using SSMS
2) Updating Existing Database and Adding Filegroup
Updating existing database is much simpler compared with adding filegroup to new database. Let us take a look at how quickly it can be completed in a few simple steps.
a) Using T-SQL
We will add a Third filegroup to the same database where we added secondary filegroup in the above example.
USE [master]
GO
ALTERÂ DATABASE [FGTest] ADD FILEGROUPÂ [Third]
GO
b) Using SSMS
Right click on the database and go to properties and filegroup. Add new filegroup by simply adding name.
I encourage all my readers to keep mailing me send in your questions and doubts. I will certainly take them up at some point of time.
Reference : Pinal Dave (https://blog.sqlauthority.com)
42 Comments. Leave new
Hello Dave,
I am New to MS sql, But I find interesting in Database servers, As I would like to learn the Concepts of Administration the SQL server as will as how efficient can I make the server work.
Can you Please Help me in it
regards
SreeMon
Hi Dave,
I’m very new to this technology. In the above you told about
Create Multiple Filegroup For Single Database..i have small query how to add table to this new filegroup…
Hi Dave,
How i will check records in particular file group.
Can you please help?
Regards
Sutanu
Filegroup is based on database.Is it wisable to partition table into hortizontally or can we make a different filegroup for single Table? I know its funny to ask.
Hello Joggee,
Asking question is not funny and you asked a very good question. You are comparing two methods (partitioning and storing a table on separate filegroup) to split the table data over multiple disks files.
If a filegroup has multiple files and you store a table on this filegroup, table’s data would be splitted over all files. This is a method to improve the I/O performance of a table. But data splitting over multiple files is not visible to user and we can not use this method to work upon a section on data.
Partitioning is another way to split the data over multiple filegroup (and internally over multiple files). But in this method we know which record is stored in which filegroup. The partitions of data are visible to user and we can perform few tasks like SELECT, index defragmentation, etc. on a single partition also.
Regards,
Pinal Dave
Pinal Dave,
I have a question and it is not related to this topic. But, it is related to filegroup. Kindly, explain how to take backup of a filegroup and restore.
Please explain step by step with images. I will be grateful to you.
Mushtaq
How could i add a table to a specific filegroup ?
Hello Zafar,
At the time of creation you can specify the filegroup as below:
CREATE TABLE tableName (columns specification…) ON filegroup
An existing table (heap) can be moved to another filegroup by creating the clustered index on new filegroup as below:
CREATE CLUSTERED INDEX indexName ON tableName (column…) ON filegroup
Now if the clustered index is not required then it can be dropped.
If table already have a clustered index then create the clustered index with DROP_EXISTING option.
Regards,
Pinal Dave
Hi,
I want to add file group dynamically.
Ex.
When date change i execute script for create file group as per date.
Declare @FileName varchar(50)
Set @FileName=convert(varchar,getdate(),112)
print @FileName
ALTER DATABASE VehicleTrackingSystem ADD FILEGROUP [@FileName]
Above script create file group with name @FileName which is wrong.
Please look in this problem.
Regards,
Parthesh
Thanks for the article….The screenshots really helped..
Thanks Dave.
Hi! Can you help me: i want to move all my tables to new filegroup, i’ve moved all tables with Primary key, but what shoud i do with tables without PK and tables with text columns? sorry for my english
Hi Dave,
Will you please explain what is the relationship between filegroup and table is it a One-to-One or One-to-many. Can we write multiple tables into single filegroup which has multiple files?? If we can how to retrieve multiple tables data from a single file which is in a filegroup?? Is my question is ODD?
for example i have a database which has three large tables A ,B and C. i want to partition month by month data for june,july,aug,sep for each table A,B and C . To achieve this do i need to create 12 files and 12 filegroups for the 3 tables?? Please Explain.
Thanks
Siri
Sir
Pl tell me how to take backup and restore of pricegroup in
SQL Server2005
Hi Dave,
I am working with win server 2003 with SQL Server 2005. I wanted to try back ups(just for working knowledge sake) and due to limited resources i wanted to try this using 2 instance of SQL rather than 2 different servers.
I had database on default instance and i wanted to restore it on named instance 1. Since i want to maintain the same name of the database i tried to create a database with same name and i got an error which i was expecting because i already had a database from named instance on the PRIMARY file group. Hence while creating the database on the instance 1 i created a new file group and changed it to default. However i had to create a new file called mydatabase_1.mdf and only then the process was sucessful. However while i was creating the datbase it still had the mydatabase.mdf and mydatabase_log.ldf, the third file i created was mydatabase_1.
I am confused as to which will would have been present on which file group.
Should have thought better. Each instance has its own PRIMARY file group and hence databases with same name could be created. I am not sure why SQL threw me an error but i did the entire process again and i was able to create and back up the database as i desired.
hi sir
i have a WINDOS SERVER 2008 AND MICROSOFT SQL CAL 2008 R2 SNGL.
I HAVE 10 USERS AND I WANT TO KNOW HOW CAN I MAKAE
A DATABASE FOR EACH USER CAN YOU HELP ME PLEASE
Thanks for the article….The screenshots really helped..
Please give and article for how can we online restore from filegroup.
Thanks Dave.
hi sir
I was very good
Hi, i have created a secondary filegroup, but this was created with the same size of primary filegroup and i dont know what is the problem, now my database meisures is 6gb and before it was 3gb and sql not allow me delete the secondary filegroup. Can you help me?
excuse my bad languaje, mi native is spanish.
Thanks so much. This article is so helpful.
hello this is zuber,
i would like to know “is this possible to store aparticular table under particular file of filegroup” and please tell me how to backup particular filegroup