How to connect and query SQL Server using PowerShell

Tagged: PowerShell

Introduction#

In this article we will query Microsoft SQL Server using PowerShell.

This can be a really powerful tool for sys admins and database administrators. You can query multiple servers at once and then use PowerShell to do work with the results.

There are multiple ways you could do this but one of the easiest is to use Invoke-Sqlcmd, so that’s what we will use for this article.

Problem#

Sometimes you need to query multiple servers across multiple domains to gather information about sql server instances, backups and other sql server information. This can be challenging within SSMS and your options with the data are limited, but with PowerShell we can query multiple servers and pipe that data to other commands to create powerful snippets of information.

Solution#

Using the Invoke-SqlCmd function, we can use the sqlcmd command line tool that your probably familiar with, but is much simpler than working directly with sqlcmd. We can achieve a connection and a query with 1 line of code, or we can use a foreach loop in PowerShell to work with many servers.

Example 1: Running PowerShell to Retrieve the Last Backup#

This example shows how we could very quickly retrieve the last backup time of each database across multiple servers.

$databaseName="master"
$instanceNames=("servera.domain1.com\MSSQLSERVER01","serverb.domain2.com\MSSQLSERVER01","serverc.domain3.com\MSSQLSERVER01"))

$query = "SELECT  
@@SERVERNAME AS 'Server', 
msdb.dbo.backupset.database_name AS 'DB Name',  
MAX(msdb.dbo.backupset.backup_finish_date) AS 'Last Backup'
FROM   msdb.dbo.backupmediafamily  
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
WHERE  msdb..backupset.type = 'D' 
GROUP BY 
msdb.dbo.backupset.database_name"


foreach($instance in $instanceNames)
{ 
    Invoke-SqlCmd –ServerInstance $instance -Database $databaseName –Query $query
}

Obviously, this example could easily be changed to perform any task you like, all you need to do is change the $query variable to your own t-sql query and then change the $instanceNames to your own array of server names.

Example 2: Running PowerShell to Retrieve Sql Server Version#

This example shows how we could check the sql versions of multiple instances, this would be useful for making sure all the servers in your sql estate have the latest patches.

$instanceNames=("servera.domain1.com\MSSQLSERVER01","servera.domain2.com\MSSQLSERVER01")
$query = "select @@SERVERNAME, @@VERSION"

foreach($instance in $instanceNames)
{ 
    Invoke-SqlCmd –ServerInstance $instance -Query $query
}

Conclusion#

The purpose of this article was to show how to connect to sql server using PowerShell and as you can see its very simple using Invoke-SqlCmd PowerShell function. Working with PowerShell and Microsoft SQL Server together you can do some really awesome things and automate a lot of the day to day sql tasks.