Book Image

SQL Server 2014 with PowerShell v5 Cookbook

By : Donabel Santos
Book Image

SQL Server 2014 with PowerShell v5 Cookbook

By: Donabel Santos

Overview of this book

Related Content you might be interested in

Current Title:

Small book image
SQL Server 2014 with PowerShell v5 Cookbook
Donabel Santos
Dec, 2015 | 760 pages
No titles found
Table of Contents (21 chapters)
SQL Server 2014 with PowerShell v5 Cookbook
SQL Server 2014 with PowerShell v5 Cookbook
Credits
Credits
About the Author
About the Author
Acknowledgments
Acknowledgments
About the Reviewers
About the Reviewers
www.PacktPub.com
www.PacktPub.com
Preface
Preface
Free Chapter
1
Getting Started with SQL Server and PowerShell
Getting Started with SQL Server and PowerShell
Introduction
Working with the sample code
Installing SQL Server using PowerShell
Installing SQL Server Management Objects
Loading SMO assemblies
Exploring the SQL Server PowerShell hierarchy
Discovering SQL-related cmdlets and modules
Creating a SQL Server Instance Object
Exploring SMO Server Objects
2
SQL Server and PowerShell Basic Tasks
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
Scripting SQL Server Stored Procedures
Creating a database
Altering database properties
Dropping a database
Changing 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 the bcp command-line utility
Performing bulk import using BULK INSERT
Performing bulk import using the bcp command-line utility
Connecting to an Azure SQL database
Creating a table in an Azure SQL database
3
Basic Administration
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 SQL query to multiple servers
Creating a filegroup
Adding a secondary data file to a filegroup
Increase data file size
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 an SQL Server Job
Scheduling a SQL Server Job
4
Security
Security
Introduction
Listing SQL Server service accounts
Changing SQL Server service account
Listing authentication mode
Changing authentication mode
Listing SQL Server log errors
Listing failed login attempts
Enabling Common Criteria compliance
Listing logins, users, and database mappings
Listing login/user roles and permissions
Creating a user-defined server role
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
5
Backup and Restore
Backup and Restore
Introduction
Changing database recovery model
Checking last backup date
Creating a backup device
Listing backup header and FileList 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
Backing up database to Azure Blob storage
Restoring database from Azure Blob storage
6
Advanced Administration
Advanced Administration
Introduction
Connecting to LocalDB
Creating a new LocalDB instance
Listing database snapshots
Creating a database snapshot
Dropping a database snapshot
Enabling FileStream
Setting up a FileStream filegroup
Adding a FileTable
Adding full-text catalog
Adding full-text index
Creating a memory-optimized table
Creating a database master key
Creating a certificate
Creating symmetric and asymmetric keys
Setting up Transparent Data Encryption
7
Audit and Policies
Audit and Policies
Introduction
Enabling/disabling change tracking
Configuring SQL Server Audit
Listing facets and their properties
Listing policies
Exporting a policy
Importing a policy
Creating a condition
Creating a policy
Evaluating a policy
Running and saving a profiler trace event
Extracting the contents of a trace file
8
High Availability with AlwaysOn
High Availability with AlwaysOn
Introduction
Installing the Failover Cluster feature on Windows
Enabling TCP and named pipes in SQL Server
Enabling AlwaysOn in SQL Server
Creating and enabling the HADR endpoint
Granting the CONNECT permission to the HADR endpoint
Creating an AlwaysOn Availability Group
Joining the secondary replicas to Availability Group
Adding an availability database to an Availability Group
Creating an Availability Group listener
Testing the Availability Group failover
Monitoring the health of an Availability Group
9
SQL Server Development
SQL Server Development
Introduction
Importing data from a text file
Exporting records to a text file
Adding files to a FileTable
Inserting XML into SQL Server
Extracting XML from SQL Server
Creating an RSS feed from SQL Server content
Applying XSL to an RSS feed
Creating a JSON file from SQL Server
Storing binary data in SQL Server
Extracting binary data from SQL Server
Creating a new assembly
Listing user-defined assemblies
Extracting user-defined assemblies
10
Business Intelligence
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 as a 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 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 a package store or filesystem
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
11
Helpful PowerShell Snippets
Helpful PowerShell Snippets
Introduction
Documenting PowerShell script for Get-Help
Getting history
Getting a timestamp
Getting more error messages
Listing processes
Getting aliases
Exporting to CSV and XML
Using Invoke-Expression
Testing regular expressions
Managing folders
Manipulating files
Compressing files
Searching for files
Reading an event log
Sending an e-mail
Embedding C# code
Creating an HTML report
Parsing XML
Extracting data from a web service
Using PowerShell remoting
PowerShell Primer
PowerShell Primer
Introduction
Understanding the need for PowerShell
Setting up the environment
Running PowerShell scripts
Learning PowerShell basics
Scripting syntax
Converting scripts into functions
Listing notable PowerShell features
Exploring more PowerShell
Creating a SQL Server VM
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
Index

Listing SQL Server Jobs

This recipe illustrates how to list SQL Server Jobs using PowerShell.

Getting ready

Do a visual check of your SQL Server Jobs in your instance. You should see the following jobs after you run the script in this recipe:

How to do it...

These are the steps to list SQL Server Jobs:

  1. Open PowerShell ISE as administrator.

  2. Import the SQLPS module and create a new SMO Server object:

    #import SQL Server module
Import-Module SQLPS -DisableNameChecking

#replace this with your instance name
$instanceName = "localhost"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

    Add the following script and run:

    $jobs=$server.JobServer.Jobs
$jobs |
Select-Object Name, OwnerLoginName,
LastRunDate, LastRunOutcome |
Sort-Object -Property Name |
Format-Table -AutoSize

How it works...

Listing SQL Server Jobs is a short, simple task in PowerShell. To list the jobs, you first need to get a handle to the JobServer.Jobs object:

$jobs=$server.JobServer.Jobs

Once...

Previous Section
End of Section 22
Next Section