79 lines
2.3 KiB
PowerShell
79 lines
2.3 KiB
PowerShell
|
function Test-DatabaseExists {
|
|||
|
|
<#
|
|||
|
|
|
|||
|
|
.SYNOPSIS
|
|||
|
|
This function confirms that the user running this script has access to the requested database at the provided connection string.
|
|||
|
|
|
|||
|
|
.DESCRIPTION
|
|||
|
|
This function confirms that the user running this script has access to the requested database at the provided connection string.
|
|||
|
|
It uses the credentials of the connection string to connect.
|
|||
|
|
|
|||
|
|
In order to test successfully you must have access to the master database to run queries for database existence
|
|||
|
|
|
|||
|
|
.PARAMETER connectionString
|
|||
|
|
[string] Used to test connection to the server. Will always set the initial catalog to master.
|
|||
|
|
|
|||
|
|
.PARAMETER dbName
|
|||
|
|
[string] Database name to check exists. If this parameter is not passed, will take the value of the database in the connection string.
|
|||
|
|
|
|||
|
|
.INPUTS
|
|||
|
|
Connection string to connect to the server, Database name to confirm exists
|
|||
|
|
|
|||
|
|
.OUTPUTS
|
|||
|
|
true or false according to existence of the database
|
|||
|
|
|
|||
|
|
.EXAMPLE
|
|||
|
|
Test-DatabaseExists
|
|||
|
|
|
|||
|
|
Test-DatabaseExists
|
|||
|
|
#>
|
|||
|
|
[CmdletBinding()]
|
|||
|
|
param(
|
|||
|
|
[string]$connectionString,
|
|||
|
|
|
|||
|
|
[string]$dbName
|
|||
|
|
)
|
|||
|
|
process {
|
|||
|
|
$logLead = (Get-LogLeadName)
|
|||
|
|
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
Write-Verbose "$logLead : Attempting to verify the connection to $connectionString"
|
|||
|
|
|
|||
|
|
$sqlConnectionBuilder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder "$connectionString"
|
|||
|
|
|
|||
|
|
if ([string]::IsNullOrWhiteSpace($dbName)) {
|
|||
|
|
$dbName = $sqlConnectionBuilder.InitialCatalog
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
$sqlConnectionBuilder["Database"] = "master"
|
|||
|
|
|
|||
|
|
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionBuilder.ConnectionString
|
|||
|
|
|
|||
|
|
[System.Data.SqlClient.SqlCommand]$command = $sqlConnection.CreateCommand()
|
|||
|
|
$command.CommandText = @"
|
|||
|
|
select [Name] from sys.databases where [name]=@name;
|
|||
|
|
"@
|
|||
|
|
$command.Parameters.AddWithValue("@Name",$dbName) | Out-Null
|
|||
|
|
|
|||
|
|
$sqlConnection.Open()
|
|||
|
|
[System.Data.SqlClient.SqlDataReader]$reader = $command.ExecuteReader()
|
|||
|
|
|
|||
|
|
$returnedName = ""
|
|||
|
|
if ($reader.Read()) {
|
|||
|
|
$returnedName = $reader[0]
|
|||
|
|
}
|
|||
|
|
$reader.Dispose()
|
|||
|
|
|
|||
|
|
$sqlConnection.Close()
|
|||
|
|
|
|||
|
|
return ($returnedName -eq $dbName)
|
|||
|
|
}
|
|||
|
|
catch
|
|||
|
|
{
|
|||
|
|
Write-Error "$logLead : Can not connect to the specified database. Do you have approved access to the server?"
|
|||
|
|
Write-Host $_.Exception.Message
|
|||
|
|
throw "could not connect to the database!"
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|