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))
{
$line
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"

SET NOCOUNT ON

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′

INSERT INTO @DBName

EXEC (@sql)

SET @End=@@Rowcount

SET @Start=1

WHILE @Start <=@End

BEGIN

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

SET @sql=”

BEGIN

SET @sql=’Write SQL Command Sscript’

END

–Print @sql

exec (@sql)

SET @Start=@Start + 1

END

  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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: