New-DbaDbFileGroup

Want to see the source code for this command? Check out New-DbaDbFileGroup on GitHub.
Want to see the Bill Of Health for this command? Check out New-DbaDbFileGroup.

Synopsis

Creates new filegroups in SQL Server databases for custom data storage organization.

Description

Creates a new filegroup for the specified database(s), supporting standard row data, FileStream, and memory-optimized storage types. This is useful when you need to separate table storage across different disk drives for performance optimization, implement compliance requirements, or organize data by department or function. The filegroup is created empty and requires adding data files with Add-DbaDbFile before it can store data. Use Set-DbaDbFileGroup to configure advanced properties like read-only status or default settings after files are added.

Syntax

New-DbaDbFileGroup
    [[-SqlInstance] <DbaInstanceParameter[]>]
    [[-SqlCredential] <PSCredential>]
    [[-Database] <String[]>]
    [[-FileGroup] <String>]
    [[-FileGroupType] <String>]
    [[-InputObject] <Database[]>]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> New-DbaDbFileGroup -SqlInstance sqldev1 -Database TestDb -FileGroup HRFG1

Creates the HRFG1 filegroup on the TestDb database on the sqldev1 instance with the default options for the filegroup.

Example: 2

PS C:\> New-DbaDbFileGroup -SqlInstance sqldev1 -Database TestDb -FileGroup HRFG1 -FileGroupType FileStreamDataFileGroup

Creates a filestream filegroup named HRFG1 on the TestDb database on the sqldev1 instance.

Example: 3

PS C:\> New-DbaDbFileGroup -SqlInstance sqldev1 -Database TestDb -FileGroup HRFG1 -FileGroupType MemoryOptimizedDataFileGroup

Creates a MemoryOptimized data filegroup named HRFG1 on the TestDb database on the sqldev1 instance.

Example: 4

PS C:\> Get-DbaDatabase -SqlInstance sqldev1 -Database TestDb | New-DbaDbFileGroup -FileGroup HRFG1

Passes in the TestDB database via pipeline and creates the HRFG1 filegroup on the TestDb database on the sqldev1 instance.

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) where the new filegroup will be created. Supports multiple database names for bulk operations.
Use this when you need to create the same filegroup structure across multiple databases for consistency.

-FileGroup

Sets the name for the new filegroup being created. The name must be unique within the database and follow SQL Server naming conventions.
Use descriptive names like 'HR_Data' or 'Archive_FG' to indicate the data's purpose or department for better organization.

-FileGroupType

Defines the storage type for the filegroup: RowsFileGroup for regular tables and indexes, FileStreamDataFileGroup for FILESTREAM data like documents and images, or MemoryOptimizedDataFileGroup for
In-Memory OLTP tables.
Most scenarios use the default RowsFileGroup unless you're specifically implementing FILESTREAM or In-Memory OLTP features.

-InputObject

Accepts database objects from Get-DbaDatabase for pipeline operations. This enables you to filter databases first, then create filegroups on 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.