SQL Server 2012 with PowerShell V3 Cookbook

SQL Server 2012 with PowerShell V3 Cookbook

By : Donabel Santos

Buy this Book

SQL Server 2012 with PowerShell V3 Cookbook

By: Donabel Santos

Buy this Book

Overview of this book

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.

SQL Server 2012 with PowerShell V3 Cookbook

Credits

About the Author

Acknowledgement

About the Reviewers

www.PacktPub.com

Preface

Free Chapter

Getting Started with SQL Server and PowerShell

Introduction

Before you start: Working with SQL Server and PowerShell

Working with the sample code

Exploring the SQL Server PowerShell hierarchy

Installing SMO

Loading SMO assemblies

Discovering SQL-related cmdlets and modules

Creating a SQL Server instance object

Exploring SMO server objects

SQL Server and PowerShell Basic Tasks

Introduction

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

Basic Administration

Introduction

Creating a SQL Server instance inventory

Creating a SQL Server database inventory

Listing installed hotfixes and service packs

Listing running/blocking processes

Killing a blocking process

Checking disk space usage

Setting up WMI Server event alerts

Detaching a database

Attaching a database

Copying a database

Executing a SQL query to multiple servers

Creating a filegroup

Adding secondary data files to a filegroup

Moving an index to a different filegroup

Checking index fragmentation

Reorganizing/rebuilding an index

Running DBCC commands

Setting up Database Mail

Listing SQL Server jobs

Adding a SQL Server operator

Creating a SQL Server job

Adding a SQL Server event alert

Running a SQL Server job

Scheduling a SQL Server job

Security

Introduction

Listing SQL Server service accounts

Changing SQL Server service account

Listing authentication modes

Changing authentication mode

Listing SQL Server log errors

Listing failed login attempts

Listing logins, users, and database mappings

Listing login/user roles and permissions

Creating a login

Assigning permissions and roles to a login

Creating a database user

Assigning permissions to a database user

Creating a database role

Fixing orphaned users

Creating a credential

Creating a proxy

Advanced Administration

Introduction

Listing facets and facet properties

Enabling/disabling change tracking

Running and saving a profiler trace event

Extracting the contents of a trace file

Creating a database master key

Creating a certificate

Creating symmetric and asymmetric keys

Setting up Transparent Data Encryption (TDE)

Backup and Restore

Introduction

Changing database recovery model

Listing backup history

Creating a backup device

Listing backup header and file list information

Creating a full backup

Creating a backup on mirrored media sets

Creating a differential backup

Creating a transaction log backup

Creating a filegroup backup

Restoring a database to a point in time

Performing an online piecemeal restore

SQL Server Development

Introduction

Inserting XML into SQL Server

Extracting XML from SQL Server

Creating an RSS feed from SQL Server content

Applying XSL to an RSS feed

Storing binary data into SQL Server

Extracting binary data from SQL Server

Creating a new assembly

Listing user-defined assemblies

Extracting user-defined assemblies

Business Intelligence

Introduction

Listing items in your SSRS Report Server

Listing SSRS report properties

Using ReportViewer to view your SSRS report

Downloading an SSRS report in Excel and PDF

Creating an SSRS folder

Creating an SSRS data source

Changing an SSRS report's data source reference

Uploading an SSRS report to Report Manager

Downloading all SSRS report RDL files

Adding a user with a role to an SSRS report

Creating folders in an SSIS package store and MSDB

Deploying an SSIS package to the package store

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

Downloading an SSIS package to a file

Creating an SSISDB catalog

Creating an SSISDB folder

Deploying an ISPAC file to SSISDB

Executing an SSIS package stored in SSISDB

Listing SSAS cmdlets

Listing SSAS instance properties

Backing up an SSAS database

Restoring an SSAS database

Processing an SSAS cube

Helpful PowerShell Snippets

Introduction

Documenting PowerShell script for Get-Help

Getting a timestamp

Getting additional error messages

Listing processes

Getting aliases

Exporting to CSV and XML

Using Invoke-Expression

Testing regular expressions

Creating an HTML report

Parsing XML

Extracting data from a web service

Using PowerShell Remoting

SQL Server and PowerShell CheatSheet

Learning PowerShell

PowerShell V2 versus V3 Where-Object syntax

Changing execution policy

Common date-time format strings

Here-string

Common regex characters and patterns

Arrays and hash tables

Logic

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

PowerShell Primer

Introduction

What is PowerShell, and why learn another language

Setting up the Environment

Running PowerShell scripts

Basics—points to remember

Scripting syntax

Converting script into functions

More about PowerShell

Resources

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

Customer Reviews

5 star

4 star

3 star

2 star

1 star

Working with the sample code

Samples in this book have been created and tested against SQL Server 2012 on Windows Server 2008 R2.

Note

To work with the sample code in this book using a similar VM setup that the book uses, see Appendix D, Creating a SQL Server VM.

How to do it...

If you want to use your current machine without creating a separate VM, as illustrated in Appendix D, Creating a SQL Server VM, follow these steps to prepare your machine:

Install SQL Server 2012 on your current operating system—either Windows 7 or Windows Server 2008 R2. See the list of upported operating systems for SQL Server 2012:
http://msdn.microsoft.com/en-us/library/ms143506.aspx
Install PowerShell V3.
- Although PowerShell V3 comes installed with Windows 8 and Windows Server 2012, at the time of writing this book these two operating systems are not listed in the list of operating systems that SQL Server 2012 supports.
- To install PowerShell V3 on Windows 7 SP1, Windows Server 2008 SP2, or Windows Server 2008 R2 SP1:
  Install Microsoft .NET Framework 4.0, if it's not already there.
  Download and install Windows Management Framework 3.0, which contains PowerShell V3. At the time of writing this book, the Release Candidate (RC) is available from:
  http://www.microsoft.com/en-us/download/details.aspx?id=29939
Enable PowerShell V3 ISE. We will be using the improved Integrated Scripting Environment in many samples in this book:
- Right-click on Windows PowerShell on your taskbar and choose Run as Administrator.
- Execute the following:
```
PS C:\Users\Administrator>Import-Module ServerManager PS C:\Users\Administrator>Add-WindowsFeature PowerShell-ISE
```
- Test to ensure you can see and launch the ISE:
```
PS C:\Users\Administrator> powershell_ise
```
  Alternatively you can go to Start | All Programs | Accessories | Windows PowerShell | Windows PowerShell ISE.
- Set execution policy to RemoteSigned by executing the following, on the code editor:
```
Set-ExecutionPolicy RemoteSigned
```
  Note
  If you want to run PowerShell V2 and V3 side by side, you can check out Jeffery Hicks' article, PowerShell 2 and 3, Side by Side:
  http://mcpmag.com/articles/2011/12/20/powershell-2-and-3-side-by-side.aspx

SQL Server 2012 with PowerShell V3 Cookbook

By : Donabel Santos

SQL Server 2012 with PowerShell V3 Cookbook

By: Donabel Santos

Overview of this book

Related Content you might be interested in

Current Title:

SQL Server 2012 with PowerShell V3 Cookbook

Working with the sample code

Note

How to do it...

Note

See also