# This function determines whether a database exists in the system. Function IsDBInstalled([string]$sqlServer, [string]$DBName) { $exists = $FALSE try { # we set this to null so that nothing is displayed $null = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") # Get reference to database instance $server = new-object ("Microsoft.SqlServer.Management.Smo.Server") $sqlServer foreach($db in $server.databases) { if ($db.name -eq $DBName) { $exists = $TRUE } } } catch { Write-Error "Failed to connect to $sqlServer" } # Return Write-Output $exists }
This function will work pretty much everywhere. It's not the most elegant way of doing it but it does the job. You can choose simply to catch the exception and not do anything with it and the results would be neater but I like catching exceptions.
# This function determines whether a database exists in the system. Function IsDBInstalled([string]$sqlServer, [string]$DBName) { $exists = $FALSE try { $conn = New-Object system.Data.SqlClient.SqlConnection $conn.connectionstring = [string]::format("Server={0};Database={1};Integrated Security=SSPI;",$sqlServer,$DBName) $conn.open() $exists = $true } catch { Write-Error "Failed to connect to DB $DBNAME on $sqlServer" } Write-Output $exists }Note that we use integrated windows authentication so that there is no need to bother with credentials.
Just what I was looking for. Thanks !!
ReplyDeletea little bit simpler approach instead of foreach loop:
ReplyDeleteif ( $null -ne $server.Databases[$DBName] ) { $exists = $true } else { $exists = $false }
Is this working ?
DeleteI did something similar:
Delete$exists = @($sqlServer.Databases | % { $_.Name }) -contains $db
The @() will make sure we get an array back, and then we can use -contains.
This also works:
ReplyDeleteif($s.Databases[$DBName])
{
...
}