Installing multiple SQL scripts using a single PowerShell script

• Download InstallSQLScripts_PS.zip - 8.3 KB

Introduction

This PowerShell script demonstrates how to read from a source file which SQL files need to be processed. Each SQL script will be read and executed against the MS SQL server. Each SQL script can contain multiple batched SQL commands. Each batch will be processed separately.

Using the code

The solution consists of at least three files:

1. InstallSQLScript_PS3.ps1

The actual PowerShell script. Note this script runs under PowerShell 3.

2. InstallSQLScripts.txt

This file holds a list of all the SQL files that need to be processed. The processing is done using the top down approach. Comments can be added on a single line by placing # in front of it. Empty lines will be ignored.

Note: This file must be placed in the same folder as where the SQL files are stored.

3. The .SQL files as mentioned in InstallSQLScripts
   The .SQL files should contain drop, create, insert, update or delete instructions. Within the .SQL script it is possible to send messages from the SQL server back to the PowerShell script. This can be done by using the following command within the .SQL file.

PRINT 'TABEL PAYMENT_TYPE DROPPED'

Within the SQL files SQL batches can be defined by separating the batches using the GO command

USE [DB-NAME]
SET ANSI_NULLS ON
GO
SET NOCOUNT ON
GO

Setting up

Step 1: Configuring the PowerShell Script variables

Within the InstallSQLScripts_PS3.ps1 the function Initialize needs to be modified to reflect your environment.

# Initialize the Script variables
function Initialize
{
    $Script:SQl_File_Location = "<SqlFileLocationPath>";
    $Script:SQLInstallList = "InstallSQLScriptsList.txt";
    $Script:ConnectionString ="Server={0};database=<DB_NAME>;user id={1};password={2};Trusted_Connection={3}";
    $Script:LogFile = [string]::Format("<LogFileLocationPath>\InstallLogFile{0:yyyy_MM_dd}.Log", [DateTime]::Now);
    $Script:HtmlLogFile = [string]::Format("<LogFileLocationPath>\\InstallLogFile{0:yyyy_MM_dd}.html", [DateTime]::Now);    
    $script:SqlInstance ="ServerName\InstanceName";
  
    $Script:InstalledListSB = New-Object System.Text.StringBuilder;
 
    Create-HtmlHeader
  
    $Script:SMTPServer = "SMTP-Server";
    $Script:FromSender ="<a href="mailto:InstallSqlScript@SMTP-SERVERDOMAIN">InstallSqlScript@SMTP-SERVERDOMAIN</a>";
    # Use ; to separate multiple email recipients
    $script:ToRecipient ="<a href="mailto:Recipient1@email.addr;Recipient2@email.addr">Recipient1@email.addr;Recipient2@email.addr</a>";
    $script:Subject = "Script Installation";
    #Track whether a script error occurred or not
    $script:ErrorHasOccured = $false;
    $script:ScriptHadError = $false;
}

The following variables need to be set to the appropriate value

• SQl_File_Location

  Replace <SqlFileLocationPath> with the actual (UNC) path to the folder that will hold the SQL Files and the InstallSqlScriptsList.txt file.

• ConnectionString

  Set the DB_NAME to the appropriate database name.

• LogFile

  Replace <LogFileLocationPath> with the actual (UNC) path to where the log file should be stored.

• HtmlLogFile

  Replace <LogFileLocationPath> with the actual (UNC) path to where the HTML formatted log file should be stored. This will be done incase it was not possible to send and email message.

• SqlInstance

  Replace ServerName\InstanceName with the actual ServerName\Instance name of the database server.

• SMTPServer

  The SMTP Server name that accepts relaying.

• FromSender

  The email address that will appear in the FROM section.

• ToRecipent

  List of email addresses of the recipients. Place a semicolon between the different email addresses. Or just provide a single email address.

Step 2: Setting PowerShell ExecutionPolicy

The PowerShell Execution Policy needs to be set on the machine that will execute this PowerShell script. By default the Execution Policy is set to restricted. This inhibits the execution of any PowerShell script.

This limitation can be modified through the PowerShell's Set-ExecutionPolicy command. See also the PowerShell help : Get-Help Set-ExecutionPolicy. Ideally one would use a signed script but if the script will undergo revisions then the unrestricted execution policy could be used. Be aware that this unrestricted policy setting may pose a security risk for the machine that runs the PowerShell script.

Step 3: Testing the setup

Now that everything is set, open the PowerShell Script in PowerShell ISE environment and run the script. If everything is correctly setup the script will execute the SQL scripts and send an email message with the script result log. If the script could not send an email then the there should be a HTML log file in the folder that was assigned to the HtmlLogFile variable.

Step 4: Scheduling the task

In order to run the PowerShell script as a scheduled task the script needs to be called through the following command line command:

powershell.exe -NoProfile -NonInteractive -File "\\path\InstallSQLScripts_PS3.ps1"

Points of interest

The default execution policy for PowerShell is restricted. This means that even the self written PowerShell scripts can only be executed interactively.

Not all .NET Methods are available in PowerShell. For instance under PowerShell 1 and 2 the System.Net.Mail.SmtpClient does not posses the method .Dispose(); This method is available in PowerShell 3.

In PowerShell 3 the following can be done

$SplitChars = @("\r","\n");
$HelpText = $TmpText.Split($SplitChars, [System.StringSplitOptions]::RemoveEmptyEntries);

The above cannot be done under PowerShell 2. There you need to loop through the $TmpText and deal with the empty lines.