varios livros sobre arduino e matematica, Notas de estudo de Informática

Baixe varios livros sobre arduino e matematica e outras Notas de estudo em PDF para Informática, somente na Docsity!

SQL Server 2012

with PowerShell V

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

P U B L I S H I N G

professional expertise distilled

BIRMINGHAM - MUMBAI

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

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.

Table of Contents

ii

iv

v

Preface

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 dives deeply into more administration topics such as backup and restore, credentials, policies, and 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.

What this book covers

Chapter 1: Getting Started with SQL Server and PowerShell

why you should consider learning PowerShell. It also introduces PowerShell V3 new features, and explains what needs to be in place when working with SQL Server 2012 and PowerShell.

Preface

2

Chapter 2: SQL Server and PowerShell Basic Tasks

of code that accomplish some basic SQL Server tasks using PowerShell. We 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 while working with SQL Server programmatically.

Chapter 3 , Basic Administration tackles more administrative tasks that can be accomplished using PowerShell, and provides recipes that can help automate a lot of repetitive tasks. Some recipes deal with instance and database properties; others provide ways of checking disk space, creating WMI alerts, setting up Database Mail, and creating and maintaining SQL Server Jobs.

Chapter 4 , Security provides snippets that simplify security monitoring, including how to check failed login attempts by parsing out event logs, or how to administer roles and permissions.

Chapter 5 , Advanced Administration shows how PowerShell can help you leverage features such as Policy Based Management (PBM) and encryption using PowerShell. This chapter also explores working with SQL Server Profiler trace files and events programmatically.

Chapter 6 , Backup and Restore looks into different ways of backing up and restoring SQL Server databases programmatically using PowerShell.

Chapter 7 , SQL Server Development provides snippets and guidance on how you can work with XML, XSL, binary data, and CLR assemblies with SQL Server and PowerShell.

Chapter 8 , Business Intelligence covers how PowerShell can help automate and manage any BI-related tasks—from rendering SQL Server Reporting Services (SSRS) reports, to deploying the new SQL Server Integration Services (SSIS) 2012 ISPAC files, to backing up and restoring SQL Server Analysis Services (SSAS) cubes.

Chapter 9 , Helpful PowerShell Snippets tackles a variety of recipes that are not SQL Server specific, but you may find them useful as you work with PowerShell. Recipes include snippets for creating files that use timestamps, analyzing event logs for recent system errors, and exporting a list of processes to CSV or XML.

Appendix A: SQL Server and PowerShell CheatSheet

commonly used terms and snippets when working with SQL Server and PowerShell.

Appendix B , PowerShell Primer offers a brief primer on PowerShell fundamentals.

Appendix C , Resources lists additional PowerShell and SQL Server books, blogs and links.

Appendix D , Creating a SQL Server VM provides a step-by-step tutorial on how to create and configure the virtual machine that was used for this book.

Preface

4

New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in the text like this: "clicking the Next button moves you to the next screen".

Warnings or important notes appear in a box like this.

Tips and tricks appear like this.

Reader feedback

Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.

To send us general feedback, simply send an e-mail to feedback@packtpub.com, and mention the book title via the subject of your message.

If there is a book that you need and would like to see us publish, please send us a note in the SUGGEST A TITLE form on www.packtpub.com or e-mail suggest@packtpub.com.

If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on www.packtpub.com/authors.

Customer support

Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.

Downloading the example code

You can download the example code files for all Packt books you have purchased from your account at http://www.PacktPub.com. If you purchased this book elsewhere, you can visit http://www.PacktPub.com/support and register to have the files e-mailed directly to you.

Preface

5

Errata

Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in the text or the code—we would be grateful if you would report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/support, selecting your book, clicking on the errata submission form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded on our website, or added to any list of existing errata, under the Errata section of that title. Any existing errata can be viewed by selecting your title from http://www.packtpub.com/support.

Piracy

Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works, in any form, on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.

Please contact us at copyright@packtpub.com with a link to the suspected pirated material.

We appreciate your help in protecting our authors, and our ability to bring you valuable content.

Questions You can contact us at questions@packtpub.com if you are having a problem with any aspect of the book, and we will do our best to address it.