O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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