SQL Server 2012 with PowerShell V3 Cookbook

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.

SQL Server 2012 with PowerShell V3 Cookbook

Cookbook
Donabel Santos

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.
$35.99
$59.99
RRP $35.99
RRP $59.99
eBook
Print + eBook
$12.99 p/month

Get Access

Get Unlimited Access to every Packt eBook and Video course

Enjoy full and instant access to over 3000 books and videos – you’ll find everything you need to stay ahead of the curve and make sure you can always get the job done.

Book Details

ISBN 139781849686464
Paperback634 pages

About This Book

  • 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

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.

Table of Contents

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

What You Will Learn

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

Authors

Table of Contents

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

Book Details

ISBN 139781849686464
Paperback634 pages
Read More