Sign In Start Free Trial

Book Overview & Buying
Table Of Contents

PowerShell for SQL Server Essentials

By : Donabel Santos

3.8 (5)

PowerShell for SQL Server Essentials

3.8 (5)

By: Donabel Santos

Overview of this book

This book is written for SQL Server administrators and developers who want to leverage PowerShell to work with SQL Server. Some background with scripting will be helpful but not necessary.

Preface

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Free Chapter

1. Getting Started with PowerShell

1. Getting Started with PowerShell

A brief history of PowerShell

The PowerShell environment

PowerShell versions

PowerShell cmdlets

PowerShell providers

Snap-ins and modules

PowerShell Pipeline

Scripting basics

Running PowerShell scripts

Getting help

Summary

2. Using PowerShell with SQL Server

2. Using PowerShell with SQL Server

SQL Server via PowerShell

SQL Server-specific cmdlets

SQL Server Management Objects

Summary

3. Profiling and Configuring SQL Server

3. Profiling and Configuring SQL Server

Current server resources

Hotfixes and service packs

Current SQL Server instances

Services and service accounts

SQL Server error logs

Current instance configuration

Changing configurations

Summary

4. Basic SQL Server Administration

4. Basic SQL Server Administration

Listing databases and tables

Adding files and filegroups

Checking enabled features

Scripting database objects

Attaching and detaching databases

Backing up and restoring databases

Reorganizing or rebuilding indexes

Managing logins, users, and permissions

Policies

Managing jobs

Summary

5. Querying SQL Server with PowerShell

5. Querying SQL Server with PowerShell

To PowerShell or not to PowerShell

Sending queries to SQL Server

Fixing orphaned users

Getting fragmentation data

Backing up and restoring databases

Exporting data using bcp

Summary

6. Monitoring and Automating SQL Server

6. Monitoring and Automating SQL Server

Getting to know helpful 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

A. Implementing Reusability with Functions and Modules

Functions

Modules

Summary

Index

Index

Current SQL Server instances

You should also check how many SQL Server instances are installed on the server and their names for non-default instances. To do this, we can use SQL Management Objects (SMO) with PowerShell and then use the ServerInstances member property:

#below should be a single line of code
$managedComputer = New-Object "Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer" $servername

#list SQL Server instances
$managedComputer.ServerInstances |
Select-Object Name, State, ServerProtocols, Urn |
Format-List

The preceding script simply lists the instances installed. Note that MSSQLSERVER is the name you'll see for a default instance:

Current SQL Server instances

CONTINUE READING

83

Tech Concepts

36

Programming languages

73

Tech Tools

Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.

Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.

50+ new titles added per month and exclusive early access to books as they are being written.

PowerShell for SQL Server Essentials

Search

Your notes and bookmarks