Add a filegroup at database creation on SQL 2014

See more:

file

SQL-Server-2014

Hi,

i have a client that is trying to enforce filegroups when new databases are created. AS the admin doesnt create the database and is done by a 3rd Party Software via the 'Create Database' command, we do not have the ability to enforce it.
We tried a server tigger monitoring for the create database command but then found you cannot alter a database due to it being in a trigger or in a multi statement transaction.

I did toy with the idea of programmitcally adding a step to a job, from the server trigger that would run after a few seconds but not sure if this would work,

given that SQL server uses filegroups heavily im surprisedthere isnt an easier way to configure this, at least not that i can see.

So essentially when a 'CREATE DATABASE' command is run, we would want to add a filegroup with multiple files over a different disks.

I have built the below which works when i run it after the database is created however this requires constant admin int database is created ?y way to do this when the database is created ?

Appreciate any help you can give.

What I have tried:

Here is the code for creating the filegroup (note this isnt the final product its just a test one we drew up to tests the disks etc.

SET @SQL = 
	'
		USE master
		ALTER DATABASE ' + @DatabaseName + '
		ADD FILEGROUP FG_' + @DatabaseName + ';


		BEGIN TRANSACTION
		ALTER DATABASE ' + @DatabaseName + '

		ADD FILE
		(
			NAME = FGF_' + @DatabaseName + '_1,
			FILENAME = ''F:\SQLDATA\FGF_' + @DatabaseName + '_1.ndf'',
			SIZE = 50MB,
			FILEGROWTH = 50MB
		),
		(
			NAME = FGF_' + @DatabaseName + '_2,
			FILENAME = ''G:\SQLDATA\FGF_' + @DatabaseName + '_2.ndf'',
			SIZE = 50MB,
			FILEGROWTH = 50MB
		),
		(
			NAME = FGF_' + @DatabaseName + '_3,
			FILENAME = ''H:\SQLDATA\FGF_' + @DatabaseName + '_3.ndf'',
			SIZE = 50MB,
			FILEGROWTH = 50MB
		),
		(
			NAME = FGF_' + @DatabaseName + '_4,
			FILENAME = ''I:\SQLDATA\FGF_' + @DatabaseName + '_4.ndf'',
			SIZE = 50MB,
			FILEGROWTH = 50MB
		),
		(
			NAME = FGF_' + @DatabaseName + '_5,
			FILENAME = ''J:\SQLDATA\FGF_' + @DatabaseName + '_5.ndf'',
			SIZE = 50MB,
			FILEGROWTH = 50MB
		),
		(
			NAME = FGF_' + @DatabaseName + '_6,
			FILENAME = ''K:\SQLDATA\FGF_' + @DatabaseName + '_6.ndf'',
			SIZE = 50MB,
			FILEGROWTH = 50MB
		),
		(
			NAME = FGF_' + @DatabaseName + '_7,
			FILENAME = ''L:\SQLDATA\FGF_' + @DatabaseName + '_7.ndf'',
			SIZE = 50MB,
			FILEGROWTH = 50MB
		)
		TO FILEGROUP  FG_' + @DatabaseName + ';
	';

    EXEC (@SQL);