| 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 a new database
This command creates a new database.
It allows creation with multiple files, and sets all growth settings to be fixed size rather than percentage growth. The autogrowth settings are obtained from the modeldev file in the model database when not supplied as command line arguments.
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 |
The name of the new database or databases to be created.
| Alias | Database |
| Required | False |
| Pipeline | false |
| Default Value |
The database collation, if not supplied the default server collation will be used.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
The recovery model for the database, if not supplied the recovery model from the model database will be used.
Valid options are: Simple, Full, BulkLogged.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | |
| Accepted Values | Simple,Full,BulkLogged |
The login that will be used as the database owner.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
The location that data files will be placed, otherwise the default SQL Server data path will be used.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
The location the log file will be placed, otherwise the default SQL Server log path will be used.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
The size in MB for the Primary file. If this is less than the primary file size for the model database, then the model size will be used instead.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 0 |
The size in MB that the Primary file will autogrow by.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 0 |
The maximum permitted size in MB for the Primary File. If this is less than the primary file size for the model database, then the model size will be used instead.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 0 |
The size in MB that the Transaction log will be created.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 0 |
The amount in MB that the log file will be set to autogrow by.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 0 |
The maximum permitted size in MB. If this is less than the log file size for the model database, then the model log size will be used instead.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 0 |
The size in MB of the files to be added to the Secondary filegroup. Each file added will be created with this size setting.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 0 |
The amount in MB that the Secondary files will be set to autogrow by. Use 0 for no growth allowed. Each file added will be created with this growth setting.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 0 |
The maximum permitted size in MB for the Secondary data files to grow to. Each file added will be created with this max size setting.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 0 |
The number of files to create in the Secondary filegroup for the database.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 0 |
Sets the default file group. Either primary or secondary.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | |
| Accepted Values | Primary,Secondary |
The data file suffix.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
The log file suffix. Defaults to "_log"
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | _log |
The secondary data file suffix.
| 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 |