Recently I started digging up Powershell. I am amazed at how elegant and powerful it is, not to mention it’s speed !
To start, I browsed to Start > All Programs > Accessories > Windows Powershell > Windows Powershell where we get the Powershell (PS) command prompt.
You can navigate to the folder of your choice using the common “cd ” format of DOS. For example, to get to G:\Powershell_test_folder, just type : “cd G:\Powershell_test_folder”. I have saved some files in this location, hence the reason to navigate here.
Now I have saved the names of all my target SQL Servers in a text file at that location called AllServers.txt. You can simply add the names one by one. I used the “IP,port_number” format like this :
Now we can add the query that we want to run against these n servers in a Query.sql file at that location. Lets say we want the database names in that server. So save this query in the Query.sql file :
select name from sys.databases
Now we will run a Powershell script to run this query against all the servers.
But before we do that, we will need to add these two lines in the Powershell command prompt :
PS > Add-PSSnapin SqlServerCmdletSnapin100
PS > Add-PSSnapin SqlServerProviderSnapin100
Here is the PS script (lets call it PScript1.ps1):
$ExecuteQuery= Get-Content -path $QueryPath | out-string
“Results — > `r`n`r`n” > $OutputFile
FOREACH($server in GC “G:\Powershell_test_folder\AllServers.txt”)
“——————————-” >> $OutputFile
$server >> $OutputFile
“——————————-” >> $OutputFile
invoke-sqlcmd -ServerInstance $server -query $ExecuteQuery -querytimeout 60000 | ft -autosize | out-string -width 4096 >> $OutputFile
Ok, so how does the script work ? We define three variables using the $ symbol :
- $QueryPath : This shows the location of the query file.
- $OutputFile : This is where the results of your query appear.
- $ExecuteQuery : This will read (get-content or gc) your input query and write the “Results” string to your output file.
Now the FOREACH command will iterate through your servers in the AllServers.txt file. It does this by reading (GC or Get-Content) the AllServers.txt file. It then outputs the name of your server first, followed by the results of your query (in this case the list of databases on your server).
The query is executed using the Invoke-Sqlcmd command let. This is just like running the usual sqlcmd command. The ” | ft” means it will output the result in a formatted table output.
In my case, I used a more complex query to collect the details of the errorlogs. The following query used in the Query.sql file will run the xp_readerrorlog command to find wherever the ‘ERROR’ or ‘FAIL’ statements were recorded in the SQL Server Errorlogs for the past two days :
declare @Start varchar(30);
declare @End varchar(30);
exec master..xp_readerrorlog 0, 1, N’error’, NULL, @Start, @End
exec master..xp_readerrorlog 0, 1, N’fail’, NULL, @Start, @End
To execute the PS script, simply type this at your PS command prompt :
PS > .\PScript1.ps1
Powershell is fast and awesome! There is still a lot to learn and study about this. Here are some resources that helped me (thx to Pinal Dave, Raushan, Sandip Shinde, and others) :