book

SQL Server 2014 with Powershell v5 Cookbook

Name: SQL Server 2014 with Powershell v5 Cookbook
Author: Donabel Santos
ISBN: 9781785283321

by Donabel Santos

December 2015

Intermediate to advanced

760 pages

14h 31m

English

Packt Publishing

Read now

Unlock full access

SQL Server 2014 with PowerShell v5 Cookbook
Table of Contents
SQL Server 2014 with PowerShell v5 Cookbook
Credits
About the Author
Acknowledgments
About the Reviewers
www.PacktPub.com
Support files, eBooks, discount offers, and moreWhy subscribe?Free access for Packt account holdersInstant updates on new Packt books
Preface
What this book covers
What you need for this book

Who this book is for
Sections
Getting readyHow to do it…How it works…There's more…See also
Conventions
Reader feedback
Customer support
Downloading the example codeDownloading the color images of this bookErrataPiracyQuestions
1. Getting Started with SQL Server and PowerShell
IntroductionWorking with SQL Server and PowerShellRunning as an administratorExecution PolicyRunning scriptsRunning different PowerShell versionsLine continuationPowerShell modules
Working with the sample code
How to do it...See also
Installing SQL Server using PowerShell
Getting readyHow to do it...How it works...There's more...
Installing SQL Server Management Objects
Getting readyHow to do it...There's more...
Loading SMO assemblies
Getting readyHow to do it...How it works...There's more...See also
Exploring the SQL Server PowerShell hierarchy
Getting readyHow to do it...How it works...
Discovering SQL-related cmdlets and modules
Getting readyHow to do it...How it works...There's more...
Creating a SQL Server Instance Object
Getting readyHow to do it...How it works...See also
Exploring SMO Server Objects
Getting readyHow to do it...How it works...See also
2. SQL Server and PowerShell Basic Tasks
Introduction
Listing SQL Server instances
Getting readyHow to do it...How it works...There's more...
Discovering SQL Server services
Getting readyHow to do it...How it works...There's more...See also
Starting/stopping SQL Server services
Getting readyHow to do it...How it works...There's more...See also
Listing SQL Server configuration settings
How to do it...How it works...There's more...
Changing SQL Server Instance configurations
Getting readyHow to do it...How it works...There's more...See also
Searching for database objects
Getting readyHow to do it...How it works...There's more...See also
Scripting SQL Server Stored Procedures
Getting readyHow to do it...How it works...There's more...
Creating a database
Getting readyHow to do it...How it works...
Altering database properties
Getting readyHow to do it...How it works...See also
Dropping a database
Getting readyHow to do it...How it works...
Changing database owner
Getting readyHow to do it...How it works...See also
Creating a table
Getting readyHow to do it...How it works...There's more...See also
Creating a view
Getting readyHow to do it...How it works...There's more...
Creating a stored procedure
Getting readyHow to do it...How it works...
Creating a trigger
Getting readyHow to do it...How it works...
Creating an index
Getting readyHow to do it...How it works...There's more...See also
Executing a query/SQL script
Getting readyHow to do it...How it works...
Performing bulk export using Invoke-SqlCmd
Getting readyHow to do it...How it works...See also
Performing bulk export using the bcp command-line utility
Getting readyHow to do it...There's more...See also
Performing bulk import using BULK INSERT
Getting readyHow to do it...How it works...See also
Performing bulk import using the bcp command-line utility
Getting readyHow to do it...How it works...See also
Connecting to an Azure SQL database
Getting readyHow to do it...How it works...There's more...There's more...
Creating a table in an Azure SQL database
Getting readyHow to do it...How it works...
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 readyHow to do it...How it works...See also
Listing installed hotfixes and Service Packs
How to do it...How it works...There's more...
Listing running/blocking processes
Getting readyHow to do it...How it works...There's more...See also
Killing a blocking process
Getting readyHow to do it...How it works...There's more...See also
Checking disk space usage
How to do it...How it works...There's more...
Setting up WMI server event alerts
Getting readyHow to do it...How it works...There's more...
Detaching a database
Getting readyHow to do it...How it works...There's more...See also
Attaching a database
Getting readyHow to do it...How it works...There's more...See also
Copying a database
Getting readyHow to do it...How it works...There's more...
Executing SQL query to multiple servers
Getting readyHow to do it...How it works...See also
Creating a filegroup
Getting readyHow to do it...How it works...See also
Adding a secondary data file to a filegroup
Getting readyHow to do it...How it works...See also
Increase data file size
Getting readyHow to do it...How it works...See also
Moving an index to a different filegroup
Getting readyHow to do it...How it works...There's more...See also
Checking index fragmentation
Getting readyHow to do it...How it works...There's more...See also
Reorganizing/rebuilding an index
Getting readyHow to do it...How it works...See also
Running DBCC commands
How to do it...How it works...
Setting up Database Mail
Getting readyHow to do it...How it works...
Listing SQL Server Jobs
Getting readyHow to do it...How it works...There's more...See also
Adding a SQL Server operator
Getting readyHow to do it...How it works...There's more...See also
Creating a SQL Server Job
Getting readyHow to do it...How it works...There's more...See also
Adding a SQL Server event alert
How to do it...How it works...There's more...See also
Running an SQL Server Job
Getting readyHow to do it...How it works...See also
Scheduling a SQL Server Job
Getting readyHow 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 readyHow to do it...How it works...There's more...See also
Listing authentication mode
Getting readyHow to do it...How it works...See also
Changing authentication mode
Getting readyHow to do it...How it works...There's more...More on legacy LoginMode valuesSee also
Listing SQL Server log errors
Getting readyHow to do it...How it works...See also
Listing failed login attempts
Getting readyHow to do it...How it works...See also
Enabling Common Criteria compliance
How to do it...How it works...There's more...See also
Listing logins, users, and database mappings
Getting readyHow 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 user-defined server role
Getting readyHow to do it...How it works...There's more...See also
Creating a login
Getting readyHow to do it...How it works...See also
Assigning permissions and roles to a login
Getting readyHow to do it...How it works...There's more...See also
Creating a database user
Getting readyHow to do it...How it works...See also
Assigning permissions to a database user
Getting readyHow to do it...How it works...There's more...See also
Creating a database role
Getting readyHow to do it...How it works...See also
Fixing orphaned users
Getting readyHow to do it...How it works...There's more...See also
Creating a credential
Getting readyHow to do it...How it works...See also
Creating a proxy
Getting readyHow to do it...How it works...There's more...See also
5. Backup and Restore
Introduction
Changing database recovery model
Getting readyHow to do it...How it works...There's more...See also
Checking last backup date
Getting readyHow to do it...How it works...See also
Creating a backup device
Getting readyHow to do it...How it works...There's more...See also
Listing backup header and FileList information
Getting readyHow to do it...How it works...There's more...
Creating a full backup
Getting readyHow to do it...How it works...There's more...More about backup and PercentCompleteEventHandlerSee also
Creating a backup on Mirrored Media Sets
Getting readyHow to do it...How it works...There's more...See also
Creating a differential backup
Getting readyHow to do it...How it works...There's more...See also
Creating a transaction log backup
Getting readyHow to do it...How it works...There's more...See also
Creating a filegroup backup
Getting readyHow to do it...How it works...There's more...See also
Restoring a database to a point-in-time
Getting readyHow to do it...How it works...Gathering your backup filesRestoring the latest good full backup with NORECOVERYRestoring the last good differential backup taken after the full backup you just restored with NORECOVERYRestoring the transaction logs taken after your differential backupThere's more...See also
Performing an online piecemeal restore
Getting readyHow to do it...How it works...There's more...See also
Backing up database to Azure Blob storage
Getting readyHow to do it...How it works...There's more...See also
Restoring database from Azure Blob storage
Getting readyHow to do it...How it works...There's more...See also
6. Advanced Administration
Introduction
Connecting to LocalDB
Getting readyHow to do it...How it works...There's more...See also
Creating a new LocalDB instance
Getting readyHow to do it...How it works...There's more...See also
Listing database snapshots
Getting readyHow to do it...How it works...There's more...See also
Creating a database snapshot
Getting readyHow to do it...How it works...See also
Dropping a database snapshot
How to do it...How it works...See also
Enabling FileStream
How to do it...How it works...There's more...See also
Setting up a FileStream filegroup
Getting readyHow to do it...How it works...There's more...See also
Adding a FileTable
Getting readyHow to do it...How it works...There's more...See also
Adding full-text catalog
Getting readyHow to do it...How it works...There's more...See also
Adding full-text index
Getting readyHow to do it...How it works...There's more...See also
Creating a memory-optimized table
Getting readyHow to do it...How it works...There's more...See also
Creating a database master key
Getting readyHow to do it...How it works...There's more...See also
Creating a certificate
Getting readyHow to do it...How it works...There's more...See also
Creating symmetric and asymmetric keys
Getting readyHow to do it...How it works...There's more...See also
Setting up Transparent Data Encryption
Getting readyHow to do it...How it works...There's more...See also
7. Audit and Policies
Introduction
Enabling/disabling change tracking
Getting readyHow to do it...How it works...There's more...See also
Configuring SQL Server Audit
How to do it...How it works...There's more...See also
Listing facets and their properties
How to do it...How it works...There's more...See also
Listing policies
Getting readyHow to do it...How it works...There's more...See also
Exporting a policy
Getting readyHow to do it...How it works...There's more...See also
Importing a policy
Getting readyHow to do it...How it works...There's more...See also
Creating a condition
Getting readyHow to do it...How it works...There's more...See also
Creating a policy
Getting readyHow to do it...How it works...There's more...See also
Evaluating a policy
Getting readyHow to do it...How it works...There's more...See also
Running and saving a profiler trace event
Getting readyHow to do it...How it works...There's more...See also
Extracting the contents of a trace file
Getting readyHow to do it...How it works...See also
8. High Availability with AlwaysOn
Introduction
Installing the Failover Cluster feature on Windows
Getting readyHow to do it...How it works...There's more...
Enabling TCP and named pipes in SQL Server
Getting readyHow to do it...How it works...There's more...
Enabling AlwaysOn in SQL Server
Getting readyHow to do it...How it works...There's more...
Creating and enabling the HADR endpoint
Getting readyHow to do it...How it works...There's more...See also
Granting the CONNECT permission to the HADR endpoint
Getting readyHow to do it...How it works...There's more...See also
Creating an AlwaysOn Availability Group
Getting readyHow to do it...How it works...There's more...
Joining the secondary replicas to Availability Group
Getting readyHow to do it...How it works...There's more...See also
Adding an availability database to an Availability Group
Getting readyHow to do it...How it works...There's more...
Creating an Availability Group listener
Getting readyHow to do it...How it works...There's more...
Testing the Availability Group failover
Getting readyHow to do it...How it works...There's more...
Monitoring the health of an Availability Group
Getting readyHow to do it...How it works...There's more...
9. SQL Server Development
Introduction
Importing data from a text file
Getting readyHow to do it...How it works...There's more...
Exporting records to a text file
Getting readyHow to do it...How it works...There's more...
Adding files to a FileTable
Getting readyHow to do it...How it works...There's more...See also
Inserting XML into SQL Server
Getting readyHow to do it...How it works...There's more...See also
Extracting XML from SQL Server
Getting readyHow to do it...How it works...See also
Creating an RSS feed from SQL Server content
Getting readyHow to do it...How it works...There's more...See also
Applying XSL to an RSS feed
Getting readyHow to do it...How it works...There's more...See also
Creating a JSON file from SQL Server
Getting readyHow to do it...How it works...There's more...
Storing binary data in SQL Server
Getting readyHow to do it...How it works...There's more...See also
Extracting binary data from SQL Server
Getting readyHow to do it...How it works...There's more...See also
Creating a new assembly
Getting readyHow to do it...How it works...There's more...See also
Listing user-defined assemblies
Getting readyHow to do it...How it works...There's more...See also
Extracting user-defined assemblies
Getting readyHow to do it...How it works...See also
10. Business Intelligence
Introduction
Listing items in your SSRS Report Server
Getting readyHow to do it...How it works...There's more...See also
Listing SSRS report properties
Getting readyHow to do it...How it works...There's more...See also
Using ReportViewer to view your SSRS report
Getting readyHow to do it...How it works...There's more...See also
Downloading an SSRS report in Excel and as a PDF
Getting readyHow to do it...How it works...There's more...See also
Creating an SSRS folder
Getting readyHow to do it...How it works...There's more...See also
Creating an SSRS data source
Getting readyHow to do it...How it works...There's more...See also
Changing an SSRS report's data source reference
Getting readyHow to do it...How it works...There's more...See also
Uploading an SSRS report to Report Manager
Getting readyHow to do it...How it works...There's more...See also
Downloading all SSRS report RDL files
Getting readyHow to do it...How it works...There's more...See also
Adding a user with a role to SSRS report
Getting readyHow to do it...How it works...There's more...
Creating folders in an SSIS package store and MSDB
Getting readyHow to do it...How it works...There's more...See also
Deploying an SSIS package to the package store
Getting readyHow to do it...How it works...There's more...See also
Executing an SSIS package stored in a package store or filesystem
Getting readyHow to do it...How it works...There's more...See also
Downloading an SSIS package to a file
Getting readyHow to do it...How it works...There's more...See also
Creating an SSISDB catalog
Getting readyHow to do it...How it works...There's more...See also
Creating an SSISDB folder
Getting readyHow to do it...How it works...There's more...See also
Deploying an ISPAC file to SSISDB
Getting readyHow to do it...How it works...There's more...See also
Executing an SSIS package stored in SSISDB
Getting readyHow to do it...How it works...There's more...See also
Listing SSAS cmdlets
How to do it...How it works...There's more...See also
Listing SSAS instance properties
How to do it...How it works...There's more...See also
Backing up an SSAS database
Getting readyHow to do it...How it works...There's more...See also
Restoring an SSAS database
Getting readyHow to do it...How it works...There's more...See also
Processing an SSAS cube
Getting readyHow to do it...How it works...There's more...See also
11. Helpful PowerShell Snippets
Introduction
Documenting PowerShell script for Get-Help
How to do it...How it works...There's more...
Getting history
How to do it...How it works...
Getting a timestamp
How to do it...How it works...There's more...
Getting more 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 readyHow 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
Compressing files
How to do it...How it works...
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 an e-mail
Getting readyHow 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 readyHow 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 readyHow to do it...How it works...There's more...
A. PowerShell Primer
Introduction
Understanding the need for PowerShell
Setting up the environment
Running PowerShell scripts
Through shell or through the ISEThe execution policy
Learning PowerShell basics
CmdletsLearning PowerShellGet-CommandGet-HelpGet-MemberStarter notesPowerShell is object-oriented and works with .NETCmdlets may have aliases or you can create oneYou can chain commandsFilter left, format rightPackage and reuseCommon cmdlets
Scripting syntax
Statement terminatorsEscape and line continuationVariablesHere-stringString interpolationOperatorsDisplaying messagesCommentsSpecial variablesSpecial charactersConditionsRegular expressionsArraysHash tablesLoopsError handling
Converting scripts into functions
Listing notable PowerShell features
Exploring more PowerShell
B. Creating a SQL Server VM
Introduction
Terminologies
Downloading software
VM details and accounts
Creating an empty virtual machine
Installing Windows Server 2012 R2 as guest OS
Installing VMware tools
Making a snapshot as a baseline
Configuring a domain controller (optional)
Creating domain accounts
Installing SQL Server 2014 on a VM
Configuring Reporting Services in native mode
Installing sample databases
Installing PowerShell V5
Using SQL Server on a Windows Azure VM
Index

Content preview from SQL Server 2014 with Powershell v5 Cookbook

Setting up Database Mail

This recipe demonstrates how to set up Database Mail programmatically using PowerShell.

Getting ready

The assumption in this recipe is that Database Mail is not yet configured on your instance.

These are the settings we will use for this recipe:

Setting	Value
Mail Server	`mail.queryworks.local`
Mail Server Port	`25`
E-mail address for Database Mail profile	`dbmail@queryworks.local`
SMTP authentication	Basic authentication
Credentials for e-mail address	Username is `dbmail@queryworks.local` Password is `<some password>`

How to do it...

To set up Database Mail, follow these steps:

Open PowerShell ISE as administrator.
Import the SQLPS module and create a new SMO Server object:
```
#import SQL Server module Import-Module SQLPS ...
```

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Microsoft SQL Server 2012 Performance Tuning Cookbook

Publisher Resources

ISBN: 9781785283321

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills