Easy method for deploying .sql script to multiple databases on a single instance

Question

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.

Check out Invoke-SqlCmd 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
– AdminOfThings, Commented Mar 30, 2020 at 20:25
You could do that pretty easily in SSIS. Use a Foreach Loop Container and put an expression on the initial catalog or connection string. — Chris Albert
– Chris Albert, Commented Mar 30, 2020 at 21:08

Martin Cairney · Accepted Answer · 2020-03-30 23:24:33Z

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"

answered Mar 30, 2020 at 23:24

Martin Cairney

1,7771 gold badge9 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Easy method for deploying .sql script to multiple databases on a single instance

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related