Managing SQL Server with PowerShell: Part 1 – The Tools
Articles,  Blog

Managing SQL Server with PowerShell: Part 1 – The Tools


Hi there. I am Michael Otey. I am a SolarWinds contributor
and President of TECA Inc. And in this presentation,
we’re going to talk about managing SQL Server with PowerShell. This is part one of a
two part presentation. What we are going to cover in part 1; First we are going to look at importing the SQL Server PowerShell Module. This module is called sqlps. We will kind of see what it does. It allows you to navigate
through a SQL server using power shell commands. Next, we’ll look at
Running PowerShell from SQL Server Management Studio. It has options where you
can launch a powershell directly from management studio. Then we’ll look at using
some navigation using the SQL Server PowerShell Provider. You can basically use this
provider to navigate through SQL Server exactly like
you might a file system. So, it’s quite interesting
and it even allows you to to drill down into the
file and table level, and you’ll see that. Next, we’ll see some examples of using the SQL Server PowerShell cmdlets. There’s a number of
different cmdlets available to help you manage SQL Server. So first, before you begin
managing SQL Server with the PowerShell console, you
need to import the sqlps module. This command shows you how to do this. You basically use the
import module command. This is a standard PowerShell command. Give it the name of the
module and that sqlps and then you use the disabled
name checking parameter to go along with that. That will prevent any
errors from being displayed as you use this module. These errors basically come about because of minor naming in compatibilities between the SQL Server and PowerShell. So, using that will just
prevent those errors from being displayed. So, it’s basically a nicety. There is no real incompatibility there. If you try to use the
different SQL Server commands, like here we’re using
the directory command to navigate into SQL
Server with PowerShell, and we haven’t loaded
that PowerShell module, you’ll get an error a
lot like this that says, “object not found.” But after you go ahead and
import the module using the import module
command, then you execute that same command. In this case, doing a
directory or navigating to the default SQL Server instance. You’ll see that everything works fine. You can also launch this
PowerShell Command Shell from SQL Server Management
System [SSMS] itself. To do that, just open up object explore, right click your SQL Server instance, and from the context
menu, go down and select Start PowerShell command. This will launch a PowerShell window. If you launch this
window from a SQL Server, you do not have to run
the import module command, because it’s done implicitly for you. Once you’ve done this, you can go ahead and navigate that SQL server instant. For example, in this screen ,
we’ve launched the SQL Server PowerShell module then we can
instantly run the DIR command, which is a PowerShell command. And we can see the different
directories that are out there. We can use the CD command,
like change directory to the databases. You can see here, we are using PowerShell and that PowerShell provider
for SQL Server to navigate through the different
directories that are out there for SQL Server. When you go to navigate using
the PowerShell Provider, there are several things you can navigate. The one that you are going
to work with most would be your database object which you can find in the SQLSERVER:/SQL directory. But, there are a lot of other objects out there that you can manage as well. There’s Policy based management objects. There’s Registered server
objects you can navigate through. Utility objects. You can navigate through
Data-tier application objects, Data collector objects, and
even integration services and Analysis Services. So the PowerShell lets you go through and navigate through a lot of
different objects out there in SQL Server, and I’ll
show you an example of using that in just a second. PowerShell also provides
a number of cmdlets that you can use to help manage them. You have cmdlets that can
create Availability Groups, as well as add notes to them. You have cmdlets that can
preform backup and restores. You can also execute commands and queries using these cmdlets. In this example, you can see I am using the Invoke-Sql command
cmdlet with a query parameter passing a simple query to a SQL Server. We will give you a
demonstration of all that in just a second. So let’s have a quick
look at getting started using SQL Server and PowerShell. First, let’s open up a PowerShell prompt. Now, we haven’t loaded the
sqlps module in here yet, so let’s try a simple PowerShell command. This is one of the navigation commands, and we are going to do a
directory of SQL Server and we will look in the default instance. So, as we hit that command,
since the module isn’t loaded, we’ve got an error. So, in order to load that sqlps module, we are going to use the
import module command, like you see here. Give it the module name, which is sqlps and disable name checking. And at this point, it’s going to go ahead and load in that sqlps module for us. This takes just a second. And there you have it. The module is loaded in. Now, let’s rerun that
command that we ran earlier. And at this point, you can
see we’re now able to navigate the SQL server instance using PowerShell. You can also start PowerShell from the SQL Server Management Studio as well. So, let’s pop over here and
get a look at doing that. In this case, we can go in, we
can say, “Start PowerShell.” And, when we do that,
you’ll notice the prompt is just a little bit different. It’s black instead of the
normal default PowerShell blue. But, once we do this, this
automatically loads in the sqlps module for
us as you can see here. And, we can preform our
navigation commands. And, you can see we can navigate much like going into the file system. So, if we wanted to go into the databases of this SQL Server
instance, we could type in CD Databases dir and we
can see the databases that are there. CD, let’s go into adventureworks 2012. And, there we’ve navigated to there. Let’s take a look in there as we continue to dig in a little deeper. And, let’s go into the tables. And, do a directory of those. And, you can see that we
can basically drill down into whatever we want to look at here. Now, let’s have a quick
look at running a couple of other commands here. Some of the SQL Server cmdlets
that we have available to us. So, let’s pop over here. We are going to go ahead and
run a couple of these cmdlets. First, we are going to start off with the Invoke SQL command. So, you can see that we’ve run
a simple invoke SQL command running a simple query,
selecting our version number. So, that was simple enough. We can run other commands too
using invoke SQL commands. For instance, we can invoke functions and return the results like here. The results of select date. We can also run queries. Select the data from different tables. Let’s go ahead and have
a look at doing that. Here we are using Invoke SQL command. Passing it in the database we are going to be using, adventureworks. And then using the query
parameter to give it a query where we are
selecting a couple of columns from the human resources employees table. And there you’ll see. That’s the basics of using
PowerShell and SQL Server. In part 2, we’ll dig into
this in a little more detail. And now, you see how to get
started using PowerShell to manage SQL Server. In part 2, wer’re going
to dive into this deeper and we’ll show you some actual examples of running various useful
commands to manage SQL Server. Thanks for watching!

2 Comments

  • Kushal Solanki

    When I do Import-Module "sqlps" -DisableNameChecking. Base dir is not changed to sql server as shown in the video. I also tried installing sql server module but get the error

    PS C:Windowssystem32> Import-Module "sqlps" -DisableNameChecking
    PS C:Windowssystem32> dir sqlserver:sqlTWEHRDB1TWEHRDB1
    Audits
    AvailabilityGroups
    BackupDevices
    Credentials
    CryptographicProviders
    Databases
    Endpoints
    JobServer
    Languages
    LinkedServers
    Logins
    Mail
    ResourceGovernor
    Roles
    ServerAuditSpecifications
    SystemDataTypes
    SystemMessages
    Triggers
    UserDefinedMessages
    PS C:Windowssystem32> Install-Module -Name SqlServer
    PackageManagementInstall-Package : No match was found for the specified search criteria and module name 'SqlServer'.
    Try Get-PSRepository to see all available registered module repositories.
    At C:Program FilesWindowsPowerShellModulesPowerShellGet1.0.0.1PSModule.psm1:1772 char:21
    + … $null = PackageManagementInstall-Package @PSBoundParameters
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : ObjectNotFound: (Microsoft.Power….InstallPackage:InstallPackage) [Install-Package], Ex
    ception
    + FullyQualifiedErrorId : NoMatchFoundForCriteria,Microsoft.PowerShell.PackageManagement.Cmdlets.InstallPackage

    PS C:Windowssystem32>

Leave a Reply

Your email address will not be published. Required fields are marked *