Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Save more on your purchases! discount-offer-chevron-icon
Savings automatically calculated. No voucher code required.
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Newsletter Hub
Free Learning
Arrow right icon
SQL Server 2012 with PowerShell V3 Cookbook
SQL Server 2012 with PowerShell V3 Cookbook

SQL Server 2012 with PowerShell V3 Cookbook:

eBook
$9.99 $39.99
Paperback
$65.99
Subscription
Free Trial
Renews at $19.99p/m

What do you get with Print?

Product feature icon Instant access to your digital eBook copy whilst your Print order is Shipped
Product feature icon Paperback book shipped to your preferred address
Product feature icon Download this book in EPUB and PDF formats
Product feature icon Access this title in our online reader with advanced features
Product feature icon DRM FREE - Read whenever, wherever and however you want
OR
Modal Close icon
Payment Processing...
tick Completed

Shipping Address

Billing Address

Shipping Methods
Table of content icon View table of contents Preview book icon Preview Book

SQL Server 2012 with PowerShell V3 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

  • 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 trigger

  • Creating an index

  • Executing a query / SQL script

  • Performing bulk export using Invoke-Sqlcmd

  • Performing bulk export using bcp

  • Performing bulk import using BULK INSERT

  • Performing bulk import using bcp

Introduction


This chapter demonstrates scripts and snippets of code that accomplish some 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, which allows for 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.

Even though we are exploring how to create some common database objects using PowerShell, I would like to note that PowerShell is not always the best tool for the task. There will be...

Listing SQL Server instances


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

Getting ready

Log in to the server that has your SQL Server development instance, as an administrator.

How to do it...

  1. Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

  2. Let's use the Start-Service cmdlet to start SQLBrowser:

    Import-Module SQLPS -DisableNameChecking
    
    #sql browser must be installed and running
    Start-Service "SQLBrowser"
  3. Next, you need to create a ManagedComputer object to get access to instances. Type the following script and run it:

    $instanceName = "KERRIGAN"  
    $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:

    Note that $managedComputer.ServerInstances gives you not only instance names, but also additional properties such as ServerProtocols...

Discovering SQL Server services


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

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.

  1. Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

  2. Add the following code and execute it:

    Import-Module SQLPS
    
    #replace KERRIGAN with your instance  name
    $instanceName = "KERRIGAN"  
    $managedComputer = New-Object 'Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer' $instanceName
    
    #list services        
    $managedComputer.Services | 
    Select Name, Type, Status, DisplayName | 
    Format-Table -AutoSize

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

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

  3. Confirm that these are the services...

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 states for your SQL Server services:

  1. Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

  2. Add the following code. Note that this code will work in both PowerShell V2 and V3:

    $Verbosepreference = "Continue"
    $services = @("SQLBrowser", "ReportServer")
    $hostName = "KERRIGAN"
    
    $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"
  3. Execute and...

Listing SQL Server configuration settings


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

How to do it...

  1. Open the PowerShell ISE. Go to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

  2. 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 = "KERRIGAN"
    $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 code snippet in PowerShell V3:

    #Explore: get all properties available for a server object
    #http://msdn.microsoft.com/en-us/library/ms212724.aspx
    $server | Get-Member | Where MemberType -eq "Property"

    In PowerShell V2, you will need to slightly modify your syntax:

    $server | Get-Member | Where {$_.MemberType -eq "Property"}
    #The Information class lists...

Changing SQL Server instance configurations


This recipe walks through how to change instance configuration settings using PowerShell.

Getting ready

For this recipe, we will:

  • Change FillFactor 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:

  1. Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

  2. 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 = "KERRIGAN"
    $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
  3. Add the following script and run it:

    <#
    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...

Searching for database objects


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

Getting ready

We will use AdventureWorks2008R2, in this exercise, and will look for SQL Server objects with the word "Product" in their names.

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

USE AdventureWorks2008R2
GO
SELECT 
 * 
FROM 
 sys.objects
WHERE 
 name LIKE '%Product%'
 -- filter table level objects only
 AND [type] NOT IN ('C', 'D', 'PK', 'F') 
ORDER BY 
 [type]

This will get you 23 results. Remember this number.

How to do it...

  1. Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

  2. 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 = "KERRIGAN"
    $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList...

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 for this task is:

CREATE DATABASE TestDB

How to do it...

  1. Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

  2. 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 = "KERRIGAN"
    $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
  3. Add the following script and run it:

    #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:

  • Change ANSI NULLS Enabled to False

  • Change ANSI PADDING Enabled to False

  • Restrict user access to RESTRICTED_USER

  • Set the database to Read Only

How to do it...

  1. Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

  2. 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 = "KERRIGAN"
    $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
  3. Add the following script and run

    #database
    $dbName = "TestDB"
    
    #we are going to assume db exists
    $db = $server.Databases[$dbName]
    
    #DatabaseOptions
    #change ANSI NULLS and ANSI PADDING
    $db.DatabaseOptions.AnsiNullsEnabled...

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 haven't, create one by following the steps in the Creating a database recipe.

How to do it...

The following are the steps to drop your TestDB database:

  1. Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

  2. 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 = "KERRIGAN"
    $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
  3. Add the following script and run it:

    $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...

Changing a 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 that a Windows account QUERYWORKS\aterra. QUERYWORKS\aterra has been created in your test VM.

Note

See Appendix D, Creating a SQL Server VM.

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

How to do it...

  1. Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

  2. 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 = "KERRIGAN"
    $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
  3. Add the following script and run it:

    #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 AdventureWorks2008R2 database to create a table named Student, which has five columns. 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 AdventureWorks2008R2
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

How to do it...

Let's create the Student table using PowerShell:

  1. Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

  2. 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...

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 AdventureWorks2008R2 database for this recipe.

To give you an idea of 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'
ORDER BY
    LastName
GO

How to do it...

  1. Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

  2. 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 = "KERRIGAN"
    $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
  3. Add the following script and run it:

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

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:

  1. Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

  2. 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 = "KERRIGAN"
    $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
  3. Add the following script and run it:

    $dbName = "AdventureWorks2008R2...

Creating a trigger


This recipe demonstrates 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 AdventureWorks2008R2 database. We will create a trivial AFTER trigger that merely displays values from the inserted and deleted records 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:

  1. Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows...

Creating an index


This recipe demonstrates how to create a non-clustered index with an included column using PowerShell and SMO.

Getting ready

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

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

How to do it...

  1. Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

  2. 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 = "KERRIGAN"
    $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
  3. Add the following script and run it:

    $dbName = "AdventureWorks2008R2"
    $db = $server.Databases[$dbName]
    
    $tableName...

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:

SELECT * 
FROM Person.Person

How to do it...

  1. Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

  2. 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 = "KERRIGAN"
    $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
  3. Add the following script and run it:

    $dbName = "AdventureWorks2008R2"
    $db = $server.Databases[$dbName]
    
    #execute a passthrough query, and export to a CSV file
    Invoke-Sqlcmd `
    -Query "SELECT * FROM Person.Person" `
    -ServerInstance "$instanceName" `
    -Database $dbName | 
    Export-Csv -LiteralPath "C:\Temp\ResultsFromPassThrough.csv" `
    ...

Performing bulk export using Invoke-Sqlcmd


This recipe demonstrates how to export contents of a table to a CSV file using PowerShell and the Invoke-Sqlcmd cmdlet.

Getting ready

Make sure you have access to the AdventureWorks2008R2 database. We will use the Person.Person table.

Create a C:\Temp folder, if you don't already have one on your system.

How to do it...

  1. Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

  2. 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 = "KERRIGAN"
    $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
  3. Add the following script and run it:

    #database handle
    $dbName = "AdventureWorks2008R2"
    $db = $server.Databases[$dbName]
    
    #export file name
    $exportfile = "C:\Temp\Person_Person.csv"
    
    $query = @"
    SELECT 
       * 
    FROM 
       Person.Person
    "@
    Invoke...

Performing bulk export using bcp


This recipe demonstrates how to export contents of a table to a CSV file using PowerShell and bcp.

Getting ready

Make sure you have access to the AdventureWorks2008R2 database. We will export the Person.Person table to a timestamped text file delimited by a pipe (|).

Create a C:\Temp\Exports folder, if you don't already have it on your system.

How to do it...

  1. Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

  2. Add the following script and run the following code:

    $server = "KERRIGAN"
    $table = "AdventureWorks2008R2.Person.Person"
    $curdate = Get-Date -Format "yyyy-MM-dd_hmmtt"
    
    $foldername = "C:\Temp\Exports\"
    
    #format file name
    $formatfilename = "$($table)_$($curdate).fmt"
    
    #export file name
    $exportfilename = "$($table)_$($curdate).csv"
    
    $destination_exportfilename = "$($foldername)$($exportfilename)"
    $destination_formatfilename = "$($foldername)$($formatfilename)"
    
    #command to generate format file
    $cmdformatfile...

Performing bulk import using BULK INSERT


This recipe will walk you through importing contents of a CSV file to SQL Server using PowerShell and BULK INSERT.

Getting ready

To do a test import, we will first need to create a Person table similar to the Person.Person table from the AdventureWorks2008R2 database, with some slight modifications.

We will create this in the Test schema, and we will remove some of the constraints and keep this table as simple and independent as we can.

To create the table that we need for this exercise, open up Management Studio and run the following code:

CREATE SCHEMA [Test]
GO
CREATE TABLE [Test].[Person](
 [BusinessEntityID] [int] NOT NULL PRIMARY KEY,
 [PersonType] [nchar](2) NOT NULL,
 [NameStyle] [dbo].[NameStyle] NOT NULL,
 [Title] [nvarchar](8) NULL,
 [FirstName] [dbo].[Name] NOT NULL,
 [MiddleName] [dbo].[Name] NULL,
 [LastName] [dbo].[Name] NOT NULL,
 [Suffix] [nvarchar](10) NULL,
 [EmailPromotion] [int] NOT NULL,
 [AdditionalContactInfo] [xml] NULL,
 [Demographics...

Performing bulk import using bcp


This recipe will walk you through the process of importing the contents of a CSV file to SQL Server using PowerShell and bcp.

Getting ready

To do a test import, let's first create a Person table similar to the Person.Person table from the AdventureWorks2008R2 database, with some slight modifications. We will create this in the Test schema, and we will remove some of the constraints and keep this table as simple and independent as we can.

If Test.Person does not yet exist in your environment, let's create it. Open up Management Studio, and run the following code:

CREATE SCHEMA [Test]
GO
CREATE TABLE [Test].[Person](
 [BusinessEntityID] [int] NOT NULL PRIMARY KEY,
 [PersonType] [nchar](2) NOT NULL,
 [NameStyle] [dbo].[NameStyle] NOT NULL,
 [Title] [nvarchar](8) NULL,
 [FirstName] [dbo].[Name] NOT NULL,
 [MiddleName] [dbo].[Name] NULL,
 [LastName] [dbo].[Name] NOT NULL,
 [Suffix] [nvarchar](10) NULL,
 [EmailPromotion] [int] NOT NULL,
 [AdditionalContactInfo] [xml...
Left arrow icon Right arrow icon
Download code icon Download Code

Key benefits

  • Provides over a hundred practical recipes that utilize PowerShell to automate, integrate and simplify SQL Server tasks
  • Offers easy to follow, step-by-step guide to getting the most out of SQL Server and PowerShell
  • Covers numerous guidelines, tips, and explanations on how and when to use PowerShell cmdlets, WMI, SMO, .NET classes or other components
  • Builds a strong foundation that gets you comfortable using PowerShell with SQL Server--empowering you to create more complex scripts that you need in your day-to-day job

Description

PowerShell is Microsoft's new command-line shell and scripting language that promises to simplify automation and integration across different Microsoft applications and components. Database professionals can leverage PowerShell by utilizing its numerous built-in cmdlets, or using any of the readily available .NET classes, to automate database tasks, simplify integration, or just discover new ways to accomplish the job at hand."SQL Server 2012 with PowerShell V3 Cookbook" provides easy-to-follow, practical examples for the busy database professional. Whether you're auditing your servers, or exporting data, or deploying reports, there is a recipe that you can use right away!You start off with basic topics to get you going with SQL Server and PowerShell scripts and progress into more advanced topics to help you manage and administer your SQL Server databases.The first few chapters demonstrate how to work with SQL Server settings and objects, including exploring objects, creating databases, configuring server settings, and performing inventories. The book then deep dives into more administration topics like backup and restore, credentials, policies, jobs.Additional development and BI-specific topics are also explored, including deploying and downloading assemblies, BLOB data, SSIS packages, and SSRS reports. A short PowerShell primer is also provided as a supplement in the Appendix, which the database professional can use as a refresher or occasional reference material. Packed with more than 100 practical, ready-to-use scripts, "SQL Server 2012 with PowerShell V3 Cookbook" will be your go-to reference in automating and managing SQL Server.

Who is this book for?

This book is written for the SQL Server database professional (DBA, developer, BI developer) who wants to use PowerShell to automate, integrate, and simplify database tasks. A little bit of scripting background is helpful, but not necessary.

What you will learn

  • Create an inventory of database properties and server configuration settings
  • Backup and restore databases
  • Execute queries to multiple servers
  • Maintain permissions and security for users
  • Import and export XML into SQL Server
  • Extract CLR assemblies and BLOB objects from the database
  • Explore database objects
  • Manage and deploy SSIS packages and SSRS reports
  • Manage and monitor running SQL Server services and accounts
  • Parse and display the contents of trace files
  • Create SQL Server jobs, alerts and operators
  • Find blocking processes that are hampering your database performance
Estimated delivery fee Deliver to Ecuador

Standard delivery 10 - 13 business days

$19.95

Premium delivery 3 - 6 business days

$40.95
(Includes tracking information)

Product Details

Country selected
Publication date, Length, Edition, Language, ISBN-13
Publication date : Oct 25, 2012
Length: 634 pages
Edition : 1st
Language : English
ISBN-13 : 9781849686464
Concepts :
Tools :

What do you get with Print?

Product feature icon Instant access to your digital eBook copy whilst your Print order is Shipped
Product feature icon Paperback book shipped to your preferred address
Product feature icon Download this book in EPUB and PDF formats
Product feature icon Access this title in our online reader with advanced features
Product feature icon DRM FREE - Read whenever, wherever and however you want
OR
Modal Close icon
Payment Processing...
tick Completed

Shipping Address

Billing Address

Shipping Methods
Estimated delivery fee Deliver to Ecuador

Standard delivery 10 - 13 business days

$19.95

Premium delivery 3 - 6 business days

$40.95
(Includes tracking information)

Product Details

Publication date : Oct 25, 2012
Length: 634 pages
Edition : 1st
Language : English
ISBN-13 : 9781849686464
Concepts :
Tools :

Packt Subscriptions

See our plans and pricing
Modal Close icon
$19.99 billed monthly
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Simple pricing, no contract
$199.99 billed annually
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Choose a DRM-free eBook or Video every month to keep
Feature tick icon PLUS own as many other DRM-free eBooks or Videos as you like for just $5 each
Feature tick icon Exclusive print discounts
$279.99 billed in 18 months
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Choose a DRM-free eBook or Video every month to keep
Feature tick icon PLUS own as many other DRM-free eBooks or Videos as you like for just $5 each
Feature tick icon Exclusive print discounts

Frequently bought together


Stars icon
Total $29.97 $108.97 $79.00 saved
Microsoft SQL Server 2012 Performance Tuning Cookbook
$65.99
Microsoft SQL Server 2012 Integration Services: An Expert Cookbook
$48.99
SQL Server 2012 with PowerShell V3 Cookbook
$65.99
Total $29.97$108.97 $79.00 saved Stars icon
Banner background image

Table of Contents

9 Chapters
Getting Started with SQL Server and PowerShell Chevron down icon Chevron up icon
SQL Server and PowerShell Basic Tasks Chevron down icon Chevron up icon
Basic Administration Chevron down icon Chevron up icon
Security Chevron down icon Chevron up icon
Advanced Administration Chevron down icon Chevron up icon
Backup and Restore Chevron down icon Chevron up icon
SQL Server Development Chevron down icon Chevron up icon
Business Intelligence Chevron down icon Chevron up icon
Helpful PowerShell Snippets Chevron down icon Chevron up icon

Customer reviews

Rating distribution
Full star icon Full star icon Full star icon Full star icon Half star icon 4.4
(5 Ratings)
5 star 80%
4 star 0%
3 star 0%
2 star 20%
1 star 0%
Parvinder Nijjar Aug 20, 2015
Full star icon Full star icon Full star icon Full star icon Full star icon 5
If your a SQL DBA with intermediate knowledge of Powershell then this is the ideal guide for you.
Amazon Verified review Amazon
Tony McBroom Aug 10, 2015
Full star icon Full star icon Full star icon Full star icon Full star icon 5
New to Powershell and this book is helping me immensely! Will update my review once I work my way through to the end, but it has been fantastic thus far...
Amazon Verified review Amazon
Mary Jane Zhang Jul 20, 2013
Full star icon Full star icon Full star icon Full star icon Full star icon 5
I wanted to learn how to use PowerShell with SQL Server. This book delivers easy to read chapters and easy to follow exercises. I liked the clear and to-the-point examples and explanations. While not exhaustive of all SQL Server 2012 features, this book provides a very good foundation to anyone who wants to start using PowerShell with SQL Server. It starts off with the basics, so even if you are not very familiar with PowerShell this book can get you started. Its inclusion of numerous SMO examples also bridge the short list of cmdlets available with the SQL Server product. You can take pieces from this book, and build your own complex automation solution!
Amazon Verified review Amazon
Scott Mattie Mar 29, 2013
Full star icon Full star icon Full star icon Full star icon Full star icon 5
Donabel did an amazing job with this book. I liked the way she started out with a base and then quickly added to the foundation. She starts off with simple powershell commands, tips and tools and then slowly starts to add more knowledge. It is a must read.
Amazon Verified review Amazon
Denis M. McDowell Jun 04, 2013
Full star icon Full star icon Empty star icon Empty star icon Empty star icon 2
I bought this book as a reference for some SQL Server 2012 automation I am working on. While this book covers many of the day to day functions of a DBA, it leaves out virtually all of the new SQL 2012 features and how to manage them in PowerShell. Not a word about AlwaysOn Availability Groups, Contained DB, or other important features. I was disappointed that these items were left out. It is almost as if they simply changed "SQL Server 2008 R2" to "SQL Server 2012".
Amazon Verified review Amazon
Get free access to Packt library with over 7500+ books and video courses for 7 days!
Start Free Trial

FAQs

What is the delivery time and cost of print book? Chevron down icon Chevron up icon

Shipping Details

USA:

'

Economy: Delivery to most addresses in the US within 10-15 business days

Premium: Trackable Delivery to most addresses in the US within 3-8 business days

UK:

Economy: Delivery to most addresses in the U.K. within 7-9 business days.
Shipments are not trackable

Premium: Trackable delivery to most addresses in the U.K. within 3-4 business days!
Add one extra business day for deliveries to Northern Ireland and Scottish Highlands and islands

EU:

Premium: Trackable delivery to most EU destinations within 4-9 business days.

Australia:

Economy: Can deliver to P. O. Boxes and private residences.
Trackable service with delivery to addresses in Australia only.
Delivery time ranges from 7-9 business days for VIC and 8-10 business days for Interstate metro
Delivery time is up to 15 business days for remote areas of WA, NT & QLD.

Premium: Delivery to addresses in Australia only
Trackable delivery to most P. O. Boxes and private residences in Australia within 4-5 days based on the distance to a destination following dispatch.

India:

Premium: Delivery to most Indian addresses within 5-6 business days

Rest of the World:

Premium: Countries in the American continent: Trackable delivery to most countries within 4-7 business days

Asia:

Premium: Delivery to most Asian addresses within 5-9 business days

Disclaimer:
All orders received before 5 PM U.K time would start printing from the next business day. So the estimated delivery times start from the next day as well. Orders received after 5 PM U.K time (in our internal systems) on a business day or anytime on the weekend will begin printing the second to next business day. For example, an order placed at 11 AM today will begin printing tomorrow, whereas an order placed at 9 PM tonight will begin printing the day after tomorrow.


Unfortunately, due to several restrictions, we are unable to ship to the following countries:

  1. Afghanistan
  2. American Samoa
  3. Belarus
  4. Brunei Darussalam
  5. Central African Republic
  6. The Democratic Republic of Congo
  7. Eritrea
  8. Guinea-bissau
  9. Iran
  10. Lebanon
  11. Libiya Arab Jamahriya
  12. Somalia
  13. Sudan
  14. Russian Federation
  15. Syrian Arab Republic
  16. Ukraine
  17. Venezuela
What is custom duty/charge? Chevron down icon Chevron up icon

Customs duty are charges levied on goods when they cross international borders. It is a tax that is imposed on imported goods. These duties are charged by special authorities and bodies created by local governments and are meant to protect local industries, economies, and businesses.

Do I have to pay customs charges for the print book order? Chevron down icon Chevron up icon

The orders shipped to the countries that are listed under EU27 will not bear custom charges. They are paid by Packt as part of the order.

List of EU27 countries: www.gov.uk/eu-eea:

A custom duty or localized taxes may be applicable on the shipment and would be charged by the recipient country outside of the EU27 which should be paid by the customer and these duties are not included in the shipping charges been charged on the order.

How do I know my custom duty charges? Chevron down icon Chevron up icon

The amount of duty payable varies greatly depending on the imported goods, the country of origin and several other factors like the total invoice amount or dimensions like weight, and other such criteria applicable in your country.

For example:

  • If you live in Mexico, and the declared value of your ordered items is over $ 50, for you to receive a package, you will have to pay additional import tax of 19% which will be $ 9.50 to the courier service.
  • Whereas if you live in Turkey, and the declared value of your ordered items is over € 22, for you to receive a package, you will have to pay additional import tax of 18% which will be € 3.96 to the courier service.
How can I cancel my order? Chevron down icon Chevron up icon

Cancellation Policy for Published Printed Books:

You can cancel any order within 1 hour of placing the order. Simply contact customercare@packt.com with your order details or payment transaction id. If your order has already started the shipment process, we will do our best to stop it. However, if it is already on the way to you then when you receive it, you can contact us at customercare@packt.com using the returns and refund process.

Please understand that Packt Publishing cannot provide refunds or cancel any order except for the cases described in our Return Policy (i.e. Packt Publishing agrees to replace your printed book because it arrives damaged or material defect in book), Packt Publishing will not accept returns.

What is your returns and refunds policy? Chevron down icon Chevron up icon

Return Policy:

We want you to be happy with your purchase from Packtpub.com. We will not hassle you with returning print books to us. If the print book you receive from us is incorrect, damaged, doesn't work or is unacceptably late, please contact Customer Relations Team on customercare@packt.com with the order number and issue details as explained below:

  1. If you ordered (eBook, Video or Print Book) incorrectly or accidentally, please contact Customer Relations Team on customercare@packt.com within one hour of placing the order and we will replace/refund you the item cost.
  2. Sadly, if your eBook or Video file is faulty or a fault occurs during the eBook or Video being made available to you, i.e. during download then you should contact Customer Relations Team within 14 days of purchase on customercare@packt.com who will be able to resolve this issue for you.
  3. You will have a choice of replacement or refund of the problem items.(damaged, defective or incorrect)
  4. Once Customer Care Team confirms that you will be refunded, you should receive the refund within 10 to 12 working days.
  5. If you are only requesting a refund of one book from a multiple order, then we will refund you the appropriate single item.
  6. Where the items were shipped under a free shipping offer, there will be no shipping costs to refund.

On the off chance your printed book arrives damaged, with book material defect, contact our Customer Relation Team on customercare@packt.com within 14 days of receipt of the book with appropriate evidence of damage and we will work with you to secure a replacement copy, if necessary. Please note that each printed book you order from us is individually made by Packt's professional book-printing partner which is on a print-on-demand basis.

What tax is charged? Chevron down icon Chevron up icon

Currently, no tax is charged on the purchase of any print book (subject to change based on the laws and regulations). A localized VAT fee is charged only to our European and UK customers on eBooks, Video and subscriptions that they buy. GST is charged to Indian customers for eBooks and video purchases.

What payment methods can I use? Chevron down icon Chevron up icon

You can pay with the following card types:

  1. Visa Debit
  2. Visa Credit
  3. MasterCard
  4. PayPal
What is the delivery time and cost of print books? Chevron down icon Chevron up icon

Shipping Details

USA:

'

Economy: Delivery to most addresses in the US within 10-15 business days

Premium: Trackable Delivery to most addresses in the US within 3-8 business days

UK:

Economy: Delivery to most addresses in the U.K. within 7-9 business days.
Shipments are not trackable

Premium: Trackable delivery to most addresses in the U.K. within 3-4 business days!
Add one extra business day for deliveries to Northern Ireland and Scottish Highlands and islands

EU:

Premium: Trackable delivery to most EU destinations within 4-9 business days.

Australia:

Economy: Can deliver to P. O. Boxes and private residences.
Trackable service with delivery to addresses in Australia only.
Delivery time ranges from 7-9 business days for VIC and 8-10 business days for Interstate metro
Delivery time is up to 15 business days for remote areas of WA, NT & QLD.

Premium: Delivery to addresses in Australia only
Trackable delivery to most P. O. Boxes and private residences in Australia within 4-5 days based on the distance to a destination following dispatch.

India:

Premium: Delivery to most Indian addresses within 5-6 business days

Rest of the World:

Premium: Countries in the American continent: Trackable delivery to most countries within 4-7 business days

Asia:

Premium: Delivery to most Asian addresses within 5-9 business days

Disclaimer:
All orders received before 5 PM U.K time would start printing from the next business day. So the estimated delivery times start from the next day as well. Orders received after 5 PM U.K time (in our internal systems) on a business day or anytime on the weekend will begin printing the second to next business day. For example, an order placed at 11 AM today will begin printing tomorrow, whereas an order placed at 9 PM tonight will begin printing the day after tomorrow.


Unfortunately, due to several restrictions, we are unable to ship to the following countries:

  1. Afghanistan
  2. American Samoa
  3. Belarus
  4. Brunei Darussalam
  5. Central African Republic
  6. The Democratic Republic of Congo
  7. Eritrea
  8. Guinea-bissau
  9. Iran
  10. Lebanon
  11. Libiya Arab Jamahriya
  12. Somalia
  13. Sudan
  14. Russian Federation
  15. Syrian Arab Republic
  16. Ukraine
  17. Venezuela