Run multiple .SQL files

[Noson5434]

Hi I was wondering if it is possible to use this tool to run a folder of . SQL files and rename the file to . Success if it executed successfully, otherwise rename to . Failed and stop running on fail...

[potatoqualitee]

Yes indeed, you can use PowerShell for this, here's some pseudocode

$files = Get-ChildItem C:\scripts

foreach ($file in $files) {
  try {
    Invoke-DbaQuery -SqlInstance sql01 -File $file.FullName -EnableException
    $file | Rename-Item -NewName { $PSItem.Name -Replace '.sql','.Success' }
  } catch {
    $file | Rename-Item -NewName { $PSItem.Name -Replace '.sql','.Failed' }
  }
}

[Noson5434]

Thank you.

[Noson5434]

How can I get a number from a file name?
Users have a folder called SQL Update Scripts.
And each file we add has a number and description. Example File Name (688. VW tblUsers)
Now, not every user is up to the same numbered file so I want to copy from one folder containing all files to their folder only from the number they are up to and further on and only execute those.

So for example Development is up to file number 688 and the user's file number is up to 650 so I want to copy from development 651-688 and only execute all files within the folder starting at 651-688.

Thank you.