Execute T-SQL Across Listed Servers/Databases

Here’s a handy trick – if you have a SQL script, stored as a text file, and need to run it against an arbitrary list of different databases on different SQL Server instances, try a little PowerShell. There are a lot of ways to accomplish this task, but personally I like this one for its flexibility.

First, make sure the SQL script does NOT include the common USE <database>. Generally that statement is your friend, but not in cases where a single script should work against multiple databases having different names.

Next, make a “collection of collections” in PowerShell, which will act as a list of instances and databases on those instances. Example:

$instances = @(
    @( 'Server1', 'someDatabase' ),
    @( 'Server2', 'AnotherDatabase' ),
    @( 'Server32', 'FooDB' )
    # Repeat
)

Each item in $instances is a mini-collection, and each of those collections has two elements: an instance name and a database name, which will be available using indexes [0] and [1].

If you prefer, it’s also possible to pull these values from a text file using Get-Content, and split each line on some delimiter character.

Next, load the content of your SQL script file into a variable. There’s an important caveat here: we have to load it as a single string, not an array of strings. The default behavior of Get-Content, though, is to split a file on line terminators and make each line into a separate object in a collection of strings.

There are a few ways to accomplish this, but I learned a simple one in a forum from Alex K. Angelopoulos – we can direct Get-Content to split on a character that doesn’t actually exist in the file. This makes the whole content of the file “look like” one line to Get-Content. He suggests the Null character, which in PowerShell is `0 (back tick zero). Note that the Null character is not the same or related to $null.

Special Characters: http://technet.microsoft.com/en-us/library/dd347558.aspx 

$deployScript = Get-Content 'C:\Path\To\Your\Script.sql' –Delimiter `0

Finally, loop over the list of ( instances + databases ) to run the script everywhere. Be cautious and test!

$instances | ForEach {

    # This should print the instance and database from your list, which is handy for troubleshooting:
    Write-Host $_[0] $_[1] 

    # This sort of thing can be used to validate that you are connecting to the right databases:
    Invoke-SqlCmd –ServerInstance $_[0] –Database $_[1] –Query 'Select @@servername, db_name()'

    # Finally, this would execute the script:
    Invoke-SqlCmd –ServerInstance $_[0] –Database $_[1] –Query $deployScript

}

There is no Undo, so test. Use your powers only for good!

Edit 5/5/2012 – fixed missing commas in code sample.