SQL Server 2012 with PowerShell V3 Cookbook

Preface 1

Chapter 1: Getting Started with SQL Server and PowerShell 7

Introduction 7

Before you start: Working with SQL Server and PowerShell 10

Working with the sample code 12

Exploring the SQL Server PowerShell hierarchy 14

Installing SMO 18

Loading SMO assemblies 20

Discovering SQL-related cmdlets and modules 22

Creating a SQL Server instance object 29

Exploring SMO server objects 32

Chapter 2: SQL Server and PowerShell Basic Tasks 35

Introduction 36

Listing SQL Server instances 39

Discovering SQL Server services 43

Starting/stopping SQL Server services 45

Listing SQL Server configuration settings 51

Changing SQL Server instance configurations 55

Searching for database objects 60

Creating a database 67

Altering database properties 68

Dropping a database 72

Changing a database owner 73

Creating a table 75

Creating a view 81

Creating a stored procedure 85

Creating a trigger 90

Creating an index 95

Executing a query / SQL script 99

Performing bulk export using Invoke-Sqlcmd 100

Performing bulk export using bcp 102

Performing bulk import using BULK INSERT 105

Performing bulk import using bcp 110

Chapter 3: Basic Administration 115

Introduction 116

Creating a SQL Server instance inventory 116

Creating a SQL Server database inventory 120

Listing installed hotfixes and service packs 124

Listing running/blocking processes 128

Killing a blocking process 131

Checking disk space usage 133

Setting up WMI Server event alerts 136

Detaching a database 143

Attaching a database 145

Copying a database 149

Executing a SQL query to multiple servers 152

Creating a filegroup 153

Adding secondary data files to a filegroup 156

Moving an index to a different filegroup 158

Checking index fragmentation 162

Reorganizing/rebuilding an index 164

Running DBCC commands 167

Setting up Database Mail 168

Listing SQL Server jobs 178

Adding a SQL Server operator 181

Creating a SQL Server job 183

Adding a SQL Server event alert 187

Running a SQL Server job 190

Scheduling a SQL Server job 192

Chapter 4: Security 203

Introduction 203

Listing SQL Server service accounts 204

Changing SQL Server service account 206

Listing authentication modes 210

Changing authentication mode 211

Listing SQL Server log errors 215

Listing failed login attempts 220

Listing logins, users, and database mappings 222

Listing login/user roles and permissions 225

Creating a login 227

Assigning permissions and roles to a login 229

Creating a database user 232

Assigning permissions to a database user 234

Creating a database role 237

Fixing orphaned users 241

Creating a credential 244

Creating a proxy 246

Chapter 5: Advanced Administration 251

Introduction 252

Listing facets and facet properties 252

Listing policies 254

Exporting a policy 257

Importing a policy 261

Creating a condition 264

Creating a policy 268

Evaluating a policy 272

Enabling/disabling change tracking 275

Running and saving a profiler trace event 276

Extracting the contents of a trace file 284

Creating a database master key 289

Creating a certificate 291

Creating symmetric and asymmetric keys 293

Setting up Transparent Data Encryption (TDE) 299

Chapter 6: Backup and Restore 305

Introduction 305

Changing database recovery model 306

Listing backup history 309

Creating a backup device 310

Listing backup header and file list information 312

Creating a full backup 316

Creating a backup on mirrored media sets 321

Creating a differential backup 324

Creating a transaction log backup 327

Creating a filegroup backup 329

Restoring a database to a point in time 332

Performing an online piecemeal restore 342

Chapter 7: SQL Server Development 351

Introduction 351

Inserting XML into SQL Server 352

Extracting XML from SQL Server 355

Creating an RSS feed from SQL Server content 358

Applying XSL to an RSS feed 363

Storing binary data into SQL Server 366

Extracting binary data from SQL Server 370

Creating a new assembly 374

Listing user-defined assemblies 378

Extracting user-defined assemblies 379

Chapter 8: Business Intelligence 385

Introduction 386

Listing items in your SSRS Report Server 386

Listing SSRS report properties 388

Using ReportViewer to view your SSRS report 391

Downloading an SSRS report in Excel and PDF 396

Creating an SSRS folder 400

Creating an SSRS data source 404

Changing an SSRS report's data source reference 409

Uploading an SSRS report to Report Manager 412

Downloading all SSRS report RDL files 416

Adding a user with a role to an SSRS report 421

Creating folders in an SSIS package store and MSDB 425

Deploying an SSIS package to the package store 428

Executing an SSIS package stored in the package store or File System 430

Downloading an SSIS package to a file 433

Creating an SSISDB catalog 435

Creating an SSISDB folder 439

Deploying an ISPAC file to SSISDB 441

Executing an SSIS package stored in SSISDB 444

Listing SSAS cmdlets 447

Listing SSAS instance properties 448

Backing up an SSAS database 450

Restoring an SSAS database 451

Processing an SSAS cube 452

Chapter 9: Helpful PowerShell Snippets 455

Introduction 456

Documenting PowerShell script for Get-Help 456

Getting a timestamp 459

Getting additional error messages 461

Listing processes 462

Getting aliases 466

Exporting to CSV and XML 467

Using Invoke-Expression 468

Testing regular expressions 470

Managing folders 474

Manipulating files 476

Searching for files 478

Reading an event log 481

Sending e-mail 482

Embedding C# code 484

Creating an HTML report 486

Parsing XML 488

Extracting data from a web service 490

Using PowerShell Remoting 492

Appendix A: SQL Server and PowerShell CheatSheet 497

Learning PowerShell 497

PowerShell V2 versus V3 Where-Object syntax 498

Changing execution policy 498

Running a script 499

Common aliases 499

Displaying output 500

Special characters 500

Special variables 501

Common operators 502

Common date-time format strings 502

Comment based help 503

Here-string 504

Common regex characters and patterns 504

Arrays and hash tables 505

Arrays and loops 506

Logic 506

Functions 507

Common Cmdlets 508

Import SQLPS module 509

Add SQL Server Snapins 509

Add SQL Server Assemblies 509

Getting credentials 510

Running and blocking SQL Server processes 510

Read file into an array 510

SQL Server-Specific Cmdlets 510

Invoke-SqlCmd 512

Create SMO Server Object 512

Create SSRS Proxy Object 512

Create SSIS Object (SQL Server 2005/2008/2008R2) 513

Create an SSIS Object (SQL Server 2012) 513

Create SSAS Object 513

Appendix B: PowerShell Primer 515

Introduction 515

What is PowerShell, and why learn another language 515

Setting up the Environment 516

Running PowerShell scripts 517

Basics—points to remember 520

Scripting syntax 527

Converting script into functions 539

More about PowerShell 542

Appendix C: Resources 543

Resources 543

Appendix D: Creating a SQL Server VM 549

Introduction 549

Terminology 550

Downloading software 551

VM details and accounts 552

Creating an empty virtual machine 553

Installing Windows Server 2008 R2 as

Guest OS 556

Installing VMWare tools 567

Configuring a domain controller 569

Creating domain accounts 577

Installing SQL Server 2012 on a VM 580

Installing sample databases 598

Installing PowerShell V3 598

Index 601