PowerShell for SQL Server Essentials电子书

PowerShell for SQL Server Essentials

Table of Contents

PowerShell for SQL Server Essentials

Credits

About the Author

Acknowledgments

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 PowerShell

A brief history of PowerShell

The PowerShell environment

The PowerShell console

The PowerShell ISE

Running PowerShell as an administrator

The execution policy

PowerShell versions

PowerShell cmdlets

Cmdlet naming convention

Cmdlet parameters

Cmdlet aliases

PowerShell providers

Snap-ins and modules

PowerShell Pipeline

Scripting basics

Running PowerShell scripts

Getting help

Getting help from other cmdlets

Summary

2. Using PowerShell with SQL Server

SQL Server via PowerShell

Mini-shell (or the sqlps utility)

The SQLPS module

SQL Server snap-ins

SQL Server assemblies

SQL Server-specific cmdlets

SQL Server Management Objects

Creating SMO objects

Summary

3. Profiling and Configuring SQL Server

Current server resources

Getting processor (CPU) information

Checking server memory

Checking disk space

Checking network settings

Hotfixes and service packs

Current SQL Server instances

Services and service accounts

SQL Server error logs

Current instance configuration

Changing configurations

Start or stop services

Changing a service account

Changing instance settings

Summary

4. Basic SQL Server Administration

Listing databases and tables

Listing database files and filegroups

Adding files and filegroups

Listing the processes

Checking enabled features

Scripting database objects

Attaching and detaching databases

Detaching databases

Backing up and restoring databases

Backing up

Restoring

Reorganizing or rebuilding indexes

Managing logins, users, and permissions

Permissions

Adding a login

Adding database users

Policies

Managing jobs

Summary

5. Querying SQL Server with PowerShell

To PowerShell or not to PowerShell

Sending queries to SQL Server

SQL Server Management Objects

The Invoke-Sqlcmd cmdlet

ADO.NET

The Invoke-Expression cmdlet

Sending simple queries to SQL Server – different variations

Fixing orphaned users

Getting fragmentation data

Backing up and restoring databases

Exporting data using bcp

Summary

6. Monitoring and Automating SQL Server

Getting to know helpful cmdlets

The Send-MailMessage cmdlet

The ConvertTo-Html cmdlet

The Export-Csv cmdlet

The Write-EventLog cmdlet

Additional cmdlets

Scheduling PowerShell scripts

Checking logs

Monitoring failed jobs

Alerting on disk space usage

Logging blocked processes

Getting performance metrics

Summary

A. Implementing Reusability with Functions and Modules

Functions

Simple functions

Advanced functions

Best practices

Modules

Script modules

Summary

Index