Batch Script to Deploy Multiple SQL files (Version 2)

I took very long break to keep the site up to date with my learning. Few years back I wrote an article to deploy multiple scripts using batch files. Refer http://sql-articles.com/scripts/batch-script-to-deploy-multiple-sql-files/ for the older version in command prompt. Thanks for all the users and lot of comments for improvements.

I wrote a new script using powershell to perform the same action as the batch script with few improvements and few add-ons based on the comments. This script picks the files based on the file name sort order. I have created sample folder structure to pick the files in certain order, users can create similar folder structure or file names in the order you want get this executed. Listed some improvements

  • Improved logging
  • Parameter details to log file
  • Error information to a separate file
  • Uses .Net driver, will be expanded to Azure AD authentication
  • Accepts file name and folder name with space in it

Folder Structure

PowerShell Script

$error.clear()
$Server = Read-Host -Prompt 'Enter Server Name'
$DatabaseName = Read-Host -Prompt 'Enter Database Name'
$Authenticationmode = Read-Host -Prompt 'Windows or SQL Authentication. Type "W" for Windows "S" for SQL'

if ($Authenticationmode.ToUpper() -eq "S")
{
    $Username = Read-Host -Prompt 'Enter User name'
    $Password = Read-Host -AsSecureString -Prompt 'Enter Password'
}

$ScriptPath = Read-Host -Prompt 'Enter relative path'
$windowsusername = [System.Security.Principal.WindowsIdentity]::GetCurrent().Name.ToString()
$Datetime = Get-Date
$processstarttime = $Datetime.ToString("yyyyMMddHHmmssfff")
$logfile = "$ScriptPath\executionlog_$processstarttime.log"
$errorlogfile = "$ScriptPath\errorlog_$processstarttime.log"

Write-Host  "Batch Execution Date   : $Datetime"
Write-Host  "Server Name            : $server" | Out-File -FilePath $logfile -Append
Write-Host  "Database Name          : $DatabaseName"
if ($Authenticationmode.ToUpper() -eq "S")
{
Write-Host  "Authentication Mode    : SQL Authenticated User"
} else {Write-Host  "Authentication Mode    : Windows Authentication"}
if ($Authenticationmode.ToUpper() -eq "S")
{
Write-Host  "User Name              : $UserName"
} else {Write-Host  "User Name              : $windowsusername" }
Write-Host  "Script Path            : $ScriptPath"
Write-Host  "Log File               : $logfile"
Write-Host  "Error Log File         : $errorlogfile"
Write-Host  $("*" * 100)


##Write to Log file
"Batch Execution Date   : $Datetime" | Out-File -FilePath $logfile -Append
"Server Name            : $server" | Out-File -FilePath $logfile -Append
"Database Name          : $DatabaseName" | Out-File -FilePath $logfile -Append
if ($Authenticationmode.ToUpper() -eq "S")
{
"Authentication Mode    : SQL Authenticated User" | Out-File -FilePath $logfile -Append
} else {"Authentication Mode    : Windows Authentication" | Out-File -FilePath $logfile -Append}
if ($Authenticationmode.ToUpper() -eq "S")
{
"User Name              : $UserName" | Out-File -FilePath $logfile -Append}
 else {"User Name              : $windowsusername" | Out-File -FilePath $logfile -Append}
"Script Path            : $ScriptPath" | Out-File -FilePath $logfile -Append
"Log File               : $logfile" | Out-File -FilePath $logfile -Append
"Error Log File         : $errorlogfile" | Out-File -FilePath $logfile -Append
$("*" * 100) | Out-File -FilePath $logfile -Append

$Password = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto([System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($Password))

## Using Invoke-Sqlcmd2 instead of native Invoke-Sqlcmd to catch granular SQL server errors
## Below function copied from https://gallery.technet.microsoft.com/scriptcenter/7985b7ef-ed89-4dfd-b02a-433cc4e30894
function Invoke-Sqlcmd2 
{ 
    [CmdletBinding()] 
    param( 
    [Parameter(Position=0, Mandatory=$true)] [string]$ServerInstance, 
    [Parameter(Position=1, Mandatory=$false)] [string]$Database, 
    [Parameter(Position=2, Mandatory=$false)] [string]$Query, 
    [Parameter(Position=3, Mandatory=$false)] [string]$Username, 
    [Parameter(Position=4, Mandatory=$false)] [string]$Password, 
    [Parameter(Position=5, Mandatory=$false)] [Int32]$QueryTimeout=600, 
    [Parameter(Position=6, Mandatory=$false)] [Int32]$ConnectionTimeout=15, 
    [Parameter(Position=7, Mandatory=$false)] [ValidateScript({test-path $_})] [string]$InputFile, 
    [Parameter(Position=8, Mandatory=$false)] [ValidateSet("DataSet", "DataTable", "DataRow")] [string]$As="DataRow" 
    ) 
 
    if ($InputFile) 
    { 
        $filePath = $(resolve-path $InputFile).path 
        $Query =  [System.IO.File]::ReadAllText("$filePath") 
    } 
 
    $conn=new-object System.Data.SqlClient.SQLConnection 
      
    if ($Username) 
    { $ConnectionString = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;Connect Timeout={4}" -f $ServerInstance,$Database,$Username,$Password,$ConnectionTimeout } 
    else 
    { $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout } 
 
    $conn.ConnectionString=$ConnectionString 
     
    #Following EventHandler is used for PRINT and RAISERROR T-SQL statements. Executed when -Verbose parameter specified by caller 
    if ($PSBoundParameters.Verbose) 
    { 
        $conn.FireInfoMessageEventOnUserErrors=$true 
        $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {Write-Verbose "$($_)"} 
        $conn.add_InfoMessage($handler) 
    } 
     
    $conn.Open() 
    $cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn) 
    $cmd.CommandTimeout=$QueryTimeout 
    $ds=New-Object system.Data.DataSet 
    $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd) 
    [void]$da.fill($ds) 
    $conn.Close() 
    switch ($As) 
    { 
        'DataSet'   { Write-Output ($ds) } 
        'DataTable' { Write-Output ($ds.Tables) } 
        'DataRow'   { Write-Output ($ds.Tables[0]) } 
    } 
 
} 

foreach ($file in Get-ChildItem $ScriptPath -Filter "*.sql" -Recurse | Sort-Object -Property FullName)
{
    Write-Host "****** PROCESSING $file FILE ******" 
    "****** PROCESSING $file FILE ******" | Out-File -FilePath $logfile -Append
    if ($Authenticationmode -eq "S" -or $Authenticationmode -eq "s")
    {
        Invoke-Sqlcmd2 -ServerInstance $Server -Database $DatabaseName -Username $Username -Password $Password -InputFile $file.FullName  | Out-File -FilePath $logfile -Append
     }
    else {
       
        Invoke-Sqlcmd2 -ServerInstance $Server -Database $DatabaseName -Username $Username -Password $Password -InputFile $file.FullName | Out-File -FilePath $logfile -Append
    }
 
    if ($error[0].Exception.Message -eq $null -and $? -eq $true)
            {
                "******SUCCESSFULLY PROCESSED $file FILE ******" | Out-File -FilePath $logfile -Append
                $("*" * 100) | Out-File -FilePath $logfile -Append
            } 
            else 
            {
                "******FAILED PROCESSING $file FILE ******" | Out-File -FilePath $logfile -Append
                Write-Host "ERROR ERROR ERROR"
                Write-Host "One\more script(s) failed to execute, terminating batch. Check error log file $errorlogfile file more details"
                "******FAILED PROCESSING $file FILE ******" | Out-File -FilePath $errorlogfile -Append
                "******Error Message $error[0].Exception.Message ******" | Out-File -FilePath $errorlogfile -Append
                $("*" * 100) | Out-File -FilePath $errorlogfile -Append
                Return
            }
   
}

 

Sample Execution

Powershell accepts parameter values like server name, database name, script path and sql login details (if applicable). In the example above you can see it provides extensive details to the execution window as well as the same data is written to success log file. For any execution two log files might be created one for successful logging and another one for error logging as shown above.

Once again thanks to all the users who tried and passed valued feedback for improvements. Please try the powershell script and let me know if any changes required. I’ll modify the script to include Azure Active directory authentication in few weeks time.

 

Leave a Reply

3 thoughts on “Batch Script to Deploy Multiple SQL files (Version 2)”

  1. Everything seems fine, but it wont execute any of my scripts? They process fine in the .bat version.

    ****** PROCESSING TestScript1.sql FILE ******
    Exception calling “Fill” with “1” argument(s): “Incorrect syntax near ‘go’.
    Incorrect syntax near ‘GO’.”
    At C:\Test\SQLPowershell\SQLPSDeployerTest.ps1:100 char:5
    + [void]$da.fill($ds)
    + ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SqlException

    ERROR ERROR ERROR

    this is the content of the test.sql i’m trying to execute

    print ‘Test1’
    go
    print ‘Executed at ‘ + @@SERVERNAME + ‘::’ + db_name()
    GO
    print ‘Executed by ‘ + suser_name()

    select * from Releases where ProjectID = 335

    help appreciated.

  2. It was because I had print statements in my test.sql files. Is there a way to allow these print statements to remain without throwing an error? It’s how the SQL queries I run have always been created.

    thanks

  3. Hi,
    It’s really good script and Thank you for sharing the details.

    I see two issues with string
    1) Following if condition missing in the script. If i select windows authentication , it ‘s throwing an error

    if ($Authenticationmode.ToUpper() -eq “S”)
    {
    $Password = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto([System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($Password))
    }

    2) Script throwing an error if script file contains GO statements between two statements.
    Example:
    IF OBJECT_ID(‘dbo.Test1’, ‘U’) IS NOT NULL
    DROP TABLE dbo.Test1;
    GO;
    CREATE TABLE [dbo].[Test1](
    [Col1] [int] NOT NULL,
    [Col2] [varchar](50) NULL
    ) ON [PRIMARY]

    1 st part i have taken care. Second issue i couldn’t able to resolve it. Looking for your quick help on resolving second issue

Leave a Reply

Your email address will not be published. Required fields are marked *

*