Recently I went through requirement of running SQL scripts on multiple databases on different servers. As the list of databases was different from script to script I decided to create universal solution, which will allow to easily prepare for deployment and can be simply reused at any time.

As a platform to execute scripts I went with PowerShell as that provides flexibility in case additional functionality will be required.

Whole solution contains 3 files:

• SqlExecutionInflow.csv – file contains list of databases and servers where particular database is located. It is simple CSV file with 2 columns
• SqlExecQuery.sql – contains SQL script which will be executed against all databases listed in SqlExecutionInflow.csv
• SqlExec.ps1 – main script which load SqlExecInflow.csv and executes query from SqlExecQuery.sql

All files have to be placed in same folder. As a result script will create transcript file with output from all executed commands.

And here are example files and script itself…

(more…)

If there is a need to pass specific credentials to Get-WmiObject in order to gather information from remote machine, here is how you can do it:

$LAdmin = "DOMAIN\Administrator"
$LPassword = ConvertTo-SecureString "Password!" -AsPlainText -Force
$Credentials = New-Object -Typename System.Management.Automation.PSCredential -ArgumentList $LAdmin, $LPassword

Once $Credentials object is created you can easily use it to gather information from remote PC:

Get-WmiObject -ComputerName $computername -Class Win32_VideoController -Namespace "root\cimv2" -Credential $Credentials

Some handy wmic commands which might be helpful when automating OS deployment:

wmic bios get serialnumber
wmic computersystem get manufacturer
wmic computersystem get model

Handful list of parameters possible to use in CMD scripts:

%~I         - expands %I removing any surrounding quotes ("")
%~fI        - expands %I to a fully qualified path name
%~dI        - expands %I to a drive letter only
%~pI        - expands %I to a path only
%~nI        - expands %I to a file name only
%~xI        - expands %I to a file extension only
%~sI        - expanded path contains short names only
%~aI        - expands %I to file attributes of file
%~tI        - expands %I to date/time of file
%~zI        - expands %I to size of file
%~$PATH:I   - searches the directories listed in the PATH
environment variable and expands %I to the
fully qualified name of the first one found.
If the environment variable name is not
defined or the file is not found by the
search, then this modifier expands to the
empty string

%~1         - expands %1 removing any surrounding quotes (")
%~f1        - expands %1 to a fully qualified path name
%~d1        - expands %1 to a drive letter only
%~p1        - expands %1 to a path only
%~n1        - expands %1 to a file name only
%~x1        - expands %1 to a file extension only
%~s1        - expanded path contains short names only
%~a1        - expands %1 to file attributes
%~t1        - expands %1 to date/time of file
%~z1        - expands %1 to size of file
%~$PATH:1   - searches the directories listed in the PATH
environment variable and expands %1 to the fully
qualified name of the first one found.  If the
environment variable name is not defined or the
file is not found by the search, then this
modifier expands to the empty string

%~dp1       - expands %1 to a drive letter and path only
%~nx1       - expands %1 to a file name and extension only
%~dp$PATH:1 - searches the directories listed in the PATH
environment variable for %1 and expands to the
drive letter and path of the first one found.
%~ftza1     - expands %1 to a DIR like output line

Very useful for automated software installations for example.

From script repository I did use in the past, VBscript which allows to clear EvenLog on Windows machine. I did use that on computers with Windows XP and Windows Server 2003. however, this script will work also on Windows 7 and Windows server 2008.

By default script clears EventLog on computer where script is executed. To clear logs on remote machine just modify variable strComputer and replace dot with name of the target machine.

Script will read all EvenLog files from machine and will go through them removing all events.

And here is script itself :

strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate, (Backup, Security)}!\\" _
& strComputer & "\root\cimv2")
Set colLogFiles = objWMIService.ExecQuery _
("Select * from Win32_NTEventLogFile")
For each objLogfile in colLogFiles
objLogFile.ClearEventLog()
Next