Connecting to SQL Server from PowerShell

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.

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.