Tài liệu SQL Server 2012 with PowerShell V3 Cookbook docx

SQL Server 2012

with PowerShell V3

Cookbook

Increase your productivity as a DBA, developer, or IT Pro, by using PowerShell with SQL Server

to simplify database management and automate repetitive, mundane tasks.

Donabel Santos

PUBLISHING

professional expertise distilled

BIRMINGHAM - MUMBAI

SQL Server 2012 with PowerShell V3

Cookbook

Copyright © 2012 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or

transmitted in any form or by any means, without the prior written permission of the publisher,

except in the case of brief quotations embedded in critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy of the

information presented. However, the information contained in this book is sold without

warranty, either express or implied. Neither the author, nor Packt Publishing, and its dealers

and distributors will be held liable for any damages caused or alleged to be caused directly or

indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all of the companies

and products mentioned in this book by the appropriate use of capitals. However, Packt

Publishing cannot guarantee the accuracy of this information.

First published: October 2012

Production Reference: 1151012

Published by Packt Publishing Ltd.

Livery Place

35 Livery Street

Birmingham B3 2PB, UK.

ISBN 978-1-84968-646-4

www.packtpub.com

Cover Image by Artie Ng ([email protected])

Credits

Author

Donabel Santos

Reviewers

Edwin Sarmiento

Laerte Poltronieri Junior

Acquisition Editor

Rukhsana Khambatta

Lead Technical Editor

Azharuddin Sheikh

Technical Editors

Charmaine Pereira

Sharvari Baet

Jalasha D'costa

Copy Editors

Alfida Paiva

Brandt D'Mello

Insiya Morbiwala

Aditya Nair

Project Coordinator

Yashodhan Dere

Proofreader

Chris Smith

Indexer

Tejal R. Soni

Production Coordinator

Manu Joseph

Cover Work

Manu Joseph

About the Author

Donabel Santos is a SQL Server MVP and is the senior SQL Server Developer/DBA/

Trainer at QueryWorks Solutions, a consulting and training company in Vancouver, BC. She

has worked with SQL Server since version 2000 in numerous development, tuning, reporting,

and integration projects with ERPs, CRMs, SharePoint, and other custom applications. She

holds MCITP certifications for SQL Server 2005/2008, and an MCTS for SharePoint. She is a

Microsoft Certified Trainer (MCT), and is also the lead instructor for SQL Server Administration,

Development, and SSIS courses at British Columbia Institute of Technology (BCIT).

Donabel is a proud member of PASS (Professional Association of SQL Server), and a proud BCIT

alumna (CST diploma and degree). She blogs (www.sqlmusings.com), tweets (@sqlbelle),

speaks and presents (SQLSaturday, VANPASS, Vancouver TechFest, and so on), trains (BCIT,

QueryWorks Solutions), and writes (Packt, Idera, SSWUG, and so on).

Acknowledgement

Writing a book would not be possible without the unwavering support of family, friends,

colleagues, mentors, acquaintances, and an awesome community. This is my first book,

a dream come true, so please forgive me if I go overboard with my thanks.

To Eric, thank you… for finding me. Despite long days, sleepless nights, lengthy writing

marathons, one smile from you never fails to wipe away my tiredness. Thank you for always

supporting me, for believing in me, for helping me reach whichever dreams I dare to chase.

I look forward to our journey together—a lifelong of hopes, dreams, and happiness.

To Mama and Papa, I am the luckiest daughter to have you as my parents. Thank you for all

the sacrifices you made for me and my brothers. Words are not enough to express how much

we love you, and how grateful we will always be.

To JR and RR—you will always be my baby brothers, and I am so proud to be your big sis. To

Lisa, my dear sis-in-law, thank you for being part of our family. The whole family adores you.

To Veronica, thanks for keeping up with the Santos' quirks. You're cool, girl! Now that the book

is done, we can all play more Kinect, Acquire, and Ticket to Ride.

To my in laws—Mom Lisa, Dad Richard, Ama, Aunt Rose, Catherine, David, and Jayden—thank

you for always making me feel welcome, for never making me feel I am different from your

family. And to my unborn niece Kristina, auntie will teach you and Jayden SQL Server… one of

these years.

To Edwin Sarmiento and Laerte Junior—my utmost and sincerest thanks for all the advice

and constructive feedback. I have the highest respect for both of you. It is very humbling to

work with both of you, and I learned so much from all the corrections and suggestions. Thank

you for bearing with me through the revisions, despite your respective hectic schedules and

numerous other commitments. I am very grateful.

To Elsie Au, thank you for introducing me to databases. I cannot imagine doing anything else.

Thank you for the friendship all these years. To Kevin Cudihee, thank you for all the support all

these years, for letting me do two things that I love the most—teaching and SQL Server. To Anne

Marie Johnston and Alan Marchant, thank you for giving me fun work with databases. To my

students, thank you for learning, sharing, and growing with me.

To BCIT—my second home. To me, BCIT was my place of refuge. When I was at a low point in

my life, feeling down and out, and without direction (and afraid of computers!), BCIT provided

me a place to learn, grow, and dream again. Now as an instructor, I hope I can help give back

to students what BCIT gave me when I was one.

To the SQL community, the SQL family, and the SQL Server MVPs—I am so proud to be part

of this group. There are so many smart SQL rockstars that I admire (Brent Ozar, Glenn Berry,

Kevin Kline, Brian Knight, Grant Fritchey, Jorge Sergarra, Jeremiah Peschka, Jen Stirrup, and

so many others I would love to mention and thank), who are way up there, yet who are always

ready to help and inspire anyone who asks. "Community" for this group is not just lip service.

It's the SQL way of life. I have learned so much from this community, and I would not be

anywhere near where I am today if not for the selfless way this community shares and helps.

To the PowerShell community, thank you to the awesome authors, bloggers, and tweeps.

Your articles, blogs, and books have immensely helped folks like me to learn, understand,

and get excited about PowerShell.To Microsoft and the SQL Server and PowerShell respective

Product Teams —thanks for creating these two amazing products. It doubles the fun for SQL

geeks like me!

To the Packt team—Dhwani Dewater, Yashodhan Dere, Azharuddin Sheikh, Charmaine Pereira,

Sharvari Baet and the rest of the editors and technical reviewers—thank you for giving me the

chance to write this book and helping me as the book writing progressed. It is one of the most

humbling, but also one of the most rewarding experiences.

To numerous friends (Shereen Qumsieh, Matthew Carriere, Grace Dimaculangan, Ben Peach,

Yaroslav Pentsarskyy, Joe Xing, Min Zhu, Mary Mootatamby, Blake Wiggs, and many others), to

all of my mentors and students, acquaintances via twitter (such as @pinaldave, @dsfnet,

@StangSCT, @retracement, @NikoNeugebauer, @TimCost), and so many others who

have helped, inspired, and encouraged me along the way—thank you.

And most importantly, thank you Lord, for all the miracles and blessings in my life.

About the Reviewers

Edwin Sarmiento is a Microsoft SQL Server MVP from Ottawa, Canada specializing in

high availability, disaster recovery, and system infrastructures running on the Microsoft

server technology stack. He is very passionate about technology but has interests in music,

professional and organizational development, leadership, and management matters when

not working with databases. He lives up to his primary mission statement—To help people

and organizations grow and develop their full potential as God has planned for them.

He wants the whole world to know that the FILIPINO is a world-class citizen and brings

Jesus Christ to the world.

Laerte Poltronieri Junior started in the IT world early, at the age of 12. When 16, he was

developing software using Clipper Summer 85 and he used almost all versions. Then in 1998

he was introduced to SQL Server 6.5; since then it was love at first sight and marriage. In 2008,

he met PowerShell and as he is an aficionado for automated, smart, and flexible solutions in

SQL Server, from this marriage was born a son. And today they are a happy family.

Currently, he is writing a book for Manning Publications.

First of all, I would like to thank God. I have not always been a guy next

to him, but I'm learning to give back all the love and affection that he has

given me.

My family—my father, an unforgettable super-hero, my beloved mother

and grandma, and my dear sister and nephews.

Also, a special thanks to some exceptional professionals and friends who

are teaching and mentoring me from the beginning: Buck Woody, Chad

Miller, Shay Levy, and Ravikanth Chaganti.

And last but not the least, all the #sqlfamily , #powershell and Simple-Talk

friends, you guys simply rock. I owe you all the good things that happened

and are happening to me.

www.PacktPub.com

Support files, eBooks, discount offers and more

You might want to visit www.PacktPub.com for support files and downloads related to

your book.

Did you know that Packt offers eBook versions of every book published, with PDF and ePub

files available? You can upgrade to the eBook version at www.PacktPub.com and as a print

book customer, you are entitled to a discount on the eBook copy. Get in touch with us at

[email protected] for more details.

At www.PacktPub.com, you can also read a collection of free technical articles, sign up

for a range of free newsletters and receive exclusive discounts and offers on Packt books

and eBooks.

TM

http://PacktLib.PacktPub.com

Do you need instant solutions to your IT questions? PacktLib is Packt's online digital book

library. Here, you can access, read and search across Packt's entire library of books.

Why Subscribe?

f Fully searchable across every book published by Packt

f Copy and paste, print and bookmark content

f On demand and accessible via web browser

Free Access for Packt account holders

If you have an account with Packt at www.PacktPub.com, you can use this to access

PacktLib today and view nine entirely free books. Simply use your login credentials for

immediate access.

Instant Updates on New Packt Books

Get notified! Find out when new books are published by following @PacktEnterprise on

Twitter, or the Packt Enterprise Facebook page.

Table of Contents

Preface 1

Chapter 1: Getting Started with SQL Server and PowerShell 7

Introduction 7

Before you start: Working with SQL Server and PowerShell 10

Working with the sample code 12

Exploring the SQL Server PowerShell hierarchy 14

Installing SMO 18

Loading SMO assemblies 20

Discovering SQL-related cmdlets and modules 22

Creating a SQL Server instance object 29

Exploring SMO server objects 32

Chapter 2: SQL Server and PowerShell Basic Tasks 35

Introduction 36

Listing SQL Server instances 39

Discovering SQL Server services 43

Starting/stopping SQL Server services 45

Listing SQL Server configuration settings 51

Changing SQL Server instance configurations 55

Searching for database objects 60

Creating a database 67

Altering database properties 68

Dropping a database 72

Changing a database owner 73

Creating a table 75

Creating a view 81

Creating a stored procedure 85

Creating a trigger 90

Creating an index 95

ii

Table of Contents

Executing a query / SQL script 99

Performing bulk export using Invoke-Sqlcmd

100

Performing bulk export using bcp

102

Performing bulk import using BULK INSERT

105

Performing bulk import using bcp

110

Chapter 3: Basic Administration

115

Introduction

116

Creating a SQL Server instance inventory

116

Creating a SQL Server database inventory

120

Listing installed hotfixes and service packs

124

Listing running/blocking processes

128

Killing a blocking process

131

Checking disk space usage

133

Setting up WMI Server event alerts

136

Detaching a database

143

Attaching a database

145

Copying a database

149

Executing a SQL query to multiple servers

152

Creating a filegroup

153

Adding secondary data files to a filegroup

156

Moving an index to a different filegroup

158

Checking index fragmentation

162

Reorganizing/rebuilding an index

164

Running DBCC commands

167

Setting up Database Mail

168

Listing SQL Server jobs

178

Adding a SQL Server operator

181

Creating a SQL Server job

183

Adding a SQL Server event alert

187

Running a SQL Server job 190

Scheduling a SQL Server job 192

Chapter 4: Security 203

Introduction 203

Listing SQL Server service accounts 20

4

Changing SQL Server service account 206

Listing authentication modes

210

Changing authentication mode

211

Listing SQL Server log errors

215

Listing failed login attempts 220

Listing logins, users, and database mappings 222

iii

Table of Contents

Listing login/user roles and permissions 225

Creating a login 22

7

Assigning permissions and roles to a login 229

Creating a database user 232

Assigning permissions to a database user 234

Creating a database role 23

7

Fixing orphaned users

241

Creating a credential

244

Creating a proxy

246

Chapter 5: Advanced Administration 25

1

Introduction 252

Listing facets and facet properties 252

Listing policies 25

4

Exporting a policy 25

7

Importing a policy 26

1

Creating a condition 26

4

Creating a policy 268

Evaluating a policy

272

Enabling/disabling change tracking

275

Running and saving a profiler trace event

276

Extracting the contents of a trace file 28

4

Creating a database master key 289

Creating a certificate 29

1

Creating symmetric and asymmetric keys 293

Setting up Transparent Data Encryption (TDE) 299

Chapter 6: Backup and Restore 305

Introduction 305

Changing database recovery model 306

Listing backup history 309

Creating a backup device

310

Listing backup header and file list information

312

Creating a full backup

316

Creating a backup on mirrored media sets 32

1

Creating a differential backup 32

4

Creating a transaction log backup 32

7

Creating a filegroup backup 329

Restoring a database to a point in time 332

Performing an online piecemeal restore 342

iv

Table of Contents

Chapter 7: SQL Server Development 351

Introduction 351

Inserting XML into SQL Server 352

Extracting XML from SQL Server 355

Creating an RSS feed from SQL Server content 358

Applying XSL to an RSS feed 363

Storing binary data into SQL Server 366

Extracting binary data from SQL Server 370

Creating a new assembly 374

Listing user-defined assemblies 378

Extracting user-defined assemblies 379

Chapter 8: Business Intelligence 385

Introduction 386

Listing items in your SSRS Report Server 386

Listing SSRS report properties 388

Using ReportViewer to view your SSRS report 391

Downloading an SSRS report in Excel and PDF 396

Creating an SSRS folder 400

Creating an SSRS data source 404

Changing an SSRS report's data source reference 409

Uploading an SSRS report to Report Manager 412

Downloading all SSRS report RDL files 416

Adding a user with a role to an SSRS report 421

Creating folders in an SSIS package store and MSDB 425

Deploying an SSIS package to the package store 428

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

Downloading an SSIS package to a file 433

Creating an SSISDB catalog 435

Creating an SSISDB folder 439

Deploying an ISPAC file to SSISDB 441

Executing an SSIS package stored in SSISDB 444

Listing SSAS cmdlets 447

Listing SSAS instance properties 448

Backing up an SSAS database 450

Restoring an SSAS database 451

Processing an SSAS cube 452

Chapter 9: Helpful PowerShell Snippets 455

Introduction 456

Documenting PowerShell script for Get-Help 456

Getting a timestamp 459

v

Table of Contents

Getting additional error messages 461

Listing processes 462

Getting aliases 466

Exporting to CSV and XML 467

Using Invoke-Expression 468

Testing regular expressions 470

Managing folders 474

Manipulating files 476

Searching for files 478

Reading an event log 481

Sending e-mail 482

Embedding C# code 484

Creating an HTML report 486

Parsing XML 488

Extracting data from a web service 490

Using PowerShell Remoting 492

Appendix A: SQL Server and PowerShell CheatSheet 497

Learning PowerShell 497

PowerShell V2 versus V3 Where-Object syntax 498

Changing execution policy 498

Running a script 499

Common aliases 499

Displaying output 500

Special characters 500

Special variables 501

Common operators 502

Common date-time format strings 502

Comment based help 503

Here-string 504

Common regex characters and patterns 504

Arrays and hash tables 505

Arrays and loops 506

Logic 506

Functions 507

Common Cmdlets 508

Import SQLPS module 509

Add SQL Server Snapins 509

Add SQL Server Assemblies 509

Getting credentials 510

Running and blocking SQL Server processes 510

Read file into an array 510

vi

Table of Contents

SQL Server-Specific Cmdlets 510

Invoke-SqlCmd 512

Create SMO Server Object 512

Create SSRS Proxy Object 512

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

Create an SSIS Object (SQL Server 2012) 513

Create SSAS Object 513

Appendix B: PowerShell Primer 515

Introduction 515

What is PowerShell, and why learn another language 515

Setting up the Environment 516

Running PowerShell scripts 517

Basics—points to remember 520

Scripting syntax 527

Converting script into functions 539

More about PowerShell 542

Appendix C: Resources 543

Resources 543

Appendix D: Creating a SQL Server VM 549

Introduction 549

Terminology 550

Downloading software 551

VM details and accounts 552

Creating an empty virtual machine 553

Installing Windows Server 2008 R2 as

Guest OS 556

Installing VMWare tools 567

Configuring a domain controller 569

Creating domain accounts 577

Installing SQL Server 2012 on a VM 580

Installing sample databases 598

Installing PowerShell V3 598

Index 601