New-SQLDatabase.ps1
|
function New-SQLDatabase { <# .Synopsis Creates a new SQL database .Description Creates a database in SQL server, SQL azure, SQLCompact, or Sqlite .Example New-SqlDatabase "Test DB" .Example New-SqlDatabase "Test DB" -ComputerName TheSqlServer .Link Select-SQL .Link Update-SQL #> [CmdletBinding(DefaultParameterSetName='SqlServer')] [OutputType([Nullable])] param( # The name of the new database [Parameter(Mandatory=$true,ParameterSetName='SqlServer',ValueFromPipelineByPropertyName=$true)] [Parameter(Mandatory=$true,ParameterSetName='MySql',ValueFromPipelineByPropertyName=$true)] [string] $DatabaseName, # The name of the SQL server. By default, the local machine [Parameter(ParameterSetName='SqlServer',ValueFromPipelineByPropertyName=$true)] [Alias('CN')] [string] $ComputerName = $env:COMPUTERNAME, # If set, will use MySql to connect to the database [Parameter(Mandatory=$true,ParameterSetName='MySql')] [Switch] $UseMySql, # The path to MySql's .NET connector. If not provided, MySql will be loaded from Program Files [Parameter(ParameterSetName='MySql')] [string] $MySqlPath, # The log folder for the database [Parameter(ParameterSetName='SqlServer')] [string] $DatabaseLogFolder, # The log folder for the database [Parameter(ParameterSetName='SqlServer')] [string] $DatabaseDataFolder, # The initial size of the database. By default, 10mb [Parameter(ParameterSetName='SqlServer')] [uint32] $InitialDatabaseSize = 10mb, # The maximum size of the database [Parameter(ParameterSetName='SqlServer')] [uint32] $MaximumDatabaseSize, # The initial size of the database logs. By default, this is 1mb [Parameter(ParameterSetName='SqlServer')] [uint32] $InitialLogSize = 1mb, # The size at which the log files will grow. By default, this is 1mb [Parameter(ParameterSetName='SqlServer')] [uint32] $LogFileGrowth =1mb, # The size at which the database will grow. By default, this is 5mb [Parameter(ParameterSetName='SqlServer')] [uint32] $DatabaseGrowth =5mb, # The maximum log size [Parameter(ParameterSetName='SqlServer')] [uint32] $MaximumLogSize, # If set, will not generate any core SQL server statistical information [Parameter(ParameterSetName='SqlServer')] [Alias('NoStats')] [Switch] $NoStat, # If set, will keep a log of all transactions to the database [Parameter(ParameterSetName='SqlServer')] [Switch] $KeepTransactionLog, # If set, will output the SQL, but will not run the command [Parameter(ParameterSetName='SqlServer')] [Switch] $OutputSQL, # If set, will create a SQLite database [Parameter(Mandatory=$true,ParameterSetName='Sqlite')] [Alias('UseSqlLite')] [Switch] $UseSqlite, # The path to SQLite. If not set, will import SQLite from program files [Parameter(ParameterSetName='Sqlite')] [string] $SqlitePath, # If set, will use SQL compact [Parameter(Mandatory=$true,ParameterSetName='SqlCompact')] [Switch] $UseSqlCompact, # The path the SQL compact. If not provided, SQL compact will be loaded from the GAC. [Parameter(ParameterSetName='SqlCompact')] [string] $SqlCompactPath, # The path to the database file. [Parameter(Mandatory=$true,ParameterSetName='SqlCompact')] [Parameter(Mandatory=$true,ParameterSetName='Sqlite')] [string] $DatabasePath, # A connection string or a setting containing a connection string. [Alias('ConnectionString', 'ConnectionSetting')] [string]$ConnectionStringOrSetting ) process { if ($PSBoundParameters.ConnectionStringOrSetting) { if ($ConnectionStringOrSetting -notlike "*;*") { $ConnectionString = Get-SecureSetting -Name $ConnectionStringOrSetting -ValueOnly } else { $ConnectionString = $ConnectionStringOrSetting } $script:CachedConnectionString = $ConnectionString } elseif ($ComputerName) { $connectionString = "Data Source=$ComputerName;Initial Catalog=Master;Integrated Security=SSPI;" } elseif ($script:CachedConnectionString){ $ConnectionString = $script:CachedConnectionString } else { $ConnectionString = "" } if ($UseSQLCompact) { #Region Create SQL Compact DB if (-not ('Data.SqlServerCE.SqlCeConnection' -as [type])) { if ($SqlCompactPath) { $resolvedCompactPath = $ExecutionContext.SessionState.Path.GetResolvedPSPathFromPSPath($SqlCompactPath) $asm = [reflection.assembly]::LoadFrom($resolvedCompactPath) } else { $asm = [reflection.assembly]::LoadWithPartialName("System.Data.SqlServerCe") } $null = $asm } $fullCreatePath = $ExecutionContext.SessionState.Path.GetUnresolvedProviderPathFromPSPath($DatabasePath) $sqlEngine = New-Object Data.SqlServerCE.SqlCeEngine "Data Source=$fullCreatePath" $sqlEngine.CreateDatabase() #endregion Create SQL Compact DB } elseif ($UseSqlite) { #region Create SQL lite DB if (-not ('Data.Sqlite.SqliteConnection' -as [type])) { if ($sqlitePath) { $resolvedLitePath = $ExecutionContext.SessionState.Path.GetResolvedPSPathFromPSPath($sqlitePath) $asm = [reflection.assembly]::LoadFrom($resolvedLitePath) } else { $asm = [Reflection.Assembly]::LoadFrom("$env:ProgramFiles\System.Data.SQLite\2010\bin\System.Data.SQLite.dll") } $null = $asm } $fullCreatePath = $ExecutionContext.SessionState.Path.GetUnresolvedProviderPathFromPSPath($DatabasePath) [Data.Sqlite.SQliteConnection]::CreateFile($fullCreatePath) #endregion Create SQL lite DB } elseif ($useMySql) { if (-not ('MySql.Data.MySqlClient.MySqlConnection' -as [type])) { if (-not $mySqlPath) { $programDir = if (${env:ProgramFiles(x86)}) { ${env:ProgramFiles(x86)} } else { ${env:ProgramFiles} } $mySqlPath = Get-ChildItem "$programDir\MySQL\Connector NET 6.7.4\Assemblies\"| Where-Object { $_.Name -like "*v*" } | Sort-Object { $_.Name.Replace("v", "") -as [Version] } -Descending | Select-object -First 1 | Get-ChildItem -filter "MySql.Data.dll" | Select-Object -ExpandProperty Fullname } $asm = [Reflection.Assembly]::LoadFrom($MySqlPath) $null = $asm } $sqlConnection = New-Object MySql.Data.MySqlClient.MySqlConnection "$ConnectionString" $sqlConnection.Open() $cmd = $sqlConnection.CreateCommand() $cmd.CommandText = "CREATE Database $DatabaseName" $null = $cmd.ExecuteNonQuery() } else { #region Create SQL server database $sqlConnection = New-Object Data.SqlClient.SqlConnection "$connectionString" $sqlConnection.Open() $cmd = $sqlConnection.CreateCommand() $dbStorage = if ($DatabaseDataFolder) { @" ON PRIMARY ( NAME = N'$($DatabaseName)', FILENAME = N'$($DataBaseDataFolder.TrimEnd("\") + "\"+ "$databaseName.MDF")', SIZE = $($InitialDatabaseSize / 1mb)MB , MAXSIZE = $(if ($maximumDataBaseSize) { $maximumDataBaseSize } else { "UNLIMITED" } ), FILEGROWTH = $($DatabaseGrowth / 1mb)MB) "@ } else { "" } $logstorage = if ($DatabaseLogFolder) { @" LOG ON ( NAME = N'${DataBaseName}_Log', FILENAME = N'$($DataBaseLogFolder.TrimEnd("\") + "\" +"$databaseName.LDF" )', SIZE = $($InitialLogSize / 1mb)MB, MAXSIZE = $(if ($MaximumLogSize) { $MaximumLogSize } else { "UNLIMITED" }), FILEGROWTH = $($LogFileGrowth / 1mb)MB) "@} else { "" } $cmd.CommandText = @" CREATE DATABASE [$DatabaseName] CONTAINMENT = NONE $dbStorage $logstorage "@ if ($OutputSQL) { $cmd.CommandText } else { $null = $cmd.ExecuteNonQuery() } $(if ((-not $keeptransactionLog) -and (-not $OutputSQL)) { $cmd.CommandText = " ALTER DATABASE [$DatabaseName] SET RECOVERY SIMPLE " $null = $cmd.ExecuteNonQuery() }) $(if (-not $NoStat) { $cmd.CommandText = " ALTER DATABASE [$DatabaseName] SET AUTO_CREATE_STATISTICS ON "} $null = $cmd.ExecuteNonQuery() ) $sqlConnection.Close() $sqlConnection.Dispose() #endregion Create SQL server database } } } |