SQL Server 2012 with PowerShell V3 Cookbook

634 Pages·2012·66.795 MB·English

by  Santos Donabel

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
professional expertise distilled
P U B L I S H I N G
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])
http://avaxhome.ws/blogs/igor_lv
Credits
Author Project Coordinator
Donabel Santos Yashodhan Dere
Reviewers Proofreader
Edwin Sarmiento Chris Smith
Laerte Poltronieri Junior
Indexer
Tejal R. Soni
Acquisition Editor
Rukhsana Khambatta
Production Coordinator
Manu Joseph
Lead Technical Editor
Azharuddin Sheikh
Cover Work
Manu Joseph
Technical Editors
Charmaine Pereira
Sharvari Baet
Jalasha D'costa
Copy Editors
Alfida Paiva
Brandt D'Mello
Insiya Morbiwala
Aditya Nair
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