SQL Server – Running A Script Or SQL On Multiple Instances/Databases With PowerShell – Level 100

Today, I received a really simple request. There was an UPDATE statement that had to be run on quite a few databases in multiple instances.  This was for a vendor software we use and the statement was sent to me by a Helpdesk professional who manages the product.

You need to have the SQLPS (older) or the SQLSERVER (newer) PowerShell module installed.

As this is the simplest of use-cases for PowerShell instead of the other methods, I am including the code that I used to quickly run that on a set of instances/databases.

$VerbosePreference = 'Continue'

[string] $query = "-- v10 ETL Licence 23Jun18 (1000)
                    UPDATE [VENDOR_SYS].[VENDOR_LIC_TABLE]
                    SET [VALUE] = '		<license>SomeReallyLongEncryptedString</license>'
                    WHERE [NAME] = 'License'
                    "

# -----------------------
# Format: InstanceName=DB
# -----------------------
[string[]] $servers = @('HOSTDEVVDRDB\SDETL01=VendorDB',
                'HOSTDEVVDRDB\ETL01=VendorDBMaster',
                'HOSTDEVVDRDB\ETL02=VRD_DWH',
                'HOSTDEVVDRDB\ETL03=VDR_DM',
                'HOSTUATVDRDB\ETL01=VendorDB',
                'HOSTUATVDRDB\ETL01=VendorDBMaster',
                'HOSTUATVDRDB\ETL02=VRD_DWH',
                'HOSTUATVDRDB\ETL03=VDR_DM',
                'HOSTMAIVDRDB\ETL01=VendorDB',
                'HOSTMAIVDRDB\ETL01=VendorDBMaster',
                'HOSTMAIVDRDB\ETL02=VRD_DWH',
                'HOSTMAIVDRDB\ETL03=VDR_DM',
                'HOSTPPDVDRDB\ETL01=VendorDB',
                'HOSTPPDVDRDB\ETL01=VendorDBMaster',
                'HOSTPPDVDRDB\ETL02=VRD_DWH',
                'HOSTPPDVDRDB\ETL03=VDR_DM',
                'HOSTPRDVDRDB\ETL01=VendorDB',
                'HOSTPRDVDRDB\ETL02=VRD_DWH')

foreach($server in $servers)
{
    $instance = ($server -split '=')[0]
    $db = ($server -split '=')[1]

    Write-Verbose ("Running against instance [{0}] & DB [{1}]" -f $instance, $db)

    Invoke-Sqlcmd `
            -ServerInstance $instance `
            -Database $db `
            -Query $query  #'SELECT @@SERVERNAME'

}

This is the output:

Output:
VERBOSE: Running against instance [HOSTDEVVDRDB\ETL01] & DB [VendorDB]
VERBOSE: Running against instance [HOSTDEVVDRDB\ETL01] & DB [VendorDBMaster]
VERBOSE: Running against instance [HOSTDEVVDRDB\ETL02] & DB [VRD_DWH]
VERBOSE: Running against instance [HOSTDEVVDRDB\ETL03] & DB [VDR_DM]
VERBOSE: Running against instance [HOSTUATVDRDB\ETL01] & DB [VendorDB]
VERBOSE: Running against instance [HOSTUATVDRDB\ETL01] & DB [VendorDBMaster]
VERBOSE: Running against instance [HOSTUATVDRDB\ETL02] & DB [VRD_DWH]
VERBOSE: Running against instance [HOSTUATVDRDB\ETL03] & DB [VDR_DM]
VERBOSE: Running against instance [HOSTMAIVDRDB\ETL01] & DB [VendorDB]
VERBOSE: Running against instance [HOSTMAIVDRDB\ETL01] & DB [VendorDBMaster]
VERBOSE: Running against instance [HOSTMAIVDRDB\ETL02] & DB [VRD_DWH]
VERBOSE: Running against instance [HOSTMAIVDRDB\ETL03] & DB [VDR_DM]
VERBOSE: Running against instance [HOSTPPDVDRDB\ETL01] & DB [VendorDB]
VERBOSE: Running against instance [HOSTPPDVDRDB\ETL01] & DB [VendorDBMaster]
VERBOSE: Running against instance [HOSTPPDVDRDB\ETL02] & DB [VRD_DWH]
VERBOSE: Running against instance [HOSTPPDVDRDB\ETL03] & DB [VDR_DM]
VERBOSE: Running against instance [HOSTPRDVDRDB\ETL01] & DB [VendorDB]
VERBOSE: Running against instance [HOSTPRDVDRDB\ETL02] & DB [VRD_DWH]

That took only a few minutes to code but the next time around, you and me can use this template and replace the SQL/instance list to run something else and get it done in seconds!

Advertisement