commands

^

New-DbaDatabase

Author Matthew Darwin (@evoDBA, naturalselectiondba.wordpress.com) , Chrissy LeMaire (@cl)
Availability Windows, Linux, macOS

 

Synopsis

Creates a new database

Description

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.

The generated database filenames take the form:

_PRIMARY _Log _MainData_1 (Secondary filegroup files)

Syntax

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>]

 

Examples

 

Example: 1
PS C:\> New-DbaDatabase -SqlInstance sql1

Creates a randomly named database (random-N) on instance sql1

Example: 2
PS C:\> New-DbaDatabase -SqlInstance sql1 -Name dbatools, dbachecks

Creates a database named dbatools and a database named dbachecks on sql1

Example: 3
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

Example: 4
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.

Example: 5
PS C:\> New-DbaDatabase -SqlInstance sql1 -Name newDb -LogSize 32 -LogMaxSize 512 -PrimaryFilesize 64 -PrimaryFileMaxSize 512 -SecondaryFilesize 64 -SecondaryFileMaxSize 512 -LogGrowth 32

-PrimaryFileGrowth 64 -SecondaryFileGrowth 64
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)

Required Parameters

-SqlInstance

The target SQL Server instance or instances.

Alias
Required True
Pipeline true (ByValue)
Default Value

Optional Parameters

-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.

Alias
Required False
Pipeline false
Default Value
-Name

The name of the new database or databases to be created.

Alias Database
Required False
Pipeline false
Default Value
-Collation

The database collation, if not supplied the default server collation will be used.

Alias
Required False
Pipeline false
Default Value
-RecoveryModel

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
-Owner

The login that will be used as the database owner.

Alias
Required False
Pipeline false
Default Value
-DataFilePath

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
-LogFilePath

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
-PrimaryFilesize

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
-PrimaryFileGrowth

The size in MB that the Primary file will autogrow by.

Alias
Required False
Pipeline false
Default Value 0
-PrimaryFileMaxSize

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
-LogSize

The size in MB that the Transaction log will be created.

Alias
Required False
Pipeline false
Default Value 0
-LogGrowth

The amount in MB that the log file will be set to autogrow by.

Alias
Required False
Pipeline false
Default Value 0
-LogMaxSize

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
-SecondaryFilesize

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
-SecondaryFileGrowth

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
-SecondaryFileMaxSize

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
-SecondaryFileCount

The number of files to create in the Secondary filegroup for the database.

Alias
Required False
Pipeline false
Default Value 0
-DefaultFileGroup

Sets the default file group. Either primary or secondary.

Alias
Required False
Pipeline false
Default Value
Accepted Values Primary,Secondary
-DataFileSuffix

The data file suffix. Defaults to "_PRIMARY"

Alias
Required False
Pipeline false
Default Value _PRIMARY
-LogFileSuffix

The log file suffix. Defaults to "_Log"

Alias
Required False
Pipeline false
Default Value _Log
-SecondaryDataFileSuffix

The secondary data file suffix. Defaults to "_MainData"

Alias
Required False
Pipeline false
Default Value _MainData
-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.

Alias
Required False
Pipeline false
Default Value False
-WhatIf

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
-Confirm

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

 

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.