New‑SqlDscDatabase - dsccommunity/SqlServerDsc GitHub Wiki

Creates a new database in a SQL Server Database Engine instance.

New-SqlDscDatabase -ServerObject <Server> -Name <String> [-Collation <String>]
 [-CatalogCollation <CatalogCollationType>] [-CompatibilityLevel <String>] [-RecoveryModel <String>]
 [-OwnerName <String>] [-IsLedger <Boolean>] [-FileGroup <DatabaseFileGroupSpec[]>] [-Force] [-Refresh]
 [-WhatIf] [-Confirm] [<CommonParameters>]

New-SqlDscDatabase -ServerObject <Server> -Name <String> -DatabaseSnapshotBaseName <String>
 [-FileGroup <DatabaseFileGroupSpec[]>] [-Force] [-Refresh] [-WhatIf]
 [-Confirm] [<CommonParameters>]

This command creates a new database in a SQL Server Database Engine instance. It supports creating both regular databases and database snapshots.

$serverObject = Connect-SqlDscDatabaseEngine -InstanceName 'MyInstance'
$serverObject | New-SqlDscDatabase -Name 'MyDatabase'

Creates a new database named MyDatabase.

$serverObject = Connect-SqlDscDatabaseEngine -InstanceName 'MyInstance'
$serverObject | New-SqlDscDatabase -Name 'MyDatabase' -Collation 'SQL_Latin1_General_Pref_CP850_CI_AS' -RecoveryModel 'Simple' -Force

Creates a new database named MyDatabase with the specified collation and recovery model without prompting for confirmation.

$serverObject = Connect-SqlDscDatabaseEngine -InstanceName 'MyInstance'
$serverObject | New-SqlDscDatabase -Name 'MyDatabaseSnapshot' -DatabaseSnapshotBaseName 'MyDatabase' -Force

Creates a database snapshot named MyDatabaseSnapshot from the source database MyDatabase without prompting for confirmation.

$serverObject = Connect-SqlDscDatabaseEngine -InstanceName 'MyInstance'

$primaryFile = New-SqlDscDataFile -Name 'MyDatabase_Primary' -FileName 'D:\SQLData\MyDatabase.mdf' -Size 102400 -Growth 10240 -GrowthType 'KB' -IsPrimaryFile -AsSpec $primaryFileGroup = New-SqlDscFileGroup -Name 'PRIMARY' -Files @($primaryFile) -IsDefault $true -AsSpec

$secondaryFile = New-SqlDscDataFile -Name 'MyDatabase_Secondary' -FileName 'E:\SQLData\MyDatabase.ndf' -Size 204800 -AsSpec $secondaryFileGroup = New-SqlDscFileGroup -Name 'SECONDARY' -Files @($secondaryFile) -AsSpec

$serverObject | New-SqlDscDatabase -Name 'MyDatabase' -FileGroup @($primaryFileGroup, $secondaryFileGroup) -Force

Creates a new database named MyDatabase with custom PRIMARY and SECONDARY file groups using specification objects created with the -AsSpec parameter. All properties are set directly via parameters without prompting for confirmation.

Specifies the collation type for the system catalog. Valid values are DATABASE_DEFAULT and SQL_Latin1_General_CP1_CI_AS. This property can only be set during database creation and cannot be modified afterward. This parameter requires SQL Server 2019 (version 15) or later.

Type: CatalogCollationType
Parameter Sets: Database
Aliases:
Accepted values: DatabaseDefault, ContainedDatabaseFixedCollation, SQLLatin1GeneralCP1CIAS

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

The name of the SQL collation to use for the new database. Default value is server collation.

Type: String
Parameter Sets: Database
Aliases:

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

The version of the SQL compatibility level to use for the new database. Default value is server version.

Type: String
Parameter Sets: Database
Aliases:

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

Specifies the name of the source database from which to create a snapshot. When this parameter is specified, a database snapshot will be created instead of a regular database. The snapshot name is specified in the Name parameter.

Type: String
Parameter Sets: Snapshot
Aliases:

Required: True
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

Specifies an array of DatabaseFileGroupSpec objects that define the file groups and data files for the database. Each DatabaseFileGroupSpec contains the file group name and an array of DatabaseFileSpec objects for the data files.

This parameter allows you to specify custom file and filegroup configurations before the database is created, avoiding the SMO limitation where DataFile objects require an existing database context.

For database snapshots, the FileName in each DatabaseFileSpec must point to sparse file locations. For regular databases, this allows full control over PRIMARY and secondary file group configurations.

Type: DatabaseFileGroupSpec[]
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

Specifies that the database should be created without any confirmation.

Type: SwitchParameter
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

Specifies whether to create a ledger database. Ledger databases provide tamper-evidence capabilities and are immutable once created. This parameter can only be set during database creation - ledger status cannot be changed after the database is created. This parameter requires SQL Server 2022 (version 16) or later, or Azure SQL Database.

Type: Boolean
Parameter Sets: Database
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

Specifies the name of the database to be created.

Type: String
Parameter Sets: (All)
Aliases:

Required: True
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

Specifies the name of the login that should be the owner of the database.

Type: String
Parameter Sets: Database
Aliases:

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

The recovery model to be used for the new database. Default value is Full.

Type: String
Parameter Sets: Database
Aliases:

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

Specifies that the ServerObject's databases should be refreshed before creating the database object. This is helpful when databases could have been modified outside of the ServerObject, for example through T-SQL. But on instances with a large amount of databases it might be better to make sure the ServerObject is recent enough.

Type: SwitchParameter
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

Specifies current server connection object.

Type: Server
Parameter Sets: (All)
Aliases:

Required: True
Position: Named
Default value: None
Accept pipeline input: True (ByValue)
Accept wildcard characters: False

Prompts you for confirmation before running the cmdlet.

Type: SwitchParameter
Parameter Sets: (All)
Aliases: cf

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

Shows what would happen if the cmdlet runs. The cmdlet is not run.

Type: SwitchParameter
Parameter Sets: (All)
Aliases: wi

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

This cmdlet supports the common parameters: -Debug, -ErrorAction, -ErrorVariable, -InformationAction, -InformationVariable, -OutVariable, -OutBuffer, -PipelineVariable, -Verbose, -WarningAction, and -WarningVariable. For more information, see about_CommonParameters.