Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions Links


SQL Server DBA Jobs

Powershell - run script on all sql servers

16 July,2011 by Tom Collins

Run a script on all SQL Servers.This powershell script assists in applying a similar change on every SQL Server Instance . Or a subset.

For example , adding a new generic logon account , or collecting regular information .

I’ve written plenty of powershell scripts – that are customised for a particular dataset – such as a morning healthcheck, which outputs different base information I’m interested on reporting every morning.  This is outputted into a formatted Excel spreadsheet – logged for auditing.

Sometimes , I just need an adhoc script run once on a list of SQL servers. The following is that script. It accepts 1 input parameter – which is the input file.

 #script accepts 1 input paramters i.e a generic sql file  

#iterates through the list of instances 

#this is a potentially dangerous script be careful!!! 

#Assumptions – SQLCMD available , security on SQL Server

 #input parameters

param ([string] $inputfile =$(throw "input file name is required"))

 #global variables

#to add to the outputfilename

$isodate=Get-Date -format s 

$isodate=$isodate -replace(":","")

#variable for outputfile name , based on inputfile and isodate

$outputfile=$inputfile + $isodate + ".out"


#list of sql server instances 1 on each line 

#iterate through the list and invoke a SQLCMD , with some format information

foreach ($svr in get-content "instances.txt"){

     $rep = $svr -replace("\\","_")

     invoke-expression "SQLCMD -E -S $svr -i $inputfile  -s '*' -W -h-1 >> $outputfile"


 To execute the script ,from a Powershell command line  run:

 PS C:\powershell\scripts> ./general.ps1 -inputfile "create_generic_logon.sql"

 Note the use of “-inputfile” , and the variable name in the Powershell script as “$inputfile” .

Related Posts

Powershell Scripts for DBA

SQL Server - Powershell for SQL SMO

Author: Tom Collins (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on Powershell - run script on all sql servers | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer