For an installation I needed to execute a sql-script to deploy a database schema. Testing the process was fine on my development machine, because I have a local SQL Server and all the client tools installed. But for the deployment in the target farm (it is a database schema for a SharePoint timer job) I do not have any SQL Server client tools available on the machines. So, what to do?

Why not using the classes from the .NET Framework to connect to the database and run the scripts. OK, I prepared a small PowerShell-script that reads the sql-script into memory and executes the script via SqlCommand.ExecuteNonQuery(). It worked for some simple scripts, but not for the large one with my database schema. The method in the .NET Framework has problems with the GO-statement, the SQL Server tools use as batch delimiter. So, I modified my script to detect the batches within the sql-script and execute batch by batch. Here is the result:

<#
.SYNOPSIS
Executes the batches of a sql script against a SQL Server

.DESCRIPTION
Normally a script for the SQL Server is separated into several batches
separated by a "GO".

.EXAMPLE
.\ExecuteSqlScript -DBServerName MyServer -DBName MyDatabase -sqlScriptFile MyScript.sql

.PARAMETER $DBServerName
The name of the database server to connect to. The current user must be able
to connect to this instance and the database.

.PARAMETER $DBName
The name of the database, where the sql file should be executed

.PARAMETER $sqlScriptFile
The name of the sql file to execute

.PARAMETER $batchEnd
The end-of-batch signal. Preset with "GO"

.PARAMETER $silent
When set to $true, all executed batches are shown in the console.
#>

param (
  [parameter(Mandatory = $true)]
  [ValidateNotNullOrEmpty()]
  [string] $DBServerName,
  [parameter(Mandatory = $true)]
  [ValidateNotNullOrEmpty()]
  [string] $DBName,
  [parameter(Mandatory = $true)]
  [ValidateNotNullOrEmpty()]
  [string] $sqlScriptFile,
  [string] $batchEnd = "GO",
  [bool] $silent = $true
)

####################
# main section
####################

if ((Test-Path $sqlScriptFile) -eq $true)
{
  # we have a full qualified filename
  $sqlFile = $sqlScriptFile
}
else
{
  # construct a full qualified filename
  $scriptPath = Split-Path -Parent $MyInvocation.MyCommand.Path

  $sqlFile = $scriptPath + "\" + $sqlScriptFile
}

if ((Test-Path $sqlFile) -eq $false)
{
  Write-Host -ForegroundColor Red "The file $sqlFile does not exist."

  Exit
}

$sqlText = Get-Content $sqlFile

$conn = New-Object System.Data.SqlClient.SqlConnection

$conn.ConnectionString = "Server=$DBServerName;Database=$DBName;Integrated Security=True"

$conn.Open()

$sqlBatch = ""
$j = 0
$hasLines = $false

for($i = 0; $i -lt $sqlText.Length; $i++)
{
  if ($sqlText[$i].ToUpper() -eq $batchEnd.ToUpper())
  {
    if ($silent -eq $false)
    {
      Write-Host "Now executing:"
      Write-Host $sqlBatch
    }
    else
    {
      Write-Host -NoNewline "."
    }

    $cmd = New-Object System.Data.SqlClient.SqlCommand ($sqlBatch, $conn)
    $cmd.ExecuteNonQuery() | Out-Null
    $cmd.Dispose()

    $sqlBatch = ""
    $j = 0

    $hasLines = $false
  }
  else
  {
    $sqlBatch =
@"
$sqlBatch
$($sqlText[$i])
"@

    $j++

    if ($sqlText[$i].Trim() -ne "")
    {
      $hasLines = $true
    }
  }
}

if ($hasLines -eq $true)
{
  if ($silent -eq $false)
  {
    Write-Host "Now executing:"
    Write-Host $sqlBatch
  }
  else
  {
    Write-Host -NoNewline "."
  }

  $cmd = New-Object System.Data.SqlClient.SqlCommand ($sqlBatch, $conn)
  $cmd.ExecuteNonQuery() | Out-Null
  $cmd.Dispose()
}

$conn.Close()

$conn.Dispose()

Write-Host

Write-Host -ForegroundColor Green "Done."

This PowerShell-script should work in most cases.

Advertisements