Sqlserver-dba.com

Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
Powershell - run script on all sql servers

16 July,2011 by Jack Vamvas

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: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

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

Working...
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.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer