One part of our SQL Server builds is to run a collection of .sql scripts that configure the server install stored procs and a bunch of other tasks. Having to run 10-20 scripts manually is no fun and prone to error, so obviously we look to PowerShell to resolve the problem. I used to use the Invoke-SQLCmd to do this but have had issues with it in the passed. As i was updating the script last week i decided to try Invoke-SQLCMD2 from DBATools but noticed i got errors with all the GO batch separators. That left with me a problem! I knew i could do it with SMO but I have no interest in writing a SMO connections, passing in credentials, loading in the assembles and all that nonsense!! The solution is what i posted before! my beloved Connect-DbaSQLInstance.. Instant SMO connection. Then its just down to how to call a script and run it! Well that again isn’t to hard, I loop over all the files that are .sql files and sort them (Some of my scripts need to be run in order which is design with appending a number to the scripts.)

Link to GitHub:  https://github.com/Staggerlee011/PowerShell/blob/master/SQLServer/Invoke-SQLScripts.ps1

Advertisements