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

$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
function Invoke-Sqlcmd2 
    [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 } 
    { $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout } 
    #Following EventHandler is used for PRINT and RAISERROR T-SQL statements. Executed when -Verbose parameter specified by caller 
    if ($PSBoundParameters.Verbose) 
        $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {Write-Verbose "$($_)"} 
    $cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn) 
    $ds=New-Object system.Data.DataSet 
    $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd) 
    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
                "******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


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


  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


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

    print ‘Test1’
    print ‘Executed at ‘ + @@SERVERNAME + ‘::’ + db_name()
    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.


  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.
    IF OBJECT_ID(‘dbo.Test1’, ‘U’) IS NOT NULL
    DROP TABLE dbo.Test1;
    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!

Leave a comment

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



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