PowerShell is an awesome scripting language with many powerful features. This includes integration with SQL Server; a fantastic database engine that is both reliable and battle-tested.
If you need to automate repetitive and/or time-consuming DBA processes, connecting PowerShell to your SQL Server instance can be a massive help.
In this article, we are going to look at how PowerShell can be used to interact with different aspects of SQL Server by employing a variety of techniques.
Let’s dive right in and see what options we have for connecting to SQL Server from PowerShell.
Connection options
There are 3 main ways of connecting to SQL Server from PowerShell which I will cover in this article. These are as follows.
- SQL Server PowerShell Module (SQLPS/SqlServer)
- SQL Server Management Objects (SMO)
- Raw .NET SQL Client (System.Data.SqlClient)
In the following sections, we will explore each of the above options in turn.
SQL Server PowerShell Module
First up, we have the SQL Server PowerShell Module which allows us to interact with SQL Server in a very intuitive way, similar to how we navigate through the Windows filesystem from the command-line.
You should consider using the SQL Server PowerShell Module whenever you are seeking to speed up operations which you would normally carry out manually via SQL Server Management Studio.
The SQL Server PowerShell Module was first made available for SQL Server 2008 and was referred to as ‘SQLPS’. However, the module had a lot of quirks and, as a result, gained a bad reputation. More recently, in 2017, a new ‘SqlServer’ module was released which solves the majority of the annoyances from SQLPS.
In the following sub-sections, we will use the newer SqlServer module to navigate through a SQL Server instance and output information regarding tables in a database. However, note that the instructions should work for SQLPS too.
SqlServer module installation
If you don’t have the SqlServer module available on your machine already, you’ll need to install it by running the following command from an elevated PowerShell prompt.
Install-Module SqlServer
After running the above command you may be prompted that the ‘NuGet provider is required to continue’. If so, enter ‘y’ followed by the Enter/Return key to install the NuGet provider.
If you get an ‘Untrusted repository’ prompt enter ‘y’ followed by the Enter/Return key to trust modules from ‘PSGallery’.
If you get an error message about some commands already being available on your system, you may need to re-run the Install-Module command with the -AllowClobber parameter.
If all goes well the prompt should return with no error messages.
PSDrive support
In my previous PowerShell article, I introduced the Get-PSDrive cmdlet.
If you run this cmdlet from a PowerShell prompt, you’ll see a number of options such as your hard-drive letters (C, D etc.), the Registry and the Windows Certificate store; nothing related to SQL Server though.
Run the following command to import the SqlServer module into your PowerShell session.
Import-Module SqlServer
Note that you’ll need to update your script execution policy if you get an error message which states that the running of scripts is disabled on your system.
Now if you run Get-PSDrive again and check your list of drives you will see a new option called ‘SQLSERVER’.
Run the following command.
SQLSERVER:
SQL Server is now a hard-drive!
Note that you can switch back to your main hard-drive at any time e.g. to switch to the C drive just type C:
Navigating SQL Server
Now that SQL Server has been set as the current PowerShell drive, let’s go ahead and change directory to the SQL Server Database Engine.
cd sql
dir
You should now see your Machine Name displayed within the terminal.
Depending on the results of the dir command you will need to adjust the following command to match your Machine Name.
cd SPECTRE-007
dir
Providing you have SQL Server set up on your machine, you should now see a list of all of your SQL Server instances.
Again, your instance name may vary from the following command e.g. it will most likely be ‘SQLEXPRESS’ if you are running the Express Edition of SQL Server.
cd DEFAULT
dir
Now we can view the available databases contained within the chosen instance.
cd databases
dir
You should see a list of your databases along with some basic details. I have included an example of the output below.
Name Status Containment Type Recovery
Model
---- ------ ---------------- -------------
AdventureWorksDW2017 Normal None Simple
SampleDatabase Normal None Simple
As the next step, we can choose a database from the list.
cd AdventureWorksDW2017
dir
Now we are in the context of the chosen database.
Database operations
There are many different areas within a database that are exposed by the SqlServer module.
Let’s keep things simple and look at the tables.
cd tables
dir
This will display a list of all tables in the current database.
You can customise the output by piping the results of the dir command to PowerShell cmdlets.
For example, you could execute the following command to get a list of the largest tables in the database and output the results to a text file.
dir | Sort-Object rowcount -descending | Format-Table name, rowcount, dataspaceused | Out-File C:\Users\Jonathan\Desktop\database-tables.txt
Note that you must update the Out-File path to a valid path on your machine.
Here’s another useful command which will output a SQL CREATE TABLE script for every table.
dir | %{$_.Script()} | Out-File C:\Users\Jonathan\Desktop\database-tables-scripts.txt
The sample commands shown above have been issued within the context of a single database.
You can imagine how much time you could save by managing multiple databases at the same time using PowerShell, rather than administering each one manually.
SSMS Integration
SQL Server Management Studio provides a convenient integration with PowerShell by allowing a PowerShell prompt which is pre-loaded with the SQL Server PowerShell Module to be launched from within the SSMS user interface.
To access PowerShell from SSMS, after logging into your SQL Server instance, right-click on the top-level instance node and select the ‘Start PowerShell’ option.
Note that you can also right-click on other nodes e.g. on a specific database node to launch PowerShell within the context of a specific database.
If you launch PowerShell from within a newer version of SSMS which doesn’t default to the SQLPS module, you’ll get a ‘SqlServer’ module not found message box if you haven’t already installed the SqlServer module.
You can click on the ‘Open PowerShell Gallery’ button within the aforementioned message box which will take you to the SqlServer PowerShell Gallery webpage. This webpage provides details regarding the SqlServer module and how to install it, as per the instructions from earlier in this article.
Providing that the SqlServer module is installed, after launching PowerShell from SSMS a ‘SQL Server PowerShell’ prompt should appear with a black background as opposed to the usual blue background.
Note that if you get a connection error, make sure that you are logged in using a proper server and instance name e.g. ‘SERVERNAME\SQLEXPRESS’ as opposed to something less common such as ‘.’
Now that your prompt is ready to go, you can go ahead and start issuing commands.
For example, we can list out all of the available databases, much like the example shown previously.
cd databases
dir
This will display details regarding all of the available databases. However, the default information which is returned is slightly different compared to when we load the SqlServer module from a standard PowerShell prompt.
Name Status Size Space Recovery Compat. Owner Collation
Available Model Level
---- ------ ---- ---------- -------- ------- ----- ---------
AdventureWorksDW2017 Normal 272.00 MB 32.12 MB Simple 140 SPECTRE-007\Jonathan SQL_Latin1_General_CP1
_CI_AS
SampleDatabase Normal 426.44 MB 74.80 MB Simple 100 SPECTRE-007\Jonathan Latin1_General_CI_AS
You can now proceed to run any other command you would normally issue when using the module in a standard PowerShell prompt. The experience will be very similar to using the SqlServer module from a standard PowerShell prompt.
SQL Server Management Objects
SQL Server Management Objects, or SMO for short, is a set of .NET libraries that provide the means to manage almost any aspect of SQL Server which you can think of. This covers everything from issuing SQL commands through to backing up and restoring databases.
You should use the SMO libraries whenever you need to automate a server operation such as a backup or replication activity and run it as a script.
In the following sub-sections, we will use SMO to connect to a server and back up all of the user databases to a backup directory.
Loading the libraries
In order to make use of SMO, first of all, we need to load the libraries.
The PowerShell code below will load all of the relevant assemblies needed to work with SMO.
$assemblies =
"Microsoft.SqlServer.ConnectionInfo",
"Microsoft.SqlServer.ConnectionInfoExtended",
"Microsoft.SqlServer.Dmf",
"Microsoft.SqlServer.Management.Collector",
"Microsoft.SqlServer.Management.CollectorEnum",
"Microsoft.SqlServer.Management.RegisteredServers",
"Microsoft.SqlServer.Management.Sdk.Sfc",
"Microsoft.SqlServer.RegSvrEnum",
"Microsoft.SqlServer.ServiceBrokerEnum",
"Microsoft.SqlServer.Smo",
"Microsoft.SqlServer.SmoExtended",
"Microsoft.SqlServer.SqlEnum",
"Microsoft.SqlServer.SqlWmiManagement",
"Microsoft.SqlServer.WmiEnum"
foreach ($assembly in $assemblies)
{
[void][Reflection.Assembly]::LoadWithPartialName($assembly)
}
The SMO libraries will now be available for the remainder of your PowerShell session or script.
Connecting to a server
After loading the SMO libraries you’ll want to connect to a server so that you can start interacting with SQL Server.
To connect to the default server instance on your local machine you can use the following code.
$machine = "$env:COMPUTERNAME"
$server = New-Object Microsoft.Sqlserver.Management.Smo.Server("$machine")
$server.ConnectionContext.LoginSecure=$true
You’ll need to update the connection details if you want to access a database on another machine e.g. use SQL Server Authentication with a username and password.
Getting a list of databases
We can use the Databases property on our Server instance to access a specific database to execute commands against.
$database = $server.Databases["master"]
$command = "SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');"
$dataSet = $database.ExecuteWithResults($command)
$dataTable = $dataSet.Tables[0]
After getting a reference to a specific database, the above code defines a SQL command and executes it. The Data-Set which is returned contains a single Data Table with the results of the SQL query.
Backing up user databases
Now for the backup code, first, let’s create a folder to store our backups.
$datedBackupFolder = "C:\DatabaseBackups\$((Get-Date).ToString('dd-MM-yyyy'))\"
New-Item -ItemType Directory -Path $datedBackupFolder
The above code defines a dated folder name and then uses the New-Item cmdlet to create the backup directory.
Next, we’ll iterate through each database row in the Data Table which was generated earlier. We then carry out the backup operation for each database.
foreach ($row in $dataTable)
{
# Set up backup properties.
$databaseName = $row.name
$backup = New-Object Microsoft.SQLserver.Management.Smo.Backup
$backup.Database = $databaseName
# Configure the backup filename.
$dateAndTime = $((Get-Date).ToString('yyyy-MM-dd_HH-mm-ss'))
$backupFile = $datedBackupFolder + $databaseName + "_" + "$dateAndTime.bak"
# Backup the database.
$backup.Devices.AddDevice($backupFile, [Microsoft.Sqlserver.Management.Smo.DeviceType]::File)
$backup.Action = [Microsoft.Sqlserver.Management.Smo.BackupActionType]::Database
$backup.SqlBackup($server)
}
After running all of the above code, if you check the backup directory you should now find a backup file for each user database.
Raw .NET SQL Client
If you are mainly wanting to execute queries against a SQL Server database and output the results somewhere, using the .NET SQL Client is a good option.
Since the .NET SQL Client is part of the .NET Framework you can execute PowerShell scripts that connect to SQL Server without needing to install any additional modules or libraries.
The SQL Client libraries have been part of the .NET Framework for a long time. They provide a reliable way of connecting to SQL Server and offer great performance.
In the following sub-sections, we will use the .NET SQL Client to fill a Data-Set with the results of a SQL query and then output the results.
Open a connection
First of all, let’s open a connection to the database.
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = “Server=SPECTRE-007;Integrated Security=true;Initial Catalog=master”
$connection.Open()
In the above code, we create a new SqlConnection object, set the Connection String, then we call the Open method on the connection object to establish the connection to SQL Server.
Create a command
Next, we craft the SQL command which we want to execute.
$command = $connection.CreateCommand()
$command.CommandText = "SELECT name, database_id, physical_database_name FROM sys.databases"
In the above code, a SqlCommand object is created from the connection object and the SQL query to execute is configured.
Fill a Data-Set
Now we can fill a Data-Set with the results of our command.
$dataSet = New-Object System.Data.DataSet
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter $command
$adapter.Fill($dataSet) | Out-Null
The code creates a Data-Set to hold the results of the SQL query and a new Data Adapter is created with the SQL command passed into it. We then call the Fill method to populate the Data-Set with the results. The Out-Null cmdlet is used to prevent the number of records from being output to the terminal.
Output results
Lastly, we can output the results of the SQL query to the terminal by accessing the Tables property on the Data-Set.
$dataSet.Tables
If needed, we could also pipe the Tables property to the Out-File cmdlet in order to write the results to a file for future reference.
Further reading
If you are seeking to further your understanding of PowerShell and are looking for a practical guide on how to accomplish a wide range of real-world tasks, I highly recommend the PowerShell Cookbook by Lee Holmes.
The PowerShell Cookbook offers a solid introduction to PowerShell fundamentals and covers a large number of problems and solutions along with the full source code for each ‘recipe’ that is covered.
The practical examples covered in the book include details on how to use the Invoke-SqlCommand cmdlet as another way of connecting to SQL Server from PowerShell.
Summary
In this article, we have explored the 3 main ways of connecting to SQL Server from PowerShell.
We’ve seen how the SQL Server PowerShell Module can be used to conveniently access SQL Server as a drive via either a standard PowerShell prompt or a SQL Server PowerShell prompt launched from SQL Server Management Studio.
SQL Server Management Objects are another powerful way of administering a SQL Server database. We’ve looked at how to use the SMO libraries to back up all user databases within a SQL Server instance.
Lastly, we’ve seen how the .NET SQL Client can be used to connect directly to SQL Server, allowing us to execute queries with fine-grained control.
Comments