Saturday 20 October 2012

Check whether database exists using PowerShell

I've using this function to check for the existence of databases for a while, there is one problem with it though and that is it will only run if Microsoft.SqlServer.Smo is in the server, or put another way, SQL server is installed. Thus far this has not been a problem, but yesterday after spending a good hour [during a meeting, I can multitask despite being a bloke] and failing to get this running remotely, I thought that I would try a simpler way.
# 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.

5 comments:

  1. Just what I was looking for. Thanks !!

    ReplyDelete
  2. a little bit simpler approach instead of foreach loop:

    if ( $null -ne $server.Databases[$DBName] ) { $exists = $true } else { $exists = $false }

    ReplyDelete
    Replies
    1. I did something similar:
      $exists = @($sqlServer.Databases | % { $_.Name }) -contains $db

      The @() will make sure we get an array back, and then we can use -contains.

      Delete
  3. This also works:
    if($s.Databases[$DBName])
    {
    ...
    }

    ReplyDelete