T-SQL to create the File Group and Files

Hi Friends,

Below is the simple T-SQL to create File Group and Files for a database and adding multiple files under that file group. Here you can specify values as per your requirement like:

1- Database Name under which you wants to add file group.
2- Name of the file group.
3- Number of files, you wants to create under the file group.
4- Path where these files will be stored.
5 Initial allocated Size and growth size for each file.

If you want, You can modify the code to pass all these values through parameters. Keep in mind to change the values as per your requirement before using it.

Create Procedure sp_FilegroupandFileCreator
AS
Begin
	Declare @DatabaseName VARCHAR(100);
	Declare @NoOfFiles smallint;
	Declare @Query VARCHAR(2000);
	Declare @FileGroupName VARCHAR(100);
	Declare @PathofFiles VARCHAR(1000);
	Declare @InitialSizeinMB INT;
	Declare @FileGrowthinMB INT;

	--Specify the database Name here
	SET @DatabaseName='Test';
	
	--Specify the file group name
	SET @FileGroupName='DataGroup';
	
	--Specify the Number of files you wants to create for above file group
	SET @NoOfFiles=2;
	
	--Specify the path where you wants to create new files
	SET @PathofFiles='C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016CTP3\MSSQL\DATA';

	--Provide the Initial Size and File growth values in MB for the files.
	SET @InitialSizeinMB = 512;
	SET @FileGrowthinMB = 256;

			--Create table to store information about DriveLetters free space
			
			if object_id('tempdb..#StorageDriveLetters') is not null
				drop table #StorageDriveLetters;

			Create table #StorageDriveLetters
			(
			DriveLetter CHAR(1),
			MBFree BIGINT 
			);

			--Create table to store information about FileGroupExistance of filegroup
			if object_id('tempdb..#FileGroupDetails') is not null
					drop table #FileGroupDetails;

			Create table #FileGroupDetails	
			(
			FileGroupExistance INT
			);
		
			--Collect the DriveLetter level information
			INSERT INTO #StorageDriveLetters
				EXEC master..xp_fixedDrives

			--Check the database name exist or not
			IF (Select count(*) from sys.databases where name=@DatabaseName)<>1
				Begin
							SELECT 'Specified Database does not exist on the system.';
							Return;
				End
				
			--Check the Number of files, if specified number of files is less than 0 then print the message and stop	
			IF (@NoOfFiles<0)
				Begin
							SELECT 'Number of files can not be less than zero.';
							Return;
				End
				
			--Check the FileGroupExistance of DriveLetter from specified path 		
			IF (Select count(*) from #StorageDriveLetters where DriveLetter=LEFT(@PathofFiles, 1))<>1
				Begin
							SELECT 'Specified DriveLetter in path does not exist.';
							Return;
				End
			
			--Prepare the Query to check the flegroup FileGroupExistance
			SET @Query='Select COUNT(*) from ' + @DatabaseName+ '.sys.filegroups where name=''' +  @FileGroupName +'''' ;
			
			INSERT INTO #FileGroupDetails
			EXEC (@QUERY);
	
			--If Filegroup already exist then show the message and exit otherwise create the filegroup
			IF (Select FileGroupExistance from #FileGroupDetails)=1
				Begin
						SELECT 'Filegroup already exist';
						Return;
				End
			ELSE
				Begin
						SET @Query='ALTER DATABASE ' + @DatabaseName+ ' ADD FILEGROUP ' + @FileGroupName;
						EXEC (@Query)
				End
		
			--Now try to add specified number of files (if greater than one)
			WHILE (@NoOfFiles>0)
				BEGIN
						SET @Query='ALTER DATABASE ' + @DatabaseName+
						' ADD FILE ( NAME =''' + @FileGroupName + '_' + CONVERT(VARCHAR(2),@NoOfFiles) +'''' + 
						', FILENAME = ''' + @PathofFiles + '\' + @FileGroupName + '_' + CONVERT(VARCHAR(2),@NoOfFiles) + '.ndf'+ '''' + 
						', SIZE = ' + CONVERT(VARCHAR(10),@InitialSizeinMB) + 'MB' +
						', FILEGROWTH = ' + CONVERT(VARCHAR(10),@FileGrowthinMB) + 'MB' +
						' ) TO FILEGROUP ' + @FileGroupName;

						EXEC (@Query)
						
						SET @NoOfFiles=@NoOfFiles-1;
				End
End

I used this to create a file group and files for my test database. Below is the image which shows, those created file group and files.

   

Output1

HAPPY LEARNING!

Regards:
Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook

   

About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

Leave a Reply

Your email address will not be published.