Search code examples
sql-serverpowershellsql-server-2016dbatools

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

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.

Solution

  • 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"