You are previewing SQL Server 2012 with PowerShell V3 Cookbook.
O'Reilly logo
SQL Server 2012 with PowerShell V3 Cookbook

Book Description

This versatile book is a great work companion if you’re a SQL Server database professional who wants to exploit the potential of PowerShell. Dip into the recipes or treat it like a training course – the choice is yours.

  • 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

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

    Table of Contents

    1. SQL Server 2012 with PowerShell V3 Cookbook
      1. Table of Contents
      2. SQL Server 2012 with PowerShell V3 Cookbook
      3. Credits
      4. About the Author
      5. Acknowledgement
      6. About the Reviewers
      7. www.PacktPub.com
        1. Support files, eBooks, discount offers and more
          1. Why Subscribe?
          2. Free Access for Packt account holders
          3. Instant Updates on New Packt Books
      8. Preface
        1. What this book covers
        2. What you need for this book
        3. Who this book is for
        4. Conventions
        5. Reader feedback
        6. Customer support
          1. Downloading the example code
          2. Errata
          3. Piracy
          4. Questions
      9. 1. Getting Started with SQL Server and PowerShell
        1. Introduction
          1. Notable PowerShell V3 features
        2. Before you start: Working with SQL Server and PowerShell
        3. Working with the sample code
          1. How to do it...
          2. See also
        4. Exploring the SQL Server PowerShell hierarchy
          1. Getting ready
          2. How to do it...
          3. How it works...
        5. Installing SMO
          1. Getting ready
          2. How to do it...
          3. There's more...
        6. Loading SMO assemblies
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        7. Discovering SQL-related cmdlets and modules
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. More information on running PowerShell scripts
            2. More information on mixed assembly error
        8. Creating a SQL Server instance object
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        9. Exploring SMO server objects
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
      10. 2. SQL Server and PowerShell Basic Tasks
        1. Introduction
          1. Development environment
          2. Administrator
          3. PowerShell ISE
          4. Running scripts
        2. Listing SQL Server instances
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        3. Discovering SQL Server services
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        4. Starting/stopping SQL Server services
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        5. Listing SQL Server configuration settings
          1. How to do it...
          2. How it works...
          3. See also
        6. Changing SQL Server instance configurations
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        7. Searching for database objects
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        8. Creating a database
          1. Getting ready
          2. How to do it...
          3. How it works...
        9. Altering database properties
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        10. Dropping a database
          1. Getting ready
          2. How to do it...
          3. How it works...
        11. Changing a database owner
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        12. Creating a table
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        13. Creating a view
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        14. Creating a stored procedure
          1. Getting ready
          2. How to do it...
          3. How it works...
        15. Creating a trigger
          1. Getting ready
          2. How to do it...
          3. How it works...
        16. Creating an index
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        17. Executing a query / SQL script
          1. Getting ready
          2. How to do it...
          3. How it works...
        18. Performing bulk export using Invoke-Sqlcmd
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        19. Performing bulk export using bcp
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        20. Performing bulk import using BULK INSERT
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        21. Performing bulk import using bcp
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
      11. 3. Basic Administration
        1. Introduction
        2. Creating a SQL Server instance inventory
          1. How to do it...
          2. How it works...
          3. There's more...
          4. See also
        3. Creating a SQL Server database inventory
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        4. Listing installed hotfixes and service packs
          1. How to do it...
          2. How it works...
          3. There's more...
          4. See also
        5. Listing running/blocking processes
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        6. Killing a blocking process
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        7. Checking disk space usage
          1. How to do it...
          2. How it works...
          3. See also
        8. Setting up WMI Server event alerts
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        9. Detaching a database
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        10. Attaching a database
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        11. Copying a database
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        12. Executing a SQL query to multiple servers
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        13. Creating a filegroup
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        14. Adding secondary data files to a filegroup
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        15. Moving an index to a different filegroup
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        16. Checking index fragmentation
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        17. Reorganizing/rebuilding an index
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        18. Running DBCC commands
          1. How to do it...
          2. How it works...
        19. Setting up Database Mail
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        20. Listing SQL Server jobs
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        21. Adding a SQL Server operator
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        22. Creating a SQL Server job
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        23. Adding a SQL Server event alert
          1. How to do it...
          2. How it works...
          3. There's more...
          4. See also
        24. Running a SQL Server job
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        25. Scheduling a SQL Server job
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
      12. 4. Security
        1. Introduction
        2. Listing SQL Server service accounts
          1. How to do it...
          2. How it works...
          3. See also
        3. Changing SQL Server service account
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        4. Listing authentication modes
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        5. Changing authentication mode
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. More on legacy LoginMode values
          5. See also
        6. Listing SQL Server log errors
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        7. Listing failed login attempts
          1. How to do it...
          2. How it works...
          3. See also
        8. Listing logins, users, and database mappings
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        9. Listing login/user roles and permissions
          1. How to do it...
          2. How it works...
          3. See also
        10. Creating a login
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        11. Assigning permissions and roles to a login
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        12. Creating a database user
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        13. Assigning permissions to a database user
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        14. Creating a database role
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        15. Fixing orphaned users
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        16. Creating a credential
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        17. Creating a proxy
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
      13. 5. Advanced Administration
        1. Introduction
        2. Listing facets and facet properties
          1. How to do it...
          2. How it works...
          3. See also
        3. Listing policies
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        4. Exporting a policy
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        5. Importing a policy
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        6. Creating a condition
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        7. Creating a policy
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        8. Evaluating a policy
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        9. Enabling/disabling change tracking
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        10. Running and saving a profiler trace event
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        11. Extracting the contents of a trace file
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        12. Creating a database master key
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        13. Creating a certificate
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        14. Creating symmetric and asymmetric keys
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        15. Setting up Transparent Data Encryption (TDE)
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
      14. 6. Backup and Restore
        1. Introduction
        2. Changing database recovery model
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        3. Listing backup history
          1. How to do it...
          2. How it works...
          3. See also
        4. Creating a backup device
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        5. Listing backup header and file list information
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        6. Creating a full backup
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. More about Backup and PercentCompleteEventHandler
          5. See also
        7. Creating a backup on mirrored media sets
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        8. Creating a differential backup
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        9. Creating a transaction log backup
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        10. Creating a filegroup backup
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        11. Restoring a database to a point in time
          1. Getting ready
          2. How to do it...
          3. How it works...
            1. Step 1 – Gather your backup files
            2. Step 2 – Restore the last good full backup, with NORECOVERY
            3. Step 3 – Restore the last good differential backup taken after the full backup you just restored, with NORECOVERY
            4. Step 4 – Restore the transaction logs taken after your differential backup
          4. See also
        12. Performing an online piecemeal restore
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
      15. 7. SQL Server Development
        1. Introduction
        2. Inserting XML into SQL Server
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        3. Extracting XML from SQL Server
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        4. Creating an RSS feed from SQL Server content
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        5. Applying XSL to an RSS feed
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        6. Storing binary data into SQL Server
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        7. Extracting binary data from SQL Server
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        8. Creating a new assembly
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        9. Listing user-defined assemblies
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        10. Extracting user-defined assemblies
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
      16. 8. Business Intelligence
        1. Introduction
        2. Listing items in your SSRS Report Server
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        3. Listing SSRS report properties
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        4. Using ReportViewer to view your SSRS report
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        5. Downloading an SSRS report in Excel and PDF
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        6. Creating an SSRS folder
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        7. Creating an SSRS data source
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        8. Changing an SSRS report's data source reference
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        9. Uploading an SSRS report to Report Manager
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        10. Downloading all SSRS report RDL files
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        11. Adding a user with a role to an SSRS report
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        12. Creating folders in an SSIS package store and MSDB
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        13. Deploying an SSIS package to the package store
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        14. Executing an SSIS package stored in the package store or File System
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        15. Downloading an SSIS package to a file
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        16. Creating an SSISDB catalog
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        17. Creating an SSISDB folder
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        18. Deploying an ISPAC file to SSISDB
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        19. Executing an SSIS package stored in SSISDB
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        20. Listing SSAS cmdlets
          1. How to do it...
          2. How it works...
          3. See also
        21. Listing SSAS instance properties
          1. How to do it...
          2. How it works...
          3. See also
        22. Backing up an SSAS database
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        23. Restoring an SSAS database
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        24. Processing an SSAS cube
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
      17. 9. Helpful PowerShell Snippets
        1. Introduction
        2. Documenting PowerShell script for Get-Help
          1. How to do it...
          2. How it works...
          3. There's more...
        3. Getting a timestamp
          1. How to do it...
          2. How it works...
          3. There's more...
        4. Getting additional error messages
          1. How to do it...
          2. How it works...
        5. Listing processes
          1. How to do it...
          2. How it works...
          3. There's more...
          4. See also
        6. Getting aliases
          1. How to do it...
          2. How it works...
          3. There's more...
        7. Exporting to CSV and XML
          1. How to do it...
          2. How it works...
          3. There's more...
        8. Using Invoke-Expression
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        9. Testing regular expressions
          1. How to do it...
          2. How it works...
          3. There's more...
        10. Managing folders
          1. How to do it...
          2. How it works...
          3. There's more...
          4. See also
        11. Manipulating files
          1. How to do it...
          2. How it works...
          3. There's more...
          4. See also
        12. Searching for files
          1. How to do it...
          2. How it works...
          3. There's more...
          4. See also
        13. Reading an event log
          1. How to do it...
          2. How it works...
          3. There's more...
        14. Sending e-mail
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        15. Embedding C# code
          1. How to do it...
          2. How it works...
          3. There's more...
        16. Creating an HTML report
          1. How to do it...
          2. How it works...
          3. There's more...
        17. Parsing XML
          1. Getting ready
          2. How to do it...
          3. How it works...
        18. Extracting data from a web service
          1. How to do it...
          2. How it works...
          3. There's more...
        19. Using PowerShell Remoting
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
      18. A. SQL Server and PowerShell CheatSheet
        1. Learning PowerShell
        2. PowerShell V2 versus V3 Where-Object syntax
        3. Changing execution policy
          1. Execution policies
        4. Running a script
        5. Common aliases
        6. Displaying output
        7. Special characters
        8. Special variables
        9. Common operators
        10. Common date-time format strings
          1. Comments
        11. Here-string
        12. Common regex characters and patterns
        13. Arrays and hash tables
        14. Arrays and loops
        15. Logic
        16. Functions
        17. Common Cmdlets
        18. Import SQLPS module
        19. Add SQL Server Snapins
        20. Add SQL Server Assemblies
        21. Getting credentials
        22. Running and blocking SQL Server processes
        23. Read file into an array
        24. SQL Server-Specific Cmdlets
        25. Invoke-SqlCmd
        26. Create SMO Server Object
        27. Create SSRS Proxy Object
        28. Create SSIS Object (SQL Server 2005/2008/2008R2)
        29. Create an SSIS Object (SQL Server 2012)
        30. Create SSAS Object
      19. B. PowerShell Primer
        1. Introduction
        2. What is PowerShell, and why learn another language
        3. Setting up the Environment
        4. Running PowerShell scripts
          1. Through shell or through ISE
            1. Execution policy
        5. Basics—points to remember
          1. Cmdlets
          2. Learning PowerShell
            1. Get-Command
            2. Get-Help
            3. Get-Member
          3. Starter notes
            1. PowerShell is object oriented, and works with .NET
            2. Cmdlets may have aliases or you can create one
            3. You can chain commands
            4. Filter left, format right
            5. Package and reuse
          4. Common Cmdlets
        6. Scripting syntax
          1. Statement terminators
          2. Escape and line continuation
          3. Variables
            1. Here-string
            2. String interpolation
          4. Operators
          5. Displaying messages
          6. Comments
          7. Special variables
          8. Conditions
          9. Regular Expressions
          10. Arrays
          11. Hashes
          12. Loop
          13. Error Handling
        7. Converting script into functions
        8. More about PowerShell
      20. C. Resources
        1. Resources
          1. PowerShell Books
            1. PowerShell V3
            2. PowerShell V2
            3. PowerShell V2 Free E-books
          2. PowerShell Blogs and Sites
          3. PowerShell Bloggers
          4. SQL Server and PowerShell Bloggers
          5. PowerShell Webcasts and Podcasts
          6. PowerShell Tools
          7. SQLPSX
          8. PSCX
      21. D. Creating a SQL Server VM
        1. Introduction
        2. Terminology
        3. Downloading software
        4. VM details and accounts
        5. Creating an empty virtual machine
        6. Installing Windows Server 2008 R2 as Guest OS
        7. Installing VMWare tools
        8. Configuring a domain controller
        9. Creating domain accounts
        10. Installing SQL Server 2012 on a VM
        11. Installing sample databases
        12. Installing PowerShell V3
      22. Index