Powershell - run script on all sql servers

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