Click here to Skip to main content
15,867,141 members
Articles / Database Development / SQL Server

Installing multiple SQL scripts using a single PowerShell script

Rate me:
Please Sign up or sign in to vote.
4.00/5 (1 vote)
18 Sep 2013CPOL3 min read 43.8K   658   14   8
Installing multiple SQL scripts using a single PowerShell script.

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
  2. The actual PowerShell script. Note this script runs under PowerShell 3.

  3. InstallSQLScripts.txt
  4. 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.

  5. 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.
SQL
PRINT 'TABEL PAYMENT_TYPE DROPPED'

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

SQL
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Netherlands Netherlands
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionIssue in Batch statement -GO Pin
sudheerYelleti26-Jun-18 23:41
sudheerYelleti26-Jun-18 23:41 
QuestionBreak on any error Pin
Member 127355933-Nov-16 9:43
Member 127355933-Nov-16 9:43 
QuestionUnable to download file Pin
The Data Man1-Apr-14 6:31
The Data Man1-Apr-14 6:31 
QuestionPlease attach downloads, sourcecode is missing for this article. Pin
radiall18-Sep-13 4:35
radiall18-Sep-13 4:35 
AnswerRe: Please attach downloads, sourcecode is missing for this article. Pin
Arjan Douwes18-Sep-13 7:30
Arjan Douwes18-Sep-13 7:30 
GeneralRe: Please attach downloads, sourcecode is missing for this article. Pin
radiall26-Sep-13 1:49
radiall26-Sep-13 1:49 
QuestionInstallSQLScript_PS3.ps1 Pin
Joel Beebe18-Jun-13 6:04
Joel Beebe18-Jun-13 6:04 
AnswerRe: InstallSQLScript_PS3.ps1 Pin
Arjan Douwes4-Aug-14 9:01
Arjan Douwes4-Aug-14 9:01 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.