Szczegóły ebooka

SQL Server 2012 with PowerShell V3 Cookbook

SQL Server 2012 with PowerShell V3 Cookbook

Donabel Santos

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