Functions/GenXdev.Data.SqlServer/New-SQLServerDatabase.ps1
|
<##############################################################################
Part of PowerShell module : GenXdev.Data.SqlServer Original cmdlet filename : New-SQLServerDatabase.ps1 Original author : René Vaessen / GenXdev Version : 3.23.2026 ################################################################################ Copyright (c) René Vaessen / GenXdev Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License. ################################################################################> # don't remove this line [dontrefactor] ############################################################################### <# .SYNOPSIS Creates a new SQL Server database. .DESCRIPTION Creates a new SQL Server database with the specified name on the specified server. Requires appropriate permissions to create databases on the target SQL Server instance. If the database already exists, the operation will be skipped. Supports file-based database creation with explicit .mdf/.ldf paths. .PARAMETER DatabaseName The name of the SQL Server database to create. Must be a valid SQL Server database name. .PARAMETER Server The SQL Server instance name where the database should be created. Defaults to '.' (local instance). .PARAMETER ConnectionString Alternative connection string to use instead of Server parameter. Should connect to master database or have appropriate permissions. .PARAMETER DataFilePath Optional path for the database data file (.mdf). When specified, creates a file-based database at this location instead of using default SQL Server paths. .PARAMETER LogFilePath Optional path for the database log file (.ldf). If not specified but DataFilePath is provided, defaults to same directory with _log.ldf suffix. .PARAMETER DetachAfterCreation Detach the database after creation. Typically used with DataFilePath to create a portable database file that can be attached via connection string. .PARAMETER ForceConsent Force a consent prompt even if a preference is already set for SQL Server package installation, overriding any saved consent preferences. .PARAMETER ConsentToThirdPartySoftwareInstallation Automatically consent to third-party software installation and set a persistent preference flag for SQL Server package, bypassing interactive consent prompts. .EXAMPLE New-SQLServerDatabase -DatabaseName "MyNewDatabase" -Server "localhost" .EXAMPLE New-SQLServerDatabase "MyNewDatabase" .EXAMPLE New-SQLServerDatabase -DatabaseName "MyDB" -ConnectionString "Server=.;..." .EXAMPLE New-SQLServerDatabase -DatabaseName "ImageIndex" -DataFilePath "C:\Data\ImageIndex.mdf" -DetachAfterCreation .EXAMPLE New-SQLServerDatabase -DatabaseName "MyDB" -Server "." -ConsentToThirdPartySoftwareInstallation #> function New-SQLServerDatabase { [CmdletBinding(SupportsShouldProcess = $true)] [Alias('nsqldb')] param ( ######################################################################## [Parameter( Mandatory = $true, Position = 0, ParameterSetName = 'DatabaseName', HelpMessage = 'The name of the SQL Server database to create' )] [ValidateNotNullOrEmpty()] [string]$DatabaseName, ######################################################################## [Parameter( Position = 1, ParameterSetName = 'DatabaseName', HelpMessage = 'The SQL Server instance name' )] [string]$Server = '.', ######################################################################## [Parameter( Mandatory = $true, Position = 0, ParameterSetName = 'ConnectionString', HelpMessage = 'The connection string to connect to SQL Server' )] [string]$ConnectionString, ######################################################################## [Parameter( Mandatory = $false, HelpMessage = 'Force a consent prompt even if preference is set for SQL Server package installation.' )] [switch] $ForceConsent, ######################################################################## [Parameter( Mandatory = $false, HelpMessage = 'Automatically consent to third-party software installation and set persistent flag for SQL Server package.' )] [switch] $ConsentToThirdPartySoftwareInstallation, ######################################################################## [Parameter( Mandatory = $false, ParameterSetName = 'DatabaseName', HelpMessage = 'Optional data file path (.mdf) for file-based database creation.' )] [string]$DataFilePath, ######################################################################## [Parameter( Mandatory = $false, ParameterSetName = 'DatabaseName', HelpMessage = 'Optional log file path (.ldf) for file-based database creation.' )] [string]$LogFilePath, ######################################################################## [Parameter( Mandatory = $false, ParameterSetName = 'DatabaseName', HelpMessage = 'Detach database after creation (for file-based databases).' )] [switch]$DetachAfterCreation ######################################################################## ) begin { # load SQL Server client assembly with embedded consent using Copy-IdenticalParamValues $ensureParams = GenXdev.FileSystem\Copy-IdenticalParamValues ` -BoundParameters $PSBoundParameters ` -FunctionName 'GenXdev.Helpers\EnsureNuGetAssembly' ` -DefaultValues ( Microsoft.PowerShell.Utility\Get-Variable -Scope Local ` -ErrorAction SilentlyContinue ) # Set specific parameters for SQL Server package $ensureParams['PackageKey'] = 'System.Data.SqlClient' $ensureParams['Description'] = 'SQL Server client library required for database operations' $ensureParams['Publisher'] = 'Microsoft' GenXdev.Helpers\EnsureNuGetAssembly @ensureParams Microsoft.PowerShell.Utility\Add-Type -AssemblyName System.Data.SqlClient # prepare connection string if ($PSCmdlet.ParameterSetName -eq 'DatabaseName') { $masterConnectionString = "Server=$Server;Database=master;Integrated Security=true;TrustServerCertificate=true" } else { $masterConnectionString = $ConnectionString } } process { # build a meaningful should process message $targetObject = 'SQL Server database' $action = 'Create' $target = if ($PSCmdlet.ParameterSetName -eq 'DatabaseName') { "$DatabaseName on $Server" } else { "database via connection string" } # check if user wants to proceed with operation if ($PSCmdlet.ShouldProcess($target, "$action $targetObject")) { try { # create connection to master database to check if target database exists $connectionClass = "System.Data.SqlClient.SqlConnection" $connection = Microsoft.PowerShell.Utility\New-Object $connectionClass($masterConnectionString) $connection.Open() # check if database already exists $checkQuery = if ($PSCmdlet.ParameterSetName -eq 'DatabaseName') { "SELECT database_id FROM sys.databases WHERE name = @DatabaseName" } else { # For connection string mode, we'll try to connect directly and see if it fails $null } if ($checkQuery) { $checkCommand = $connection.CreateCommand() $checkCommand.CommandText = $checkQuery $checkCommand.Parameters.AddWithValue("@DatabaseName", $DatabaseName) $existingDb = $checkCommand.ExecuteScalar() if ($null -eq $existingDb) { # Build CREATE DATABASE statement if ($DataFilePath) { # File-based database with explicit paths $dbName = $DatabaseName -replace '-', '_' -replace ' ', '_' $logPath = if ($LogFilePath) { $LogFilePath } else { $DataFilePath -replace '\.mdf$', '_log.ldf' } # Ensure parent directories exist $dataDir = [System.IO.Path]::GetDirectoryName($DataFilePath) if (-not [System.IO.Directory]::Exists($dataDir)) { [System.IO.Directory]::CreateDirectory($dataDir) | Microsoft.PowerShell.Utility\Out-Null } $logDir = [System.IO.Path]::GetDirectoryName($logPath) if (-not [System.IO.Directory]::Exists($logDir)) { [System.IO.Directory]::CreateDirectory($logDir) | Microsoft.PowerShell.Utility\Out-Null } $createQuery = @" CREATE DATABASE [$DatabaseName] ON PRIMARY ( NAME = N'${dbName}', FILENAME = N'${DataFilePath}', SIZE = 8MB, FILEGROWTH = 64MB ) LOG ON ( NAME = N'${dbName}_log', FILENAME = N'${logPath}', SIZE = 8MB, FILEGROWTH = 64MB ) "@ } else { # Standard database with default file locations $createQuery = "CREATE DATABASE [$DatabaseName]" } $createCommand = $connection.CreateCommand() $createCommand.CommandText = $createQuery $createCommand.ExecuteNonQuery() Microsoft.PowerShell.Utility\Write-Verbose "Successfully created database '$DatabaseName' on server '$Server'" # Set recovery model to SIMPLE to avoid excessive log file growth $recoveryCommand = $connection.CreateCommand() $recoveryCommand.CommandText = "ALTER DATABASE [$DatabaseName] SET RECOVERY SIMPLE" $recoveryCommand.ExecuteNonQuery() Microsoft.PowerShell.Utility\Write-Verbose "Set recovery model to SIMPLE for database '$DatabaseName'" # Detach if requested (typically for file-based databases) if ($DetachAfterCreation) { $detachCommand = $connection.CreateCommand() $detachCommand.CommandText = "EXEC sp_detach_db @dbname = N'$DatabaseName'" $detachCommand.ExecuteNonQuery() Microsoft.PowerShell.Utility\Write-Verbose "Detached database '$DatabaseName' from server '$Server'" } } else { Microsoft.PowerShell.Utility\Write-Verbose "Database '$DatabaseName' already exists on server '$Server'" } } $connection.Close() } catch { if ($null -ne $connection -and $connection.State -eq 'Open') { $connection.Close() } throw "Failed to create database. Error: $($_.Exception.Message)" } } } end { } } |