Working with SQL Server and PowerShell

This tutorial chapter is going to tackle the nitty-gritty of working with SQL Server and PowerShell. SQL Server is a proficient and secure data platform that provides you with optimum performance and data protection tools. On the other hand, PowerShell, which is Microsoft's shell scripting language, gives you the ability to automate and execute administrative tasks.

One may wonder, why would anyone need to connect SQL Server with PowerShell? The answer is simple. SQL Server offers incredible capabilities for data storage, retrieval, and manipulation. PowerShell, with its scripting capabilities, can be utilized effectively to automate routine tasks, such as backups and report generation on SQL Server. Now, let's dive in to understand how to get SQL Server data using PowerShell.

Getting SQL Server Data Using PowerShell

PowerShell connects to SQL Server by establishing a connection to the SQL Server instance. The instance can be the name of your local or remote SQL Server.

Here is a simple script that connects to a local SQL Server instance:

$ServerName = "localhost"
$DatabaseName = "MovieMasterDB"
$Connection = New-Object System.Data.SqlClient.SqlConnection
$Connection.ConnectionString = "Server=$ServerName;Database=$DatabaseName;Integrated Security=True;"
$Connection.Open()
$Connection.Close()

In the script above, we first define the server name and the database name. "localhost" is used when SQL Server is installed on the same machine where the script is being run. The "MovieMasterDB" is the name of our sample database which contains information about movies and their characters. Once the server and the database are set up, we create an object for SqlConnection which is a .NET class used to establish connections to SQL Server. The connection string gets formatted with the server and database names along with the authentication details. Lastly, we open and then close the connection to the database.

You may wonder why we took the time to open the connection if we are just going to close it again. The truth is, we need to confirm that a connection to the database is possible before we execute further operations.

Running SQL Server Queries using PowerShell

We are now able to connect to the SQL Server database from PowerShell. The next step is to run SQL queries to fetch data. Assume, for example, we are working with a movie database, and we want to fetch information about the movie characters.

$ServerName = "localhost"
$DatabaseName = "MovieMasterDB"
$query = "SELECT MovieName, CharacterName, ReleaseYear FROM moviecharacters"
 
$Connection = New-Object System.Data.SqlClient.SqlConnection
$Connection.ConnectionString = "Server=$ServerName;Database=$DatabaseName;Integrated Security=True;"
$Command = New-Object System.Data.SqlClient.SqlCommand
$Command.Connection = $Connection
 
$Command.CommandText = $query
$Connection.Open()
$Reader = $Command.ExecuteReader()

while ($Reader.Read()) {
   Write-Host "`nMovie Name: $($Reader["MovieName"])"
   Write-Host "Character: $($Reader["CharacterName"])"
   Write-Host "Released Year: $($Reader["ReleaseYear"])"
} 
$Connection.Close()

In this script, we create a $query variable to store our SQL query. We then initiate the connection as previously explained, and attach the SQL command object to the connection before assigning the SQL query to the command text. Once the connection is open, use the ExecuteReader method to return data. The while loop reads and prints out the data row by row.

Automation with PowerShell and SQL Server

Task automation is one of the significant advantages of combining SQL Server and PowerShell. Let's assume we wish to automate a nightly backup process of our "MovieMasterDB" database.

$ServerName = 'localhost'
$DatabaseName = 'MovieMasterDB'
$BackupFile = 'C:\SQLBackups\MovieMasterDB.bak'
 
$Connection = New-Object -TypeName System.Data.SqlClient.SqlConnection
$Connection.ConnectionString = "Server=$ServerName;Database=master;Integrated Security=True;"
$Connection.Open()
 
$Command = New-Object -TypeName System.Data.SqlClient.SqlCommand
$Command.Connection = $Connection
$Command.CommandText = "BACKUP DATABASE [$DatabaseName] TO DISK='$BackupFile'"
 
$Command.ExecuteNonQuery()
 
$Connection.Close()

The script above creates a connection to our SQL Server instance and opens a connection. It then defines a SqlCommand and executes it through the ExecuteNonQuery method which performs database operations that do not return rows - particularly suitable for backup operations.

By applying techniques outlined above, you can perform database operations, automate tasks and generate reports using the combined power of SQL Server and PowerShell. You can go further to explore and take advantage of the diverse .NET library that PowerShell provides, there are numerous classes and methods that can be used to interact and control SQL Servers more efficiently.