0

I have a dbscript.sql file that I would like to have run against roughly 30 databases sitting on a named SQL Server instance. The script is fairly lengthy (1000+ lines) and contains numerous quotes to change to accomodate using MS_ForEachDB. I had tried a mass ctrl-h replace-all apostrophe's but that just ended up producing other erroneous errors.

I have started down the road of using dbatools and powershell to accomplish this task but I'm wondering if there is a simpler trick to get this script applied to multiple databases at once.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Geoff Dawdy
  • 886
  • 6
  • 15
  • 42
  • 1
    Check out [Invoke-SqlCmd](https://learn.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps) and note the `-InputFile`, `-ServerInstance`, and `-Database` parameters. It would be best if you posted your code attempts so we can work through them. – AdminOfThings Mar 30 '20 at 20:25
  • You could do that pretty easily in SSIS. Use a [Foreach Loop Container](https://learn.microsoft.com/en-us/sql/integration-services/control-flow/foreach-loop-container?view=sql-server-ver15) and put an expression on the initial catalog or connection string. – Chris Albert Mar 30 '20 at 21:08

1 Answers1

3

Dbatools is a good option as you could put use the pipeline to pass it into a the Invoke-DbaQuery command. An example would be:

Get-DbaDatabase -SqlInstance "server1", "server1\nordwind", "server2" | Invoke-DbaQuery -File "C:\scripts\sql\rebuild.sql"
Martin Cairney
  • 1,714
  • 1
  • 6
  • 18