This function may be invoked remotely from a machine that has SQL-PS module installed. The default MS-SQL port (1433) is assumed to be used for the connection, although that value can be set to a different number. Moreover, database usename and password are non-mandatory. When those variables are specified, they would be used. Otherwise, Windows integrated authentication shall be the default.

Function databaseExists{
    param(
        [Parameter(Mandatory=$true)][string]$sqlServer,
        [Parameter(Mandatory=$true)][string]$dbName,
        [Parameter(Mandatory=$false)][string]$port=1433,
        [Parameter(Mandatory=$false)][string]$dbUser,
        [Parameter(Mandatory=$false)][string]$dbPassword
        )
    $ErrorActionPreference='stop'
    $dbExists = $false
    try{
        $moduleName='sqlps'    
        if(!(Get-Module -ListAvailable -Name $moduleName -ea SilentlyContinue)){
            if(!('NuGet' -in (get-packageprovider).Name)){    
                try{
                    Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force -ErrorAction SilentlyContinue;
                    }
                catch{
                    [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
                    Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force -ErrorAction SilentlyContinue;
                    }            
                }
            $null=Install-Module -Name $moduleName -Force -Confirm:$false
            Update-SessionEnvironment
            }
        $null=import-module $moduleName  
        $conn = New-Object system.Data.SqlClient.SqlConnection
        if($dbUser -and $dbPassword){
            $conn.ConnectionString=[string]::format("Server={0};Database={1};User ID={2};Password={3};","$sqlServer,$port",$dbName,$dbUser,$dbPassword)
        }else{
            $conn.connectionstring=[string]::format("Server={0};Database={1};Integrated Security=SSPI;","$sqlServer,$port",$dbName)
            #$conn.ConnectionString=”Server={0};Database={1};Integrated Security=True” -f $ServerInstance,$Database
            }
        $conn.open()
        $conn.close()
        return $true
    }catch{
        Write-Error $error[0].Exception.Message
        return $false
        }
}
Categories: