Installing multiple SQL scripts using a single PowerShell script
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:
- InstallSQLScript_PS3.ps1
The actual PowerShell script. Note this script runs under PowerShell 3.
- 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.
- .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 ="InstallSqlScript@SMTP-SERVERDOMAIN";
# Use ; to separate multiple email recipients
$script:ToRecipient ="Recipient1@email.addr;Recipient2@email.addr";
$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 possess 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 was first released on codeproject and is licensed under Code Project Open License (CPOL)