| Author | Matthew Darwin (@evoDBA, naturalselectiondba.wordpress.com) , Chrissy LeMaire (@cl) |
| Availability | Windows, Linux, macOS |
Want to see the source code for this command? Check out New-DbaDatabase on GitHub.
Want to see the Bill Of Health for this command? Check out New-DbaDatabase.
Creates new SQL Server databases with customizable file layout and growth settings
Creates new databases on SQL Server instances with full control over file placement, sizing, and growth settings. Rather than using T-SQL CREATE DATABASE statements manually, this function provides a structured approach to database creation with built-in best practices.
The function automatically configures growth settings to use fixed MB increments instead of percentage-based growth, which prevents runaway autogrowth issues in production environments. When specific file sizes aren't provided, it inherits sensible defaults from the model database to ensure new databases start with appropriate baseline configurations.
Supports creating databases with secondary filegroups and multiple data files for performance optimization, making it useful for both simple development databases and complex production systems that require specific file layouts for optimal I/O distribution.
New-DbaDatabase
[-SqlInstance] <DbaInstanceParameter[]>
[[-SqlCredential] <PSCredential>]
[[-Name] <String[]>]
[[-Collation] <String>]
[[-RecoveryModel] <String>]
[[-Owner] <String>]
[[-DataFilePath] <String>]
[[-LogFilePath] <String>]
[[-PrimaryFilesize] <Int32>]
[[-PrimaryFileGrowth] <Int32>]
[[-PrimaryFileMaxSize] <Int32>]
[[-LogSize] <Int32>]
[[-LogGrowth] <Int32>]
[[-LogMaxSize] <Int32>]
[[-SecondaryFilesize] <Int32>]
[[-SecondaryFileGrowth] <Int32>]
[[-SecondaryFileMaxSize] <Int32>]
[[-SecondaryFileCount] <Int32>]
[[-DefaultFileGroup] <String>]
[[-DataFileSuffix] <String>]
[[-LogFileSuffix] <String>]
[[-SecondaryDataFileSuffix] <String>]
[-EnableException]
[-WhatIf]
[-Confirm]
[<CommonParameters>]
PS C:\> New-DbaDatabase -SqlInstance sql1
Creates a randomly named database (random-N) on instance sql1
PS C:\> New-DbaDatabase -SqlInstance sql1 -Name dbatools, dbachecks
Creates a database named dbatools and a database named dbachecks on sql1
PS C:\> New-DbaDatabase -SqlInstance sql1, sql2, sql3 -Name multidb, multidb2 -SecondaryFilesize 20 -SecondaryFileGrowth 20 -LogSize 20 -LogGrowth 20
Creates two databases, multidb and multidb2, on 3 instances (sql1, sql2 and sql3) and sets the secondary data file size to 20MB, the file growth to 20MB and the log growth to 20MB for each
PS C:\> New-DbaDatabase -SqlInstance sql1 -Name nondefault -DataFilePath M:\Data -LogFilePath 'L:\Logs with spaces' -SecondaryFileCount 2
Creates a database named nondefault and places data files in in the M:\data directory and log files in "L:\Logs with spaces".
Creates a secondary group with 2 files in the Secondary filegroup.
PS C:\> $databaseParams = @{
>> SqlInstance = "sql1"
>> Name = "newDb"
>> LogSize = 32
>> LogMaxSize = 512
>> PrimaryFilesize = 64
>> PrimaryFileMaxSize = 512
>> SecondaryFilesize = 64
>> SecondaryFileMaxSize = 512
>> LogGrowth = 32
>> PrimaryFileGrowth = 64
>> SecondaryFileGrowth = 64
>> DataFileSuffix = "_PRIMARY"
>> LogFileSuffix = "_Log"
>> SecondaryDataFileSuffix = "_MainData"
>> }
>> New-DbaDatabase @databaseParams
Creates a new database named newDb on the sql1 instance and sets the file sizes, max sizes, and growth as specified. The resulting filenames will take the form:
newDb_PRIMARY
newDb_Log
newDb_MainData_1 (Secondary filegroup files)
The target SQL Server instance or instances.
| Alias | |
| Required | True |
| Pipeline | true (ByValue) |
| Default Value |
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.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Specifies the name of the database(s) to create on the SQL Server instance. Accepts multiple database names as an array to create several databases in a single operation. If not provided, creates a
database with a randomly generated name like "random-12345".
| Alias | Database |
| Required | False |
| Pipeline | false |
| Default Value |
Specifies the collation for the new database, which determines sorting rules, case sensitivity, and accent sensitivity for string data. Use this when creating databases that need specific language or
cultural sorting requirements different from the server default. If not specified, inherits the server's default collation.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Sets the recovery model which determines how transaction log backups work and how much data loss is acceptable. Simple recovery model doesn't require log backups but limits point-in-time recovery,
Full enables complete point-in-time recovery with log backups, BulkLogged offers a compromise for bulk operations. If not specified, inherits from the model database.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | |
| Accepted Values | Simple,Full,BulkLogged |
Specifies which SQL Server login will be assigned as the database owner (dbo). Use this to assign ownership to a specific service account or administrator instead of the default creator. The login
must already exist on the SQL Server instance. If not specified, the connecting user becomes the database owner.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Specifies the directory path where database data files (.mdf and .ndf) will be created. Use this when you need to place database files on specific drives for performance or storage management. If not
specified, uses the SQL Server instance's default data directory. The function will create the directory if it doesn't exist.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Specifies the directory path where transaction log files (.ldf) will be created. Best practice is to place log files on separate drives from data files for performance and availability. If not
specified, uses the SQL Server instance's default log directory. The function will create the directory if it doesn't exist.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Sets the initial size in MB for the primary data file (.mdf). Use this to create databases with appropriate initial sizing based on expected data volume to reduce autogrowth events. If the specified
size is smaller than the model database's primary file, the model size is used instead to maintain minimum requirements.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 0 |
Specifies the autogrowth increment in MB for the primary data file when it needs more space. Using fixed MB increments prevents runaway percentage-based growth that can cause performance issues and
disk space problems in production environments. If not specified, inherits the model database's growth settings.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 0 |
Sets the maximum size limit in MB that the primary data file can grow to during autogrowth events. Use this to prevent database files from consuming all available disk space in case of runaway
processes or data imports. If set smaller than the initial file size, the initial size becomes the maximum.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 0 |
Sets the initial size in MB for the transaction log file (.ldf). Proper log sizing prevents frequent autogrowth during normal operations which can impact performance. Size the log based on your
transaction volume and backup frequency - larger logs for high-activity databases or infrequent log backups. If not specified, uses the model database's log size.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 0 |
Specifies the autogrowth increment in MB for the transaction log file when additional space is needed. Fixed MB growth prevents percentage-based growth that can cause performance delays during
high-activity periods. Consider setting this to handle your typical transaction volume between log backups. If not specified, uses the model database's growth settings.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 0 |
Sets the maximum size limit in MB for the transaction log file during autogrowth. This prevents transaction logs from consuming all disk space during bulk operations or when log backups are delayed.
Consider your available disk space and typical maintenance windows when setting this limit. If set smaller than the initial log size, the initial size becomes the maximum.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 0 |
Sets the initial size in MB for each file in the secondary filegroup. All secondary files will be created with this same size. Use this to establish consistent file sizes across the filegroup for
balanced I/O distribution. If not specified and secondary files are created, uses the model database's file size.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 0 |
Specifies the autogrowth increment in MB for each secondary data file when additional space is needed. All secondary files will use the same growth increment to maintain balanced file sizes. Set to 0
to disable autogrowth for controlled file management. Fixed MB increments provide predictable growth behavior.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 0 |
Sets the maximum size limit in MB for each secondary data file during autogrowth. This prevents individual files from consuming excessive disk space while allowing controlled growth. All secondary
files will use this same maximum size limit to maintain consistency across the filegroup.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 0 |
Specifies how many data files to create in the secondary filegroup. Multiple files allow parallel I/O operations which can improve performance for large databases with high activity. Consider your
storage configuration and available CPU cores when determining the file count - typically one file per CPU core up to the number of available drives.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 0 |
Specifies which filegroup becomes the default for new tables and indexes when no filegroup is explicitly specified. Primary uses the standard PRIMARY filegroup, Secondary uses the created secondary
filegroup. Setting the secondary filegroup as default directs new objects to use the optimized multi-file configuration for better performance.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | |
| Accepted Values | Primary,Secondary |
Specifies a custom suffix to append to the primary data file name. The full filename becomes DatabaseName + DataFileSuffix + .mdf. Use this to follow organizational naming conventions or to
differentiate between environments (like "_PROD" or "_DEV"). If not specified, no suffix is added.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Specifies the suffix to append to the transaction log file name. The full filename becomes DatabaseName + LogFileSuffix + .ldf. Use this to follow naming conventions that distinguish log files from
data files. Defaults to "_log" if not specified, creating files like "MyDatabase_log.ldf".
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | _log |
Specifies the suffix used for the secondary filegroup name and its data files. The filegroup becomes DatabaseName + SecondaryDataFileSuffix, and files are named like DatabaseName +
SecondaryDataFileSuffix + "_1.ndf". Use descriptive suffixes like "_Data" or "_Indexes" to indicate the intended use of the secondary filegroup.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
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.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
If this switch is enabled, no actions are performed but informational messages will be displayed that explain what would happen if the command were to run.
| Alias | wi |
| Required | False |
| Pipeline | false |
| Default Value |
If this switch is enabled, you will be prompted for confirmation before executing any operations that change state.
| Alias | cf |
| Required | False |
| Pipeline | false |
| Default Value |