I am scripting out our database and because we have multiple stacks of drives assigned to each file group we have the files split into multiple files per file group also to split the IO / distribute storage among drives.
This currently is in SQL Server 2017 / SSDT in VS 2019
We always gave logical names to our database that started with the name of the database like how it is default in SSMS ex "MyDbName_FileGroup" then the file name similar ex: "MyDbName_FileGorup.ndf" but we never scripted this part before, we manually set that up.
I would like to get this scripted as part of the SSDT Deployment package so it can be used to set up new DB's easily also.
Everything is great so far made scripts for each file group that will create the files, but it of course will not let me use a SQLCMD variable as part of an object name.
So trying this
ALTER DATABASE [$(DatabaseName)]
ADD FILE
(
NAME=[$(DatabaseName)_FileGroupName],
FILENAME= '$(DefaultDataPath)$(DefaultFilePrefix)_FileGroupName.mdf'
) TO FILEGROUP [MESSAGING];
GO
does not work since I can't prepend the database name to the logical name like I want it to.
Yes this is purely cosmetic to match a pattern, but how would you go about doing something like this in SSDT?