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.


Posted

in

by

Comments

13 responses to “Batch Script to Deploy Multiple SQL files (Version 2)”

  1. InternetGuy avatar
    InternetGuy

    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. InternetGuy avatar
    InternetGuy

    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. Ravi Raj Chelluri avatar
    Ravi Raj Chelluri

    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

  4. Ravi Raj Chelluri avatar
    Ravi Raj Chelluri

    i can see file processing message in log file. However, script not printing how many rows are impacted. Can you please kindly advice how can I print this message in out file?

  5. Curious DBA avatar
    Curious DBA

    I like the concept but I trust you programmatically placed your .sql files categorically into each of the folder structure.
    I have > 500 scripts and dont plan on moving them one-at-a-time.

    Now THAT would be a useful script!

    1. VidhyaSagar avatar

      One of the user already requested a script to move the files to respective directories, let me see if I can do this

  6. fhussain@cognisco.com avatar

    Do you have a script to programmatically categorise each script to be placed in the folder structure?

    1. VidhyaSagar avatar

      Unfortunately No, I dont have script. Let me see if I can do this

  7. sai avatar
    sai

    This article is very userful. Once error comes in any of file then it should not go to next script. Could you help with this requirement.

    1. VidhyaSagar avatar

      @Sai – You can use the latest version which has a switch to continue even on failure. I have changed it as a function so you can reuse it

  8. Sai avatar
    Sai

    You want me use powershell ? . Actually I don’t want to continue after the error.

    1. VidhyaSagar avatar

      Don’t add the parameter ContinueOnException when you call the function, that should stop when the first error occurred

  9. Shailendra Sahu avatar
    Shailendra Sahu

    Whenever I am trying to execute the ps1 file it is throwing me an error.
    Cannot index into a null array.
    At C:\Users\shaisahu\Desktop\PS\New folder\run-sql.ps1:158 char:44
    + Write-Verbose “an error occurred:$($err[0]).”
    + ~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : NullArray

Leave a Reply

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