SQL Server 2012 with PowerShell V3 Cookbook电子书

SQL Server 2012 with PowerShell V3 Cookbook

Table of Contents

SQL Server 2012 with PowerShell V3 Cookbook

Credits

About the Author

Acknowledgement

About the Reviewers

www.PacktPub.com

Support files, eBooks, discount offers and more

Why Subscribe?

Free Access for Packt account holders

Instant Updates on New Packt Books

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Downloading the example code

Errata

Piracy

Questions

1. Getting Started with SQL Server and PowerShell

Introduction

Notable PowerShell V3 features

Before you start: Working with SQL Server and PowerShell

Working with the sample code

How to do it...

See also

Exploring the SQL Server PowerShell hierarchy

Getting ready

How to do it...

How it works...

Installing SMO

Getting ready

How to do it...

There's more...

Loading SMO assemblies

Getting ready

How to do it...

How it works...

There's more...

See also

Discovering SQL-related cmdlets and modules

Getting ready

How to do it...

How it works...

There's more...

More information on running PowerShell scripts

More information on mixed assembly error

Creating a SQL Server instance object

Getting ready

How to do it...

How it works...

See also

Exploring SMO server objects

Getting ready

How to do it...

How it works...

See also

2. SQL Server and PowerShell Basic Tasks

Introduction

Development environment

Administrator

PowerShell ISE

Running scripts

Listing SQL Server instances

Getting ready

How to do it...

How it works...

There's more...

Discovering SQL Server services

Getting ready

How to do it...

How it works...

There's more...

See also

Starting/stopping SQL Server services

Getting ready

How to do it...

How it works...

There's more...

See also

Listing SQL Server configuration settings

How to do it...

How it works...

See also

Changing SQL Server instance configurations

Getting ready

How to do it...

How it works...

There's more...

See also

Searching for database objects

Getting ready

How to do it...

How it works...

There's more...

See also

Creating a database

Getting ready

How to do it...

How it works...

Altering database properties

Getting ready

How to do it...

How it works...

See also

Dropping a database

Getting ready

How to do it...

How it works...

Changing a database owner

Getting ready

How to do it...

How it works...

See also

Creating a table

Getting ready

How to do it...

How it works...

See also

Creating a view

Getting ready

How to do it...

How it works...

There's more...

Creating a stored procedure

Getting ready

How to do it...

How it works...

Creating a trigger

Getting ready

How to do it...

How it works...

Creating an index

Getting ready

How to do it...

How it works...

There's more...

See also

Executing a query / SQL script

Getting ready

How to do it...

How it works...

Performing bulk export using Invoke-Sqlcmd

Getting ready

How to do it...

How it works...

See also

Performing bulk export using bcp

Getting ready

How to do it...

How it works...

See also

Performing bulk import using BULK INSERT

Getting ready

How to do it...

How it works...

See also

Performing bulk import using bcp

Getting ready

How to do it...

How it works...

See also

3. Basic Administration

Introduction

Creating a SQL Server instance inventory

How to do it...

How it works...

There's more...

See also

Creating a SQL Server database inventory

Getting ready

How to do it...

How it works...

See also

Listing installed hotfixes and service packs

How to do it...

How it works...

There's more...

See also

Listing running/blocking processes

Getting ready

How to do it...

How it works...

See also

Killing a blocking process

Getting ready

How to do it...

How it works...

There's more...

See also

Checking disk space usage

How to do it...

How it works...

See also

Setting up WMI Server event alerts

Getting ready

How to do it...

How it works...

There's more...

Detaching a database

Getting ready

How to do it...

How it works...

There's more...

See also

Attaching a database

Getting ready

How to do it...

How it works...

See also

Copying a database

Getting ready

How to do it...

How it works...

See also

Executing a SQL query to multiple servers

Getting ready

How to do it...

How it works...

See also

Creating a filegroup

Getting ready

How to do it...

How it works...

See also

Adding secondary data files to a filegroup

Getting ready

How to do it...

How it works...

See also

Moving an index to a different filegroup

Getting ready

How to do it...

How it works...

There's more...

See also

Checking index fragmentation

Getting ready

How to do it...

How it works...

See also

Reorganizing/rebuilding an index

Getting ready

How to do it...

How it works...

See also

Running DBCC commands

How to do it...

How it works...

Setting up Database Mail

Getting ready

How to do it...

How it works...

There's more...

Listing SQL Server jobs

Getting ready

How to do it...

How it works...

See also

Adding a SQL Server operator

Getting ready

How to do it...

How it works...

See also

Creating a SQL Server job

Getting ready

How to do it...

How it works...

See also

Adding a SQL Server event alert

How to do it...

How it works...

There's more...

See also

Running a SQL Server job

Getting ready

How to do it...

How it works...

See also

Scheduling a SQL Server job

Getting ready

How to do it...

How it works...

There's more...

See also

4. Security

Introduction

Listing SQL Server service accounts

How to do it...

How it works...

See also

Changing SQL Server service account

Getting ready

How to do it...

How it works...

See also

Listing authentication modes

Getting ready

How to do it...

How it works...

See also

Changing authentication mode

Getting ready

How to do it...

How it works...

There's more...

More on legacy LoginMode values

See also

Listing SQL Server log errors

Getting ready

How to do it...

How it works...

See also

Listing failed login attempts

How to do it...

How it works...

See also

Listing logins, users, and database mappings

Getting ready

How to do it...

How it works...

There's more...

See also

Listing login/user roles and permissions

How to do it...

How it works...

See also

Creating a login

Getting ready

How to do it...

How it works...

See also

Assigning permissions and roles to a login

Getting ready

How to do it...

How it works...

See also

Creating a database user

Getting ready

How to do it...

How it works...

See also

Assigning permissions to a database user

Getting ready

How to do it...

How it works...

See also

Creating a database role

Getting ready

How to do it...

How it works...

See also

Fixing orphaned users

Getting ready

How to do it...

How it works...

There's more...

See also

Creating a credential

Getting ready

How to do it...

How it works...

See also

Creating a proxy

Getting ready

How to do it...

How it works...

There's more...

See also

5. Advanced Administration

Introduction

Listing facets and facet properties

How to do it...

How it works...

See also

Listing policies

Getting ready

How to do it...

How it works...

See also

Exporting a policy

Getting ready

How to do it...

How it works...

There's more...

See also

Importing a policy

Getting ready

How to do it...

How it works...

There's more...

See also

Creating a condition

Getting ready

How to do it...

How it works...

See also

Creating a policy

Getting ready

How to do it...

How it works...

See also

Evaluating a policy

Getting ready

How to do it...

How it works...

There's more...

See also

Enabling/disabling change tracking

Getting ready

How to do it...

How it works...

There's more...

See also

Running and saving a profiler trace event

Getting ready

How to do it...

How it works...

See also

Extracting the contents of a trace file

Getting ready

How to do it...

How it works...

See also

Creating a database master key

Getting ready

How to do it...

How it works...

See also

Creating a certificate

Getting ready

How to do it...

How it works...

There's more...

See also

Creating symmetric and asymmetric keys

Getting ready

How to do it...

How it works...

There's more...

See also

Setting up Transparent Data Encryption (TDE)

Getting ready

How to do it...

How it works...

There's more...

See also

6. Backup and Restore

Introduction

Changing database recovery model

Getting ready

How to do it...

How it works...

There's more...

See also

Listing backup history

How to do it...

How it works...

See also

Creating a backup device

Getting ready

How to do it...

How it works...

See also

Listing backup header and file list information

Getting ready

How to do it...

How it works...

See also

Creating a full backup

Getting ready

How to do it...

How it works...

There's more...

More about Backup and PercentCompleteEventHandler

See also

Creating a backup on mirrored media sets

Getting ready

How to do it...

How it works...

See also

Creating a differential backup

Getting ready

How to do it...

How it works...

There's more...

See also

Creating a transaction log backup

Getting ready

How to do it...

How it works...

There's more...

See also

Creating a filegroup backup

Getting ready

How to do it...

How it works...

See also

Restoring a database to a point in time

Getting ready

How to do it...

How it works...

Step 1 – Gather your backup files

Step 2 – Restore the last good full backup, with NORECOVERY

Step 3 – Restore the last good differential backup taken after the full backup you just restored, with NORECOVERY

Step 4 – Restore the transaction logs taken after your differential backup

See also

Performing an online piecemeal restore

Getting ready

How to do it...

How it works...

See also

7. SQL Server Development

Introduction

Inserting XML into SQL Server

Getting ready

How to do it...

How it works...

See also

Extracting XML from SQL Server

Getting ready

How to do it...

How it works...

See also

Creating an RSS feed from SQL Server content

Getting ready

How to do it...

How it works...

There's more...

See also

Applying XSL to an RSS feed

Getting ready

How to do it...

How it works...

See also

Storing binary data into SQL Server

Getting ready

How to do it...

How it works...

There's more...

See also

Extracting binary data from SQL Server

Getting ready

How to do it...

How it works...

There's more...

See also

Creating a new assembly

Getting ready

How to do it...

How it works...

There's more...

See also

Listing user-defined assemblies

Getting ready

How to do it...

How it works...

There's more...

See also

Extracting user-defined assemblies

Getting ready

How to do it...

How it works...

See also

8. Business Intelligence

Introduction

Listing items in your SSRS Report Server

Getting ready

How to do it...

How it works...

See also

Listing SSRS report properties

Getting ready

How to do it...

How it works...

See also

Using ReportViewer to view your SSRS report

Getting ready

How to do it...

How it works...

See also

Downloading an SSRS report in Excel and PDF

Getting ready

How to do it...

How it works...

See also

Creating an SSRS folder

Getting ready

How to do it...

How it works...

See also

Creating an SSRS data source

Getting ready

How to do it...

How it works...

See also

Changing an SSRS report's data source reference

Getting ready

How to do it...

How it works...

See also

Uploading an SSRS report to Report Manager

Getting ready

How to do it...

How it works...

See also

Downloading all SSRS report RDL files

Getting ready

How to do it...

How it works...

See also

Adding a user with a role to an SSRS report

Getting ready

How to do it...

How it works...

See also

Creating folders in an SSIS package store and MSDB

Getting ready

How to do it...

How it works...

See also

Deploying an SSIS package to the package store

Getting ready

How to do it...

How it works...

See also

Executing an SSIS package stored in the package store or File System

Getting ready

How to do it...

How it works...

There's more...

See also

Downloading an SSIS package to a file

Getting ready

How to do it...

How it works...

See also

Creating an SSISDB catalog

Getting ready

How to do it...

How it works...

See also

Creating an SSISDB folder

Getting ready

How to do it...

How it works...

See also

Deploying an ISPAC file to SSISDB

Getting ready

How to do it...

How it works...

See also

Executing an SSIS package stored in SSISDB

Getting ready

How to do it...

How it works...

See also

Listing SSAS cmdlets

How to do it...

How it works...

See also

Listing SSAS instance properties

How to do it...

How it works...

See also

Backing up an SSAS database

Getting ready

How to do it...

How it works...

See also

Restoring an SSAS database

Getting ready

How to do it...

How it works...

See also

Processing an SSAS cube

Getting ready

How to do it...

How it works...

See also

9. Helpful PowerShell Snippets

Introduction

Documenting PowerShell script for Get-Help

How to do it...

How it works...

There's more...

Getting a timestamp

How to do it...

How it works...

There's more...

Getting additional error messages

How to do it...

How it works...

Listing processes

How to do it...

How it works...

There's more...

See also

Getting aliases

How to do it...

How it works...

There's more...

Exporting to CSV and XML

How to do it...

How it works...

There's more...

Using Invoke-Expression

Getting ready

How to do it...

How it works...

There's more...

Testing regular expressions

How to do it...

How it works...

There's more...

Managing folders

How to do it...

How it works...

There's more...

See also

Manipulating files

How to do it...

How it works...

There's more...

See also

Searching for files

How to do it...

How it works...

There's more...

See also

Reading an event log

How to do it...

How it works...

There's more...

Sending e-mail

Getting ready

How to do it...

How it works...

There's more...

Embedding C# code

How to do it...

How it works...

There's more...

Creating an HTML report

How to do it...

How it works...

There's more...

Parsing XML

Getting ready

How to do it...

How it works...

Extracting data from a web service

How to do it...

How it works...

There's more...

Using PowerShell Remoting

Getting ready

How to do it...

How it works...

There's more...

A. SQL Server and PowerShell CheatSheet

Learning PowerShell

PowerShell V2 versus V3 Where-Object syntax

Changing execution policy

Execution policies

Running a script

Common aliases

Displaying output

Special characters

Special variables

Common operators

Common date-time format strings

Comments

Here-string

Common regex characters and patterns

Arrays and hash tables

Arrays and loops

Logic

Functions

Common Cmdlets

Import SQLPS module

Add SQL Server Snapins

Add SQL Server Assemblies

Getting credentials

Running and blocking SQL Server processes

Read file into an array

SQL Server-Specific Cmdlets

Invoke-SqlCmd

Create SMO Server Object

Create SSRS Proxy Object

Create SSIS Object (SQL Server 2005/2008/2008R2)

Create an SSIS Object (SQL Server 2012)

Create SSAS Object

B. PowerShell Primer

Introduction

What is PowerShell, and why learn another language

Setting up the Environment

Running PowerShell scripts

Through shell or through ISE

Execution policy

Basics—points to remember

Cmdlets

Learning PowerShell

Get-Command

Get-Help

Get-Member

Starter notes

PowerShell is object oriented, and works with .NET

Cmdlets may have aliases or you can create one

You can chain commands

Filter left, format right

Package and reuse

Common Cmdlets

Scripting syntax

Statement terminators

Escape and line continuation

Variables

Here-string

String interpolation

Operators

Displaying messages

Comments

Special variables

Conditions

Regular Expressions

Arrays

Hashes

Loop

Error Handling

Converting script into functions

More about PowerShell

C. Resources

Resources

PowerShell Books

PowerShell V3

PowerShell V2

PowerShell V2 Free E-books

PowerShell Blogs and Sites

PowerShell Bloggers

SQL Server and PowerShell Bloggers

PowerShell Webcasts and Podcasts

PowerShell Tools

SQLPSX

PSCX

D. Creating a SQL Server VM

Introduction

Terminology

Downloading software

VM details and accounts

Creating an empty virtual machine

Installing Windows Server 2008 R2 as Guest OS

Installing VMWare tools

Configuring a domain controller

Creating domain accounts

Installing SQL Server 2012 on a VM

Installing sample databases

Installing PowerShell V3

Index