Problem Statement:-
At times, we may need to run SQL files present in a folder against SQL Server Database. Since there are no built in feature available as of now, here is our try with a powershell script.
Pre-requisites:-
- Save the (.bat) Batch script & (.ps1) PS Script in same folder in which all the SQL Files are stored.
- The Machine should have the SQL Server instance and Power shell tool.
- Ensure the parameters values are verified in (.bat) Batch file
Scenarios Covered:-
i) All key values are handled with parameters
ii) If database not exist it will throw error and comes out of PS
iii) If any script has issues/failed, it will throw error and comes out of PS
iv) If we want to run the sql scripts in subfolder as well, then “Includesubfolders” parameter set to 1
v) Batch file automatically retrieve the root path
PS Script:-
$Scriptpath = $args[0]
$Server = $args[1]
$database = $args[2]
$user= $args[3]
$pwd= $args[4]
$Includesubfolders=$args[5]
Function IsDBInstalled([string]$Server, [string]$database)
{
$t=Invoke-Sqlcmd -ServerInstance $Server -Username $user -Password $pwd -Database "master" -Query "select 1 from sys.databases where name='$database'" -OutputSqlErrors $true
if (!$t) {
Write-Host "Failed to connect to [$database] database on [$Server]" -BackgroundColor darkred
Write-Error "Failed to connect to [$database] database on [$Server]" -ErrorAction Stop
} else {
write-host "[$database] Database exists in SQL Server [$Server]" -BackgroundColor blue -ForegroundColor black
}
}
IsDBInstalled $Server $database
if($Includesubfolders -eq 1) {
$subscripts = Get-ChildItem $Scriptpath -recurse | Where-Object {$_.Extension -eq ".sql"}
foreach ($s in $subscripts)
{ Write-Host "Running Script : " $s.Name -BackgroundColor green -ForegroundColor darkRed
$tables=Invoke-Sqlcmd -ServerInstance $Server -Username $user -Password $pwd -Database $database -InputFile $s.FullName -ErrorAction 'Stop' -querytimeout ([int]::MaxValue)
write-host ($tables | Format-List | Out-String)
}
} else {
$scripts = Get-ChildItem $Scriptpath | Where-Object {$_.Extension -eq ".sql"}
foreach ($s in $scripts)
{ Write-Host "Running Script : " $s.Name -BackgroundColor green -ForegroundColor darkRed
$tables=Invoke-Sqlcmd -ServerInstance $Server -Username $user -Password $pwd -Database $database -InputFile $s.FullName -ErrorAction 'Stop' -querytimeout ([int]::MaxValue)
write-host ($tables | Format-List | Out-String)
}
}
Batch Script:-
@ECHO ON
SET root=%cd%
SET PSScript=%root%\RunSQLFiles.ps1
SET PowerShellDir=C:\Windows\System32\WindowsPowerShell\v1.0
CD /D "%PowerShellDir%"
SET path=%root%
SET "machine=sqlserversample45"
SET "db=sample"
SET "user=username"
SET "pwd=password"
SET "Includesubfolders=0"
Powershell -ExecutionPolicy Bypass -Command "& '%PSScript%' '%path%' '%machine%' '%db%' '%user%' '%pwd%' '%Includesubfolders%'"
Pause
EXIT /B
Output:-
If Database Not Exists:
If Database Exists:
If Any Script has Error:
If “Includesubfolders” parameters set to “1”
I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!
SQLZealots 
Its amazing with the powershell. I need to get the logs of SQL Server as well. Can you please let me know what needs to be done further
LikeLike