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
}
It's amazing
ReplyDeleteHi 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
ReplyDeleteIts amazing. I want to know the logs which file is getting executed and what it is executing in the sql server.
ReplyDeleteGreat info, congratulations!
ReplyDeleteCould you add an option so sqlcmd would generate an errorlog file for each runned script?
Example:
script01.sql --> generate script01.err log file
Thanks