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 https://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

Update: 05-Apr-2019

• Moved the script to GitHub and open sourced.
• Leveraging Invoke-DbaQuery from dbatools
• Returns output from Print statement and executes script with GO statement in it.
• Added additional parameter to continue even on error
• Using transcript to log everything, no more separate log file

Folder Structure

PowerShell Script

<#
 .SYNOPSIS Execute SQL Script in order
 .Parameter Server Provide SQL Server Name
 .Parameter DatabaseName Provide database name
 .Parameter UserName Provide a windows user name or SQL Server user name
 .Parameter Password Provide password as a secure string
 .Parameter ScriptPath Provide the path where all the SQL scripts are located
 .Parameter ContineOnException Enable this switch to continue even if one of the script failed to execute
 .EXAMPLE 
 $SecurePass = ConvertTo-SecureString "MyPassword"
 Invoke-BatchSqlExecution -Server "MyServer" -DatabaseName "MyDatabasae" -UserName "MyUserId" -Password $SecurePass -ScriptPath "C:\Temp\SQL" -ContinueOnException
#>
function Invoke-BatchSqlExecution { 
    param(
        [parameter(Mandatory = $True, Position = 0)]
        [string]$Server,
        [parameter(Mandatory = $True, Position = 1)]
        [string]$DatabaseName,
        [parameter(Mandatory = $True, Position = 2)]
        [string]$UserName,
        [parameter(Mandatory = $True, Position = 3)]
        [SecureString]$Password,
        [parameter(Mandatory = $True, Position = 3)]
        [string]$ScriptPath,
        [parameter(Mandatory = $False, Position = 3)]
        [switch]$ContinueOnException
    )

    $error.clear()

    $Credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $Username, $Password 

    $windowsusername = [System.Security.Principal.WindowsIdentity]::GetCurrent().Name.ToString()
    $Datetime = Get-Date
    $processstarttime = $Datetime.ToString("yyyyMMddHHmmssfff")
    $logfile = "$ScriptPath\executionlog_$processstarttime.log"

    Start-Transcript -Path $logfile
    Write-Host  "Batch Execution Date   : $Datetime"
    Write-Host  "Server Name            : $server" 
    Write-Host  "Database Name          : $DatabaseName"
    Write-Host  "User Name              : $UserName"
    Write-Host  "Executed By            : $windowsusername" 
    Write-Host  "Script Path            : $ScriptPath"
    Write-Host  "Log File               : $logfile"
    Write-Host  $("*" * 100) -ForegroundColor Cyan

    $checkmodule = Get-Module -ListAvailable | Where-Object { $_.Name -eq "dbatools" }
    if (!$checkmodule) {    
        Write-Host "Installing dbatools module" -ForegroundColor Cyan
        Start-Process powershell.exe -ArgumentList "-Command Install-Module dbatools" -Verb RunAs -Wait
    }
    elseif ($checkmodule) {
        Import-Module dbatools -Force
        Write-Host "dbatools module already exists" -ForegroundColor Green
    }

    if (!(Test-Path $ScriptPath)) {
        Write-Host "$ScriptPath does not exists or the user does not have access" -ForegroundColor Red
        Break
    }
               
    foreach ($file in Get-ChildItem $ScriptPath -Filter "*.sql" -Recurse | Sort-Object -Property FullName) {
        try {
            Write-Host "****** PROCESSING $file FILE ******" -ForegroundColor Yellow
            Invoke-DbaQuery -SqlInstance $Server `
                -SqlCredential $Credential `
                -Database $DatabaseName `
                -File $file.FullName `
                -EnableException `
                -MessagesToOutput
            Write-Host "******SUCCESSFULLY PROCESSED $file FILE ******" -ForegroundColor Green  
            Write-Host $("*" * 100) 
                
        }
        catch {
            Write-Host "******FAILED PROCESSING $file FILE ******" -ForegroundColor Red
            Write-Host $($Error[0].Exception.Message) -ForegroundColor Red
            Write-Host $("*" * 100)
            IF ($ContinueonException.IsPresent) {
                continue
            }
            else { throw }
        }
    }
    Write-Host "****** All Files Processed Successfully ******" -ForegroundColor Green
    Stop-Transcript
}

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.