SQL SERVER – How To Create Multiple Filegroup For Single Database

Hi .... Friends Well Come ....

NOTE:- Frequently Asked Question In Interview.

Today I am sharing very interesting Topic with you. I always get problem in add new FileGroup in SQL Server. But today I got the solution that is How to create new and Multiple FileGroups in Single Database.

There are two way to do this thing.

1) Using T-SQL 

2) Using SSMS (Sql Server Management Studio) Wizard

Solution: Now  we will  know  How to  Updating Existing Database and Adding Filegroup

1) Creating New Database And Adding Filegroup
   

Using T-SQL

CREATE DATABASE [DBFGTest] ON  PRIMARY

( NAME = N'DBFGTest',

FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DBFGTest.mdf' ,

SIZE = 3072KB , FILEGROWTH = 1024KB ),

FILEGROUP [Secondary]

( NAME = N'DBFGTest_2',

FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DBFGTest_2.ndf' ,

SIZE = 3072KB , FILEGROWTH = 1024KB )

LOG ON

( NAME = N'DBFGTest_log',

FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DBFGTest_log.ldf' ,

SIZE = 1024KB , FILEGROWTH = 10%)

GO

 

 2) Using SSMS (Sql Server Management Studio) Wizard (Please Try This way also..)

a) Rigth click on New Database and Wirte New database Name




b) Select File option in properties (Showing on Left side in Dialog box)




c) Then 




Use this Operation	Do this operation In Showing Grid in Database Property Windows
File Name	Write the file name for the database file.
Location	Write the location of the database file, or click the ellipsis to navigate to the location.
Initial size (MB)	Write the initial size of the database file in megabytes (MB).
Filegroup	Write the name of the filegroup to which the database file will belong.

 D) Click OK Button .




This is very simple way to create Multiple filegroup in singale database ..




If you like this comment then please Comment it ...Thanks