How can I create a filegroup on SQL Server attaching a bare filename?

Question

I'm trying to script a database creation from a Java program. I need to create 2 file groups, and link them to the database. In the example I can see in the official docs, I only see examples with paths, like in this case:

B. Adding a filegroup with two files to a database

The following example creates the filegroup Test1FG1 in the AdventureWorks2012 database and adds two 5-MB files to the filegroup.

USE master
GO
ALTER DATABASE AdventureWorks2012
ADD FILEGROUP Test1FG1;
GO
ALTER DATABASE AdventureWorks2012
ADD FILE
(
 NAME = test1dat3,
 FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t1dat3.ndf',
 SIZE = 5MB,
 MAXSIZE = 100MB,
 FILEGROWTH = 5MB
), 
( 
 NAME = test1dat4,
 FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t1dat4.ndf',
 SIZE = 5MB,
 MAXSIZE = 100MB,
 FILEGROWTH = 5MB
) 
TO FILEGROUP Test1FG1;
GO

However, I have no knowledge of what any user's setup would look like (and it could potentially be Windows OR Linux, so an answer I found where the person re), but I would like to add these file group files to the DATA/ folder of the installation, or some other standard/default location. To achieve that, I tried to execute the following command:

ALTER DATABASE myapp_db ADD FILEGROUP data_dg
ALTER DATABASE myapp_db ADD FILE (
 NAME=data_fg,
 FILENAME='data_fg.ndf',
 SIZE=32MB,
 MAXSIZE=4096MB,
 FILEGROWTH=32MB
) TO FILEGROUP data_fg

However, I get the following error:

com.microsoft.sqlserver.jdbc.SQLServerException: A file activation error occurred.
The physical file name 'data_fg.ndf' may be incorrect.
Diagnose and correct additional errors, and retry the operation.

Is this because SQL Server does not allow relative file paths/names for FILEGROUPs? (if so, why is it called FILENAME instead of FILEPATH?) What options exist for achieving this?

• Scott Hodgin - Retired: I'm not sure about Linux, but on Windows from SQL 2016 forward, this should give you the instance default data path - SELECT SERVERPROPERTY('InstanceDefaultDataPath') AS InstanceDefaultDataPath. You should be able to dynamically build the correct filename including the path.
• Aaron Bertrand: That is correct, SQL Server does not allow relative paths. Why is it called filename? Because it is a fully-qualified filename. If they called it filepath, you'd complain that it also had a filename at the end, and so is more than just a path. Can't win. The why is largely irrelevant though - this is the way it works, for 20+ years now, whether they had what you consider to be a logical reason, went out of their way to defy your logic, or two developers rock-paper-scissored.

Answer 1

You can't construct a path inside the ALTER DATABASE command, so you're going to have to use dynamic SQL.

If you want to just put the new data file in the default instance path, you can do as Scott suggests - use the server property for default data path:

DECLARE @dbname sysname = N'myapp_db';
DECLARE @path nvarchar(511) = 
 CONVERT(nvarchar(511), SERVERPROPERTY('InstanceDefaultDataPath');
DECLARE @sql nvarchar(max) = N'ALTER DATABASE $db$
 ADD FILEGROUP data_fg;
ALTER DATABASE $db$ ADD FILE (
 NAME=data_fg,
 FILENAME=''data_fg.ndf'',
 SIZE=32MB,
 MAXSIZE=4096MB,
 FILEGROWTH=32MB
) TO FILEGROUP data_fg;';
SET @sql = REPLACE(REPLACE(@sql, N'$path$', @p), N'$db$', @dbname);
PRINT @sql;
-- EXEC sys.sp_executesql @sql;

If, however, you want to base this off an existing database (maybe the one you're altering, if they pass that by a parameter), then you could do this:

DECLARE @dbname sysname = N'model';
DECLARE @p nvarchar(511);
;WITH x AS (SELECT TOP (1) p = physical_name, type_desc
 FROM sys.master_files
 WHERE database_id = DB_ID(@dbname)
 AND type_desc = N'ROWS'
 ORDER BY file_id
)
SELECT @p = SUBSTRING(p, 1, LEN(p)-CHARINDEX('/',REVERSE(p))+1)
FROM x;
DECLARE @sql nvarchar(max) = N'ALTER DATABASE $db$
 ADD FILEGROUP data_fg;
ALTER DATABASE $db$ ADD FILE (
 NAME=data_fg,
 FILENAME=''data_fg.ndf'',
 SIZE=32MB,
 MAXSIZE=4096MB,
 FILEGROWTH=32MB
) TO FILEGROUP data_fg;';
SET @sql = REPLACE(REPLACE(@sql, N'$path$', @p), N'$db$', @dbname);
PRINT @sql;
-- EXEC sys.sp_executesql @sql;

You may even want to allow your users to override with a specific path (think of the case where the current drive is getting crowded and they want to start this new file in a totally new location).

It also strikes me as weird that you'd bother creating a data file that is only 32 MB. That's barely big enough to hold the metadata about the file, and will surely start a cycle of autogrowths as soon as it becomes active.

• Daniel Gray: I'm basically adding MS-SQL support for something that used to be Oracle-only. I'm far from an expert DBA, and I'm going over the database creation. The Oracle DB definition has those size numbers defined in its creation script (and I am unaware of the reasoning behind it). I'm just "porting" it over to MS-SQL as closely as possible, but if one of those numbers doesn't make sense, I'm happy to get a fuller picture/better size values :) And last but not least, thanks a lot for your detailed/in-depth answer!!
• Daniel Gray: Correction: the script was only for DB creation for dev environments, so it's not critical that the numbers are optimized. Thanks for your help anyway!