SQL Server 2012 with PowerShell V3 Cookbook

SQL Server 2012 with PowerShell V3 Cookbook
eBook: $35.99
Formats: PDF, PacktLib, ePub and Mobi formats
save 15%!
Print + free eBook + free PacktLib access to the book: $95.98    Print cover: $59.99
save 37%!
Free Shipping!
UK, US, Europe and selected countries in Asia.
Also available on:
Table of Contents
Sample Chapters
  • Provides over a hundred practical recipes that utilize PowerShell to automate, integrate and simplify SQL Server tasks
  • Offers easy to follow, step-by-step guide to getting the most out of SQL Server and PowerShell
  • Covers numerous guidelines, tips, and explanations on how and when to use PowerShell cmdlets, WMI, SMO, .NET classes or other components
  • Builds a strong foundation that gets you comfortable using PowerShell with SQL Server--empowering you to create more complex scripts that you need in your day-to-day job

Book Details

Language : English
Paperback : 634 pages [ 235mm x 191mm ]
Release Date : October 2012
ISBN : 1849686467
ISBN 13 : 9781849686464
Author(s) : Donabel Santos
Topics and Technologies : All Books, Enterprise Products and Platforms, Microsoft Servers, Cookbooks, Enterprise, Microsoft, Microsoft SQL Server

Table of Contents

Chapter 1: Getting Started with SQL Server and PowerShell
Chapter 2: SQL Server and PowerShell Basic Tasks
Chapter 3: Basic Administration
Chapter 4: Security
Chapter 5: Advanced Administration
Chapter 6: Backup and Restore
Chapter 7: SQL Server Development
Chapter 8: Business Intelligence
Chapter 9: Helpful PowerShell Snippets
Appendix A: SQL Server and PowerShell CheatSheet
Appendix B: PowerShell Primer
Appendix C: Resources
Appendix D: Creating a SQL Server VM
  • Chapter 1: 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
    • Chapter 2: 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
      • Chapter 3: 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
        • Chapter 4: 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
          • Chapter 5: Advanced Administration
            • Introduction
            • Listing facets and facet properties
            • Listing policies
            • Exporting a policy
            • Importing a policy
            • Creating a condition
            • Creating a policy
            • Evaluating a policy
            • 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)
            • Chapter 6: 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
              • Chapter 7: 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
                • Chapter 8: 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
                  • Chapter 9: 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
                    • Managing folders
                    • Manipulating files
                    • Searching for files
                    • Reading an event log
                    • Sending e-mail
                    • Embedding C# code
                    • Creating an HTML report
                    • Parsing XML
                    • Extracting data from a web service
                    • Using PowerShell Remoting
                    • Appendix A: SQL Server and PowerShell CheatSheet
                      • Learning PowerShell
                      • PowerShell V2 versus V3 Where-Object syntax
                      • Changing execution policy
                      • Running a script
                      • Common aliases
                      • Displaying output
                      • Special characters
                      • Special variables
                      • Common operators
                      • Common date-time format strings
                      • Comment based help
                      • 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
                      • Appendix B: 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
                          • Appendix 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

                            Donabel Santos

                            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).
                            Sorry, we don't have any reviews for this title yet.

                            Code Downloads

                            Download the code and support files for this book.

                            Submit Errata

                            Please let us know if you have found any errors not listed on this list by completing our errata submission form. Our editors will check them and add them to this list. Thank you.


                            - 3 submitted: last submission 28 May 2013

                            Errata type: Typo | Page number: 342

                            You can use the script 6464 - Ch05 - 11 - Perform an Online PieceMeal Restore - Prep.ps1 to create your files, which is included in the downloadable files for this book.

                            should be

                            You can use the script 6464 - Ch06 - 11 - Perform an Online PieceMeal Restore - Prep.ps1 to create your files, which is included in the downloadable files for this book.

                            Errata type: Code | Page number: 339

                            To restore our database, we are simply going to use the Backup-SqlDatabase cmdlet.

                            should be

                            To restore our database, we are simply going to use the Restore-SqlDatabase cmdlet.

                            Errata type: Code | Page number: 334 and 335

                            The list of backups is displayed in descending order. The following code is retrieving the earliest full backup, not the latest.

                            #look for the last full backupfile
                            #you can be more specific and specify filename
                            $fullBackupFile =
                            Get-ChildItem $backupfilefolder -Filter "*Full*" |
                            Sort -Property LastWriteTime -Descending |
                            Select -Last 1

                            So, the correct code should be either: Select -First 1 or remove the -Descending attribute from the Sort statement.

                            Sample chapters

                            You can view our sample chapters and prefaces of this title on PacktLib or download sample chapters in PDF format.

                            Frequently bought together

                            SQL Server 2012 with PowerShell V3 Cookbook +    Oracle JRockit: The Definitive Guide =
                            50% Off
                            the second eBook
                            Price for both: $53.70

                            Buy both these recommended eBooks together and get 50% off the cheapest eBook.

                            What you will learn from this book

                            • Create an inventory of database properties and server configuration settings
                            • Backup and restore databases
                            • Execute queries to multiple servers
                            • Maintain permissions and security for users
                            • Import and export XML into SQL Server
                            • Extract CLR assemblies and BLOB objects from the database
                            • Explore database objects
                            • Manage and deploy SSIS packages and SSRS reports
                            • Manage and monitor running SQL Server services and accounts
                            • Parse and display the contents of trace files
                            • Create SQL Server jobs, alerts and operators
                            • Find blocking processes that are hampering your database performance


                            In Detail

                            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" is an example-focused book that provides step-by-step instructions on how to accomplish specific SQL Server tasks using PowerShell. Each recipe is followed by an analysis of the steps or design decisions taken, and additional information about the task at hand. Working scripts are provided for all examples so that you can dive in right away. You can read this book sequentially by chapter, or you can pick and choose which topics you need right away.

                            Who this book is for

                            This book is written for the SQL Server database professional (DBA, developer, BI developer) who wants to use PowerShell to automate, integrate, and simplify database tasks. A little bit of scripting background is helpful, but not necessary.

                            Code Download and Errata
                            Packt Anytime, Anywhere
                            Register Books
                            Print Upgrades
                            eBook Downloads
                            Video Support
                            Contact Us
                            Awards Voting Nominations Previous Winners
                            Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
                            Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software