SQL Server 2014 with Powershell v5 Cookbook

Chapter 2. SQL Server and PowerShell Basic Tasks

In this chapter, we will cover:

Listing SQL Server instances
Discovering SQL Server services
Starting/stopping SQL Server services
Listing SQL Server configuration settings
Changing SQL Server Instance configurations
Searching for database objects
Scripting SQL Server Stored Procedures
Creating a database
Altering database properties
Dropping a database
Changing a database owner
Creating a table
Creating a view
Creating a stored procedure
Creating a user defined function
Creating a trigger
Creating an index
Executing a query / SQL script
Performing bulk export using Invoke-SqlCmd
Performing bulk export using the bcp command-line utility
Performing bulk import using BULK INSERT
Performing bulk import using the bcp command-line utility
Connecting to an Azure SQL database
Creating a table in an Azure SQL database

Introduction

In this chapter, we will discuss scripts and snippets of code that accomplish basic SQL Server tasks using PowerShell. We will start with simple tasks such as listing SQL Server instances and creating objects such as tables, indexes, stored procedures, and functions to get you comfortable with working with SQL Server programmatically.

You will find that many of the recipes can be accomplished using PowerShell and SQL Management Objects (SMO). SMO is a library that exposes SQL Server classes that allow programmatic manipulation and automation of many database tasks. For some recipes, we will also explore alternative ways of accomplishing the same tasks using different native PowerShell cmdlets.

Note

SMO is explained in more detail in Chapter 1, Getting Started with SQL Server and PowerShell.

However, even if we are exploring how to create some common database objects using PowerShell, keep in mind that PowerShell will not always be the best tool for the task. There will be tasks...

Listing SQL Server instances

In this recipe, we will list all SQL Server instances in the local network.

Getting ready

How to do it...

Let's look at the steps to list your SQL Server instances:

Open PowerShell ISE as administrator.

Let's use the Start-Service cmdlet to start the SQL Browser service:

Import-Module SQLPS -DisableNameChecking

#out of the box, the SQLBrowser is disabled. To enable:
Set-Service SQLBrowser -StartupType Automatic

#sql browser must be installed and running for us
#to discover SQL Server instances
Start-Service "SQLBrowser"

Next, you need to create a ManagedComputer object to get access to instances. Type the following script and run:

$instanceName = "localhost"
$managedComputer = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $instanceName

#list server instances
$managedComputer.ServerInstances

Your result should look similar to the...

Discovering SQL Server services

In this recipe, we will enumerate all SQL Server services and list their statuses.

Getting ready

Check which SQL Server services are installed in your instance. Go to Start | Run and type services.msc. You should see a screen similar to this:

How to do it...

Let's assume you are running this script on the server box:

Open PowerShell ISE as administrator.

Add the following code and execute:

Import-Module SQLPS -DisableNameChecking

#you can replace localhost with your instance name
$instanceName = "localhost"
$managedComputer = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $instanceName

#list services
$managedComputer.Services |
Select-Object Name, Type, ServiceState, DisplayName |
Format-Table -AutoSize

Your result will look similar to the one shown in the following screenshot:

Items listed in your screen will vary depending on the features installed and running in your instance

Confirm that these are the services that exist in your...

Starting/stopping SQL Server services

This recipe describes how to start and/or stop SQL Server services.

Getting ready

Check which SQL Services are installed in your machine. Go to Start | Run and type services.msc. You should see a screen similar to this:

How to do it...

Let's look at the steps to toggle your SQL Server services states:

Open PowerShell ISE as administrator.

Add the following code:

$verbosepreference = "Continue"
$services = @("SQLBrowser", "ReportServer")
$hostName = "localhost"

$services |
ForEach-Object {
   $service = Get-Service -Name $_
   if($service.Status -eq "Stopped")
   {
      Write-Verbose "Starting $($service.Name) ...."
      Start-Service -Name $service.Name
   }
   else
   {
      Write-Verbose "Stopping $($service.Name) ...."
      Stop-Service -Name $service.Name
   }
}
$verbosepreference = "SilentlyContinue"

Note

Be careful; the status may return prematurely as "started...

Listing SQL Server configuration settings

This recipe walks through how to list SQL Server configurable and nonconfigurable instance settings using PowerShell.

How to do it...

Let's look at the steps involved in listing SQL Server configuration settings:

Open PowerShell ISE as administrator.

Import the SQLPS module and create a new SMO Server Object:

#import SQL Server module
Import-Module SQLPS -DisableNameChecking

#replace this with your instance name
$instanceName = "localhost"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

To explore what members and methods are included in the SMO server, use the following:

#Explore: get all properties available for a server object
#see http://msdn.microsoft.com/en-us/library/ms212724.aspx

$server |
Get-Member |
Where-Object MemberType -eq "Property"

First, let's explore the Information class.

#The Information class lists nonconfigurable
#instance settings, like BuildNumber...

Changing SQL Server Instance configurations

This recipe discusses how to change instance configuration settings using PowerShell.

Getting ready

For this recipe, we will perform the following tasks:

Change fill factor to 60 percent
Enable SQL Server Agent
Set Minimum Server Memory to 500 MB
Change authentication method to Mixed

How to do it...

Let's change some SQL Server settings using PowerShell:

Open PowerShell ISE as administrator.

Import the SQLPS module and create a new SMO Server Object:

#import SQL Server module
Import-Module SQLPS -DisableNameChecking

#replace this with your instance name
$instanceName = "localhost"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

Add the following script and run:

<#
run value vs config value
config_value," is what the setting has been set to (but may or may not be what SQL Server is actually running now. Some settings don't go into effect until SQL Server has been restarted, or...

Introduction

Note

SMO is explained in more detail in Chapter 1, Getting Started with SQL Server and PowerShell.

However, even if we are exploring how to create some common database objects using PowerShell, keep in mind that PowerShell will not always be the best tool for the task. There will be tasks...

Listing SQL Server instances

In this recipe, we will list all SQL Server instances in the local network.

Getting ready

How to do it...

Let's look at the steps to list your SQL Server instances:

Open PowerShell ISE as administrator.

Let's use the Start-Service cmdlet to start the SQL Browser service:

Import-Module SQLPS -DisableNameChecking

#out of the box, the SQLBrowser is disabled. To enable:
Set-Service SQLBrowser -StartupType Automatic

#sql browser must be installed and running for us
#to discover SQL Server instances
Start-Service "SQLBrowser"

Next, you need to create a ManagedComputer object to get access to instances. Type the following script and run:
```
$instanceName = "localhost"
$managedComputer = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $instanceName

#list server instances
$managedComputer.ServerInstances
```
Your result should look similar to the one shown in the following screenshot...

Discovering SQL Server services

In this recipe, we will enumerate all SQL Server services and list their statuses.

Getting ready

Check which SQL Server services are installed in your instance. Go to Start | Run and type services.msc. You should see a screen similar to this:

How to do it...

Let's assume you are running this script on the server box:

Open PowerShell ISE as administrator.

Add the following code and execute:

Import-Module SQLPS -DisableNameChecking

#you can replace localhost with your instance name
$instanceName = "localhost"
$managedComputer = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $instanceName

#list services
$managedComputer.Services |
Select-Object Name, Type, ServiceState, DisplayName |
Format-Table -AutoSize

Your result will look similar to the one shown in the following screenshot:

Items listed in your screen will vary depending on the features installed and running in your instance

Confirm that these are the services that exist in your server. Check...

Starting/stopping SQL Server services

This recipe describes how to start and/or stop SQL Server services.

Getting ready

Check which SQL Services are installed in your machine. Go to Start | Run and type services.msc. You should see a screen similar to this:

How to do it...

Let's look at the steps to toggle your SQL Server services states:

Open PowerShell ISE as administrator.

Add the following code:

$verbosepreference = "Continue"
$services = @("SQLBrowser", "ReportServer")
$hostName = "localhost"

$services |
ForEach-Object {
   $service = Get-Service -Name $_
   if($service.Status -eq "Stopped")
   {
      Write-Verbose "Starting $($service.Name) ...."
      Start-Service -Name $service.Name
   }
   else
   {
      Write-Verbose "Stopping $($service.Name) ...."
      Stop-Service -Name $service.Name
   }
}
$verbosepreference = "SilentlyContinue"

Note

Be careful; the status may return prematurely as "started" when in fact the service is still in the "starting" state.

Execute and confirm that the...

Listing SQL Server configuration settings

This recipe walks through how to list SQL Server configurable and nonconfigurable instance settings using PowerShell.

How to do it...

Let's look at the steps involved in listing SQL Server configuration settings:

Open PowerShell ISE as administrator.

Import the SQLPS module and create a new SMO Server Object:

#import SQL Server module
Import-Module SQLPS -DisableNameChecking

#replace this with your instance name
$instanceName = "localhost"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

To explore what members and methods are included in the SMO server, use the following:

#Explore: get all properties available for a server object
#see http://msdn.microsoft.com/en-us/library/ms212724.aspx

$server |
Get-Member |
Where-Object MemberType -eq "Property"

First, let's explore the Information class.

#The Information class lists nonconfigurable
#instance settings, like BuildNumber,
#OSVersion, ProductLevel etc...

Changing SQL Server Instance configurations

This recipe discusses how to change instance configuration settings using PowerShell.

Getting ready

For this recipe, we will perform the following tasks:

Change fill factor to 60 percent
Enable SQL Server Agent
Set Minimum Server Memory to 500 MB
Change authentication method to Mixed

How to do it...

Let's change some SQL Server settings using PowerShell:

Open PowerShell ISE as administrator.

Import the SQLPS module and create a new SMO Server Object:

#import SQL Server module
Import-Module SQLPS -DisableNameChecking

#replace this with your instance name
$instanceName = "localhost"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

Add the following script and run:

<#
run value vs config value
config_value," is what the setting has been set to (but may or may not be what SQL Server is actually running now. Some settings don't go into effect until SQL Server has been restarted, or until the RECONFIGURE WITH...

Searching for database objects

In this recipe, we will search database objects based on a search string using PowerShell.

Getting ready

We will use the AdventureWorks2014 database in this recipe and look for SQL Server objects with the word Product in it.

To get an idea of what we are expecting to retrieve, run the following script in SQL Server Management Studio:

USE AdventureWorks2014
GO
SELECT
  *
FROM
  sys.objects
WHERE
  name LIKE '%Product%'
   -- check only for table, view, function
   -- or stored procedure
  AND [type] IN ('U', 'FN', 'P', 'V')
ORDER BY
  [type]

This will get you 23 results. Remember this number.

How to do it...

Let's see how we can search for objects in your SQL Server database using PowerShell:

Open PowerShell ISE as administrator.

Import the SQLPS module and create a new SMO Server Object:

#import SQL Server module
Import-Module SQLPS -DisableNameChecking

#replace this with your instance name
$instanceName = "localhost"
$server = New-Object -TypeName Microsoft.SqlServer...

Scripting SQL Server Stored Procedures

In this recipe, we will explore how to script SQL Server objects, specifically; how to script all non-system and unencrypted stored procedures in a database and save them individually in their own .sql files.

Getting ready

We will use the AdventureWorks2014 database in this recipe. Before we start, let's check which stored procedures are available in this database so that we can cross-check against the scripts we are going to create later.

Open SQL Server Management Studio and navigate to the Programmability node under AdventureWorks2014. You should see a list of stored procedures as shown in the following screenshot:

How to do it...

Let's see how we can script stored procedures in your SQL Server database using PowerShell:

Open PowerShell ISE as administrator.

Import the SQLPS module and create a new SMO Server Object:

#import SQL Server module
Import-Module SQLPS -DisableNameChecking

#replace this with your instance name
$instanceName = "localhost"
$server...

Creating a database

This recipe walks through creating a database with default properties using PowerShell.

Getting ready

In this example, we are going to create a database called TestDB, and we assume that this database does not yet exist in your instance.

For your reference, the equivalent T-SQL code of this task is as follows:

CREATE DATABASE TestDB

How to do it...

Follow these steps to create a simple database in SQL Server:

Open PowerShell ISE as administrator.

Import the SQLPS module and create a new SMO Server Object:

#import SQL Server module
Import-Module SQLPS -DisableNameChecking

#replace this with your instance name
$instanceName = "localhost"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

Add the following script and run:

#database TestDB with default settings
#assumption is that this database does not yet exist
$dbName = "TestDB"
$db = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database($server, $dbName)
$db.Create()...

Altering database properties

This recipe shows you how to change database properties using SMO and PowerShell.

Getting ready

Create a database called TestDB by following the steps in the Creating a database recipe.

Using TestDB, we will perform the following tasks:

Change ANSI NULLS Enabled to False
Change ANSI PADDING Enabled to False
Change compatibility version to 110 (SQL Server 2012)
Restrict user access to RESTRICTED_USER
Set the database to Read Only

How to do it...

Let's look at the steps involved in altering databases using PowerShell:

Open PowerShell ISE as administrator.

Import the SQLPS module and create a new SMO Server Object:

#import SQL Server module
Import-Module SQLPS -DisableNameChecking

#replace this with your instance name
$instanceName = "localhost"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

Add the following script and run:

#database
$dbName = "TestDB"

#we are going to assume db exists
$db = $server.Databases[$dbName...

Dropping a database

This recipe shows how you can drop a database using PowerShell and SMO.

Getting ready

This task assumes you have created a database called TestDB. If you don't have it, create the database TestDB by following the steps in the Creating a database recipe.

How to do it...

Here are the steps to drop your TestDB database:

Open PowerShell ISE as administrator.

Import the SQLPS module and create a new SMO Server Object:

#import SQL Server module
Import-Module SQLPS -DisableNameChecking

#replace this with your instance name
$instanceName = "localhost"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

Add the following script and run:

$dbName = "TestDB"

#need to check if database exists, and if it does, drop it
$db = $server.Databases[$dbName]
if ($db)
{
      #we will use KillDatabase instead of Drop
      #Kill database will drop active connections before
      #dropping the database
      $server.KillDatabase($dbName)
}

How it works...

Changing database owner

This recipe shows how to programmatically change a SQL Server database owner.

Getting ready

This task assumes you have created a database called TestDB, and a Windows account QUERYWORKS\srogers. QUERYWORKS\srogers has been created in our test VM.

Note

For more information, see Appendix B, Create a SQL Server VM.

If you don't have it, create the database TestDB by following the steps in the Creating a database recipe.

How to do it...

Let's look at the steps involved in changing a database owner:

Open PowerShell ISE as administrator.

Import the SQLPS module and create a new SMO Server Object:

#import SQL Server module
Import-Module SQLPS -DisableNameChecking

#replace this with your instance name
$instanceName = "localhost"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

Add the following script and run:

#create database handle
$dbName = "TestDB"
$db = $server.Databases[$dbName]

#display current owner
$db.Owner

#change owner...

Creating a table

This recipe shows how to create a table using PowerShell and SMO.

Getting ready

We will use the AdventureWorks2014 database to create a table named Student, which has five columns and two default constraints. To give you a better idea of what we are trying to achieve, the equivalent T-SQL script needed to create this table is as follows:

USE AdventureWorks2014
GO
CREATE TABLE [dbo].[Student](
  [StudentID] [INT] IDENTITY(1,1) NOT NULL,
  [FName] [VARCHAR](50) NULL,
  [LName] [VARCHAR](50) NOT NULL,
  [DateOfBirth] [DATETIME] NULL,
  [Age]  AS (DATEPART(YEAR,GETDATE())-DATEPART(YEAR,[DateOfBirth])),
    CONSTRAINT [PK_Student_StudentID] PRIMARY KEY CLUSTERED
   (
     [StudentID] ASC
   )
)

GO
ALTER TABLE [dbo].[Student] ADD  CONSTRAINT [DF_Student_LName] DEFAULT ('Doe') FOR [LName]
GO

ALTER TABLE [dbo].[Student] ADD  CONSTRAINT [DF_Student_DateOfBirth]  DEFAULT ('1800-00-00') FOR [DateOfBirth]
GO

How to do it...

Let's create the Student table using PowerShell:

Open PowerShell...

Creating a view

This recipe shows how to create a view using PowerShell and SMO.

Getting ready

We will use the Person.Person table in the AdventureWorks2014 database for this recipe.

To give you an idea what we are attempting to create in this recipe, this is the T-SQL equivalent.

CREATE VIEW dbo.vwVCPerson
AS
SELECT
   TOP 100
  BusinessEntityID,
  LastName,
  FirstName
FROM
  Person.Person
WHERE
    PersonType = 'IN'
GO

How to do it...

Let's check out the steps to create a view using PowerShell:

Open PowerShell ISE as administrator.

Import the SQLPS module and create a new SMO Server object:

#import SQL Server module
Import-Module SQLPS -DisableNameChecking

#replace this with your instance name
$instanceName = "localhost"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

Add the following script and run:

$dbName = "AdventureWorks2014"
$db = $server.Databases[$dbName]
$viewName = "vwVCPerson"
$view = $db.Views[$viewName]

#if view exists, drop it...

Creating a stored procedure

This recipe shows how to create an encrypted stored procedure using SMO and PowerShell.

Getting ready

The T-SQL equivalent of the encrypted stored procedure we are about to recreate in PowerShell is as follows:

CREATE PROCEDURE [dbo].[uspGetPersonByLastName]
@LastName [varchar](50)
WITH ENCRYPTION
AS

SELECT
   TOP 10
   BusinessEntityID,
   LastName
FROM
   Person.Person
WHERE
   LastName = @LastName

How to do it...

Follow these steps to create the uspGetPersonByLastName stored procedure using PowerShell:

Open PowerShell ISE as administrator.

Import the SQLPS module and create a new SMO Server Object:

#import SQL Server module
Import-Module SQLPS -DisableNameChecking

#replace this with your instance name
$instanceName = "localhost"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

Add the following script and run:

$dbName = "AdventureWorks2014"
$db = $server.Databases[$dbName]

#storedProcedure class on MSDN:
#http:/...

Creating a trigger

This recipe shows how to programmatically create a trigger in SQL Server using SMO and PowerShell

Getting ready

For this recipe, we will use the Person.Person table in the AdventureWorks2014 database. We will create a trivial AFTER trigger that merely displays values from the inserted and deleted tables upon firing.

The following is the T-SQL equivalent of what we are going to accomplish programmatically in this section:

CREATE TRIGGER [Person].[tr_u_Person]
ON [Person].[Person]
AFTER  UPDATE
AS

  SELECT
     GETDATE() AS UpdatedOn,
     SYSTEM_USER AS UpdatedBy,
     i.LastName AS NewLastName,
     i.FirstName AS NewFirstName,
     d.LastName AS OldLastName,
     d.FirstName AS OldFirstName
  FROM
     inserted i
     INNER JOIN deleted d
     ON i.BusinessEntityID = d.BusinessEntityID

How to do it...

Let's follow these steps to create an AFTER trigger in PowerShell:

Open PowerShell ISE as administrator.
Import the SQLPS module and create a new SMO Server object:
```
#import SQL...
```

Creating an index

This recipe shows how to create a nonclustered index with an included column using PowerShell and SMO.

Getting ready

We will use the Person.Person table in the AdventureWorks2014 database. We will create a nonclustered index on FirstName, LastName, and include MiddleName. The T-SQL equivalent of this task is as follows:

CREATE NONCLUSTERED INDEX [idxLastNameFirstName]
ON [Person].[Person]
(
  [LastName] ASC,
  [FirstName] ASC
)
INCLUDE ([MiddleName])
GO

How to do it...

Let's check out how we can create an index using PowerShell:

Open PowerShell ISE as administrator.

Import the SQLPS module and create a new SMO Server object:

#import SQL Server module
Import-Module SQLPS -DisableNameChecking

#replace this with your instance name
$instanceName = "localhost"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

Add the following script and run:

$dbName = "AdventureWorks2014"
$db = $server.Databases[$dbName]

$tableName = "Person"
$schemaName...

Executing a query/SQL script

This recipe shows how you can execute either a hardcoded query or a SQL Script from PowerShell.

Getting ready

Create a file in your C:\Temp folder called SampleScript.sql. This should contain the following:

SELECT TOP 10 *
FROM Person.Person

How to do it...

The following are the steps in executing a query / SQL script:

Open PowerShell ISE as administrator.

Import the SQLPS module and create a new SMO Server Object:

#import SQL Server module
Import-Module SQLPS -DisableNameChecking

#replace this with your instance name
$instanceName = "localhost"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

Add the following script and run:

$dbName = "AdventureWorks2014"
$db = $server.Databases[$dbName]

#execute a passthrough query, and export to a CSV file

#line continuation in code below only happens at
#the pipe (|) delimiter
Invoke-Sqlcmd -Query "SELECT * FROM Person.Person"
-ServerInstance "$instanceName" -Database $dbName...

Page 1 of 25

Alan Jaeger Mar 04, 2016

Aktuell kenne ich kein besseres Buch mit diesem Fokus, deshalb bekommt es volle Sternzahl, wenn auch in Punkto Sprache, deutsche Ausgabe, Luft nach hoben ist.

Amazon Verified review

Enrique Thedy Jan 25, 2016

The book is very complete and the explanations are clear! An imprescindible complement for any SQL Server user

Dan Q Yang May 08, 2016

good one!

txtester Aug 20, 2019

I am using PowerShell on a greater scale, especially with SQL Server, and I have bought previous books with this subject matter. None of the PowerShell books I have bought are really no better than the Blog Posts and public boards, but at least they have some organization that can be useful. This book has none. Not sure what the other two reviewers were looking at, but the several times I have tried to get useful information out of this book, it is simply too frustrating to find anything. Since the cost of this book was higher than most of the other books I have bought (@ $30), I did expect better.

kit Apr 27, 2020

現在のpowershell では対応しないように思います。