How to Execute .SQL Files from Directory by using PowerShell

Powershell scripts can be used to execute SQL Scripts. The below scripts can be used to run all the .sql scripts from a directory. I did not use sort-object , that means the files will run in ascending order.


#Provide SQLServerName
$SQLServer ="MySQLSErver\InstanceName"
#Provide Database Name 
$DatabaseName ="Test"
#Scripts Folder Path
$FolderPath ="C:\MyScripts\Test\"

#Loop through the .sql files and run them
foreach ($filename in get-childitem -path $FolderPath -filter "*.sql")
{
invoke-sqlcmd –ServerInstance $SQLServer -Database $DatabaseName -InputFile $filename.fullname
#Print file name which is executed
$filename 
} 



If you would like to run the script in descending order you can use below script

#Provide SQLServerName
$SQLServer ="MySQLSErver\InstanceName"
#Provide Database Name 
$DatabaseName ="Test"
#Scripts Folder Path
$FolderPath ="C:\MyScripts\Test\"

#Loop through the .sql files and run them
foreach ($filename in get-childitem -path $FolderPath -filter "*.sql" |sort-object 
-descending)
{
invoke-sqlcmd –ServerInstance $SQLServer -Database $DatabaseName -InputFile $filename.fullname
#Print file name which is executed
$filename 
} 

3 comments:

  1. Hi I am unable to sort the sql files in a descending order as explained above. It is showing the latest commits first . Can you please let me know the solution to arrange all files in descending order

    ReplyDelete
  2. Its amazing. I want to know the logs which file is getting executed and what it is executing in the sql server.

    ReplyDelete