Querying SQL Server through Powershell

I got Up North this last weekend.  It was the first weekend in a couple months, and I definitely haven’t had the time for fishing like I have in years past.  This last weekend was no exception, as we spent most of the time re-sealing the logs in Grandpa’s cabin.

log-sealing-cabin

I did get out for an hour or two of row-trolling, but didn’t manage any bites.  It’s a bit sad because usually this is my favorite time of year to fish, but there have just been too many other things going on this year.  I did get the top picture above of the lake in the early morning behind the rowboat.  I love the look of a still lake in the morning.

I have found a great use for Powershell in my daily DBA tasks.  I use it to create an Excel workbook each day with various health checks on my SQL Servers.  It is great to look in a single workbook and see all the potential issues across all the servers highlighted.  I have also begun to add my Oracle servers to the same process.  In this post I’m going to share the basics for connecting to SQL Server.  The next post will do the same for Oracle, and the final post in this series will show my full script for creating the workbook.

#Load the dll that contains the SQL Server objects. 
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null 
#Create the SQL Server instance.  This will be the domain name of the server 
$instance = mySQLServer 
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance

It’s that easy.  Now within the SQL Server, I can reference different areas I may want to monitor:

#SQL Agent jobs 
$jobs=$srv.JobServer.Jobs 

#Databases 
$dbs = $srv.Databases 
#Loop through each database 
ForEach ($db in $dbs) 
{ 
#Database Name 
$db.Name 
#Last backup date 
$db.LastBackupDate 
}

Stay tuned for my next post which will show how to connect to and query Oracle database.  The final post in this series will show how to use the above script to create an Excel workbook that gives an awesome summary of the health of my SQL Server databases and Oracle databases.

Advertisements

2 thoughts on “Querying SQL Server through Powershell

  1. Pingback: Querying Oracle through Powershell | The Fisherman DBA

  2. Pingback: Using Powershell to create a nightly SQL health check | The Fisherman DBA

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s