Microsoft® SQL Server® 2012 Bible

769

CHAPTER

30

Confi guring and Managing SQL

Server with PowerShell

IN THIS CHAPTER

Introduction to PowerShell and PowerShell Functions

Avoiding the SMO While Using PowerShell to Automate SQL Server Administration

Using PowerShell to Extract SQL Server Data

New SQL Server 2012 PowerShell Features

P

owerShell is the new scripting environment for the Microsoft Windows platform. For admin-

istrators who don’t have access to the Visual Studio environment, or for the IT professional

who prefers scripting to compiled code, PowerShell provides access to the .NET Framework

and the objects available within it. PowerShell is part of the operating system for Windows Server

2008, Windows Server 2008 R2, and Windows Server 2012. It’s also available as a free download from

Microsoft at http://www.microsoft.com/powershell/. SQL Server 2012 comes with a Provider and 40

cmdlets automatically installed when SQL Server Management Studio 2012 is installed. PowerShell

is included with the Windows Server 2008 and later operating systems but must be installed on

Windows 2003, Windows XP (SP2 or later), and Windows Vista. It cannot run on operating systems

earlier than those mentioned here.

PowerShell was introduced in late 2006 to enable administrators to develop scripts for automat-

ing processes they did regularly. Prior to the introduction of PowerShell, most administrators used

command-line batch fi les, VBScript, or some third-party proprietary application designed for this

function.

Batch fi les had somewhat limited functionality and signifi cant security problems. It was easy using

batch fi les to “hijack” actual operating system commands by creating a batch fi le with the same

name and placing it in a directory in the PATH environment variable or in the current directory.

Batch fi les also suffer signifi cant limitations for iterative processes.

PowerShell addresses these limitations through a strong security foundation that prevents com-

mand-line hijacking by allowing only the user (by default) to run digitally signed scripts, and by

forcing the user to specify the directory for each command run from the script environment. It also

supports the .NET Framework (2.0 and later), so the functionality built in the framework is avail-

able in this scripting environment.

c30.indd 769c30.indd 769 7/31/2012 9:46:19 AM7/31/2012 9:46:19 AM

http://www.it-ebooks.info