Home > SQL Server PowerShell Scripts > Executing SQL Script on multiple Servers/Databases

Executing SQL Script on multiple Servers/Databases

On a daily basis DBA’s has to monitor on multiple database instances and what if you have to run a script on multiple SQL Server Instances and Multiple databases

While working on a project where we have more than 15 SQL Instances and on a daily basis we have been asked by dev team to run sql script on each of the server on specific databases. so I developed a small PowerShell script which calls the sql server script and applies to all the databases which are online or to the databases which are not mentioned in the exclude database parameter

param ([string]$DatabaseToBeExcluded)
if (-not (Get-Module SQLPS)) {Import-Module SQLPS

$ExcludeDatabases=$DatabaseToBeExcluded #‘1,2,3,4’

$localpath=split-path $SCRIPT:MyInvocation.MyCommand.Path -parent

foreach($line in (Get-Content $localpath\ServerList.txt))
Invoke-Sqlcmd -InputFile $localpath\PermissionScript.sql-Variable ExDb=$ExcludeDatabases -ServerInstance $line -ErrorAction SilentlyContinue -Verbose -QueryTimeout 0 -Database master

SQL Script –:setvar ExDb "1,2,3,4"


DECLARE @ExDb varchar(100)

DECLARE @Start int

DECLARE @End int

DECLARE @Databasename varchar(100)

DECLARE @sql nvarchar(500)

SET @ExDb= ‘$(ExDb)’

–print @ExDb

DECLARE @DBName TABLE (ID INT IDENTITY(1,1),DBName varchar(100))

SET @sql=’SELECT name from sys.databases where convert(varchar(10),database_id) not in (‘+@ExDb+’) and state=0′


EXEC (@sql)

SET @End=@@Rowcount

SET @Start=1

WHILE @Start <=@End


SELECT @Databasename=DBName FROM @DBName WHERE ID=@Start

SET @sql=”


SET @sql=’Write SQL Command Sscript’


–Print @sql

exec (@sql)

SET @Start=@Start + 1


  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: