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.

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