Add-DbaDbFile

Synopsis

Adds data files to existing filegroups in SQL Server databases.

Description

Adds new data files (.mdf or .ndf) to existing filegroups in SQL Server databases. This is essential after creating new filegroups (especially MemoryOptimizedDataFileGroup for In-Memory OLTP) because filegroups cannot store data until they contain at least one file. The function supports all filegroup types including standard row data, FileStream, and memory-optimized storage, with automatic path resolution to SQL Server default data directories when no explicit path is specified.

Syntax

Add-DbaDbFile
    [[-SqlInstance] <DbaInstanceParameter[]>]
    [[-SqlCredential] <PSCredential>]
    [[-Database] <String[]>]
    [[-FileGroup] <String>]
    [[-FileName] <String>]
    [[-Path] <String>]
    [[-Size] <Int32>]
    [[-Growth] <Int32>]
    [[-MaxSize] <Int32>]
    [[-InputObject] <Database[]>]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Add-DbaDbFile -SqlInstance sql2016 -Database TestDb -FileGroup HRFG1 -FileName "HRFG1_data1"

Adds a new data file named HRFG1_data1 to the HRFG1 filegroup in the TestDb database using default size and growth settings.

Example: 2

PS C:\> Add-DbaDbFile -SqlInstance sql2016 -Database TestDb -FileGroup dbatools_inmem -FileName "inmem_container" -Path "C:\Data\inmem"

Adds a memory-optimized container to the dbatools_inmem MemoryOptimizedDataFileGroup. For memory-optimized filegroups, the Path should be a directory.

Example: 3

PS C:\> Add-DbaDbFile -SqlInstance sql2016 -Database TestDb -FileGroup Secondary -FileName "Secondary_data2" -Size 512 -Growth 128 -MaxSize 10240

Adds a new 512MB data file with 128MB growth increments and a maximum size of 10GB to the Secondary filegroup.

Example: 4

PS C:\> Get-DbaDatabase -SqlInstance sql2016 -Database TestDb | Add-DbaDbFile -FileGroup HRFG1 -FileName "HRFG1_data1"

Pipes the TestDb database and adds a new file to the HRFG1 filegroup using pipeline input.

Example: 5

PS C:\> Add-DbaDbFile -SqlInstance sql2016 -Database TestDb -FileGroup HRFG1 -FileName "HRFG1_data1" -Path "E:\SQLData\TestDb_HRFG1_data1.ndf"

Adds a new data file with a custom path and filename to the HRFG1 filegroup.

Optional Parameters

-SqlInstance

The target SQL Server instance or instances. This can be a collection and receive pipeline input to allow the function
to be executed against multiple SQL Server instances.

-SqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.

-Database

Specifies the database(s) containing the filegroup where the file will be added. Supports multiple database names for bulk operations.
Use this when you need to add files to the same filegroup across multiple databases for consistency.

-FileGroup

Specifies the name of the filegroup where the new file will be added. The filegroup must already exist in the database.
This is typically used after creating a new filegroup with New-DbaDbFileGroup, especially for MemoryOptimizedDataFileGroup which requires files before use.

-FileName

Sets the logical name for the new file being created. This name is used within SQL Server to reference the file.
If not specified, a name will be auto-generated based on the database and filegroup names to ensure uniqueness.

-Path

Specifies the full physical path where the file will be created on disk, including the filename and extension (.ndf for data files).
If not specified, the file will be placed in the SQL Server default data directory with an auto-generated filename.
For MemoryOptimizedDataFileGroup, the path should point to a directory (not a file) where the container will be created.

-Size

Sets the initial size of the file in megabytes (MB). Defaults to 128MB if not specified.
Use larger values for high-volume databases or smaller values for development/test databases to optimize storage allocation.
For MemoryOptimizedDataFileGroup, this parameter is ignored as memory-optimized filegroups manage their own sizing.

-Growth

Specifies the file growth increment in megabytes (MB). Defaults to 64MB if not specified.
This controls how much the file expands when it runs out of space, with fixed-size growth preferred over percentage-based for predictable space management.
For MemoryOptimizedDataFileGroup, this parameter is ignored as memory-optimized filegroups do not use auto-growth settings.

-MaxSize

Sets the maximum size the file can grow to in megabytes (MB). Defaults to unlimited (-1) if not specified.
Use this to prevent runaway file growth and protect disk space, particularly important on shared storage or systems with limited capacity.
For MemoryOptimizedDataFileGroup, this parameter is ignored.

-InputObject

Accepts database objects from Get-DbaDatabase for pipeline operations. This enables you to filter databases first, then add files to the selected ones.
Useful when working with multiple databases that match specific criteria rather than specifying database names directly.

-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with “sea of red” exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this “nice by default” feature off and enables you to catch exceptions with your own try/catch.

-WhatIf

Shows what would happen if the command were to run. No actions are actually performed.

-Confirm

Prompts you for confirmation before executing any changing operations within the command.