In SQL Server, databases store their data and objects (such as tables, views, stored procedures, etc) in files. When you create a database using the default options, one data file and one log file are created. However, you’re not restricted to just one of each file. You can add data files (and log files) to a database at a later date if required.

This article contains examples of using T-SQL to add a data file to an existing database in SQL Server.

Example

Here’s an example of adding a single data file to an existing database:

USE master;  
GO

ALTER DATABASE Solutions   
ADD FILE 
(  
    NAME = Solutions_dat_2,  
    FILENAME = '/var/opt/mssql/data/Solutions_dat_2.ndf',  
    SIZE = 10MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5%  
);
GO

This adds a data file with a logical name of Solutions_dat_2, and with a physical path of /var/opt/mssql/data/Solutions_dat_2.ndf (this is the physical location on the computer).

Note that this example uses Linux/Mac file paths. If you use Windows, you’ll need to use backslashes (\) instead of forward slashes (/).

Here’s an explanation of the various arguments used in this example:

NAME

Specifies the logical file name. This is the logical name used in an instance of SQL Server when referencing the file.

FILENAME

This is the full physical path to the file (i.e. the operating system’s path to the file).

SIZE

Specifies the file size. Can be specified in KB, MB, GB, or TB.

MAXSIZE

The maximum file size to which the file can grow. Can be specified in KB, MB, GB, TB, or UNLIMITED.

FILEGROWTH

The automatic growth increment of the file (the amount of space added to the file every time new space is required). Can be specified in KB, MB, GB, TB, or %.

The default is MB (for megabytes).

Check the Result

We can check the result by running the following query:

USE Solutions;  
GO

SELECT name, physical_name  
FROM sys.database_files;  
GO

Result:

name             physical_name                          
---------------  ---------------------------------------
Solutions        /var/opt/mssql/data/Solutions.mdf      
Solutions_log    /var/opt/mssql/data/Solutions_log.ldf  
Solutions_dat_2  /var/opt/mssql/data/Solutions_dat_2.ndf

We can see the new data file listed in addition to the original data file (and log file).

Of course, you can also use the asterisk (*) to return all columns if you wish.

If you find that one day you no longer need it, you can always remove your new data file.