How to Create a Filegroup on SQL Server with a Bare Filename

sql serversql-server-2017

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?

Best Answer

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.

Related Question