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

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.

Leave a Reply

Join the Conversation

13 Comments

  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

  4. 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. 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!

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

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

Leave a Reply to VidhyaSagar Cancel reply

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

*


x

Related Posts

Provisioning Azure Cosmos DB using Powershell
Azure Cosmos database is one of the NoSQL database that is available in Microsoft Azure cloud platform. Azure Cosmos db is getting more popular c...
SSIS 2014 Execution Error while validating Script Component
It's been so long after my last article. Looks like I been lazy for few years now :-) I assume lot of people using  SSIS package deployment util...
Batch script to deploy multiple SQL files
I was working on a project where I’m supposed to deploy lots of SQL Server script files (.sql). It’s very tedious job either to manually execute ...
powered by RelatedPosts