Browse Library
Sign In Start Free Trial
Close icon Search icon
Account
Browse Library Sign In Start Free Trial

Add to playlist

Create a Playlist

Modal Close icon
You need to login to use this feature.
  • Book Overview & Buying SQL Server 2012 with PowerShell V3 Cookbook
  • Table Of Contents Toc
SQL Server 2012 with PowerShell V3 Cookbook

SQL Server 2012 with PowerShell V3 Cookbook

By : Donabel Santos
4.4 (5)
Buy this Book
close
close
SQL Server 2012 with PowerShell V3 Cookbook

SQL Server 2012 with PowerShell V3 Cookbook

4.4 (5)
By: Donabel Santos
Buy this Book

Overview of this book

PowerShell is Microsoft's new command-line shell and scripting language that promises to simplify automation and integration across different Microsoft applications and components. Database professionals can leverage PowerShell by utilizing its numerous built-in cmdlets, or using any of the readily available .NET classes, to automate database tasks, simplify integration, or just discover new ways to accomplish the job at hand."SQL Server 2012 with PowerShell V3 Cookbook" provides easy-to-follow, practical examples for the busy database professional. Whether you're auditing your servers, or exporting data, or deploying reports, there is a recipe that you can use right away!You start off with basic topics to get you going with SQL Server and PowerShell scripts and progress into more advanced topics to help you manage and administer your SQL Server databases.The first few chapters demonstrate how to work with SQL Server settings and objects, including exploring objects, creating databases, configuring server settings, and performing inventories. The book then deep dives into more administration topics like backup and restore, credentials, policies, jobs.Additional development and BI-specific topics are also explored, including deploying and downloading assemblies, BLOB data, SSIS packages, and SSRS reports. A short PowerShell primer is also provided as a supplement in the Appendix, which the database professional can use as a refresher or occasional reference material. Packed with more than 100 practical, ready-to-use scripts, "SQL Server 2012 with PowerShell V3 Cookbook" will be your go-to reference in automating and managing SQL Server.
Table of Contents (15 chapters)
close
close
close
Preface
Icon
Preface
Icon
What this book covers
Icon
What you need for this book
Icon
Who this book is for
Icon
Conventions
Icon
Reader feedback
Icon
Customer support
Lock Free Chapter
1
1. Getting Started with SQL Server and PowerShell
chevron up
Icon
1. Getting Started with SQL Server and PowerShell
Icon
Introduction
Icon
Before you start: Working with SQL Server and PowerShell
Icon
Working with the sample code
Icon
Exploring the SQL Server PowerShell hierarchy
Icon
Installing SMO
Icon
Loading SMO assemblies
Icon
Discovering SQL-related cmdlets and modules
Icon
Creating a SQL Server instance object
Icon
Exploring SMO server objects
2
2. SQL Server and PowerShell Basic Tasks
Icon
2. SQL Server and PowerShell Basic Tasks
Icon
Introduction
Icon
Listing SQL Server instances
Icon
Discovering SQL Server services
Icon
Starting/stopping SQL Server services
Icon
Listing SQL Server configuration settings
Icon
Changing SQL Server instance configurations
Icon
Searching for database objects
Icon
Creating a database
Icon
Altering database properties
Icon
Dropping a database
Icon
Changing a database owner
Icon
Creating a table
Icon
Creating a view
Icon
Creating a stored procedure
Icon
Creating a trigger
Icon
Creating an index
Icon
Executing a query / SQL script
Icon
Performing bulk export using Invoke-Sqlcmd
Icon
Performing bulk export using bcp
Icon
Performing bulk import using BULK INSERT
Icon
Performing bulk import using bcp
3
3. Basic Administration
Icon
3. Basic Administration
Icon
Introduction
Icon
Creating a SQL Server instance inventory
Icon
Creating a SQL Server database inventory
Icon
Listing installed hotfixes and service packs
Icon
Listing running/blocking processes
Icon
Killing a blocking process
Icon
Checking disk space usage
Icon
Setting up WMI Server event alerts
Icon
Detaching a database
Icon
Attaching a database
Icon
Copying a database
Icon
Executing a SQL query to multiple servers
Icon
Creating a filegroup
Icon
Adding secondary data files to a filegroup
Icon
Moving an index to a different filegroup
Icon
Checking index fragmentation
Icon
Reorganizing/rebuilding an index
Icon
Running DBCC commands
Icon
Setting up Database Mail
Icon
Listing SQL Server jobs
Icon
Adding a SQL Server operator
Icon
Creating a SQL Server job
Icon
Adding a SQL Server event alert
Icon
Running a SQL Server job
Icon
Scheduling a SQL Server job
4
4. Security
Icon
4. Security
Icon
Introduction
Icon
Listing SQL Server service accounts
Icon
Changing SQL Server service account
Icon
Listing authentication modes
Icon
Changing authentication mode
Icon
Listing SQL Server log errors
Icon
Listing failed login attempts
Icon
Listing logins, users, and database mappings
Icon
Listing login/user roles and permissions
Icon
Creating a login
Icon
Assigning permissions and roles to a login
Icon
Creating a database user
Icon
Assigning permissions to a database user
Icon
Creating a database role
Icon
Fixing orphaned users
Icon
Creating a credential
Icon
Creating a proxy
5
5. Advanced Administration
Icon
5. Advanced Administration
Icon
Introduction
Icon
Listing facets and facet properties
Icon
Listing policies
Icon
Exporting a policy
Icon
Importing a policy
Icon
Creating a condition
Icon
Creating a policy
Icon
Evaluating a policy
Icon
Enabling/disabling change tracking
Icon
Running and saving a profiler trace event
Icon
Extracting the contents of a trace file
Icon
Creating a database master key
Icon
Creating a certificate
Icon
Creating symmetric and asymmetric keys
Icon
Setting up Transparent Data Encryption (TDE)
6
6. Backup and Restore
Icon
6. Backup and Restore
Icon
Introduction
Icon
Changing database recovery model
Icon
Listing backup history
Icon
Creating a backup device
Icon
Listing backup header and file list information
Icon
Creating a full backup
Icon
Creating a backup on mirrored media sets
Icon
Creating a differential backup
Icon
Creating a transaction log backup
Icon
Creating a filegroup backup
Icon
Restoring a database to a point in time
Icon
Performing an online piecemeal restore
7
7. SQL Server Development
Icon
7. SQL Server Development
Icon
Introduction
Icon
Inserting XML into SQL Server
Icon
Extracting XML from SQL Server
Icon
Creating an RSS feed from SQL Server content
Icon
Applying XSL to an RSS feed
Icon
Storing binary data into SQL Server
Icon
Extracting binary data from SQL Server
Icon
Creating a new assembly
Icon
Listing user-defined assemblies
Icon
Extracting user-defined assemblies
8
8. Business Intelligence
Icon
8. Business Intelligence
Icon
Introduction
Icon
Listing items in your SSRS Report Server
Icon
Listing SSRS report properties
Icon
Using ReportViewer to view your SSRS report
Icon
Downloading an SSRS report in Excel and PDF
Icon
Creating an SSRS folder
Icon
Creating an SSRS data source
Icon
Changing an SSRS report's data source reference
Icon
Uploading an SSRS report to Report Manager
Icon
Downloading all SSRS report RDL files
Icon
Adding a user with a role to an SSRS report
Icon
Creating folders in an SSIS package store and MSDB
Icon
Deploying an SSIS package to the package store
Icon
Executing an SSIS package stored in the package store or File System
Icon
Downloading an SSIS package to a file
Icon
Creating an SSISDB catalog
Icon
Creating an SSISDB folder
Icon
Deploying an ISPAC file to SSISDB
Icon
Executing an SSIS package stored in SSISDB
Icon
Listing SSAS cmdlets
Icon
Listing SSAS instance properties
Icon
Backing up an SSAS database
Icon
Restoring an SSAS database
Icon
Processing an SSAS cube
9
9. Helpful PowerShell Snippets
Icon
9. Helpful PowerShell Snippets
Icon
Introduction
Icon
Documenting PowerShell script for Get-Help
Icon
Getting a timestamp
Icon
Getting additional error messages
Icon
Listing processes
Icon
Getting aliases
Icon
Exporting to CSV and XML
Icon
Using Invoke-Expression
Icon
Testing regular expressions
Icon
Managing folders
Icon
Manipulating files
Icon
Searching for files
Icon
Reading an event log
Icon
Sending e-mail
Icon
Embedding C# code
Icon
Creating an HTML report
Icon
Parsing XML
Icon
Extracting data from a web service
Icon
Using PowerShell Remoting
10
A. SQL Server and PowerShell CheatSheet
Icon
Learning PowerShell
Icon
PowerShell V2 versus V3 Where-Object syntax
Icon
Changing execution policy
Icon
Running a script
Icon
Common aliases
Icon
Displaying output
Icon
Special characters
Icon
Special variables
Icon
Common operators
Icon
Common date-time format strings
Icon
Here-string
Icon
Common regex characters and patterns
Icon
Arrays and hash tables
Icon
Arrays and loops
Icon
Logic
Icon
Functions
Icon
Common Cmdlets
Icon
Import SQLPS module
Icon
Add SQL Server Snapins
Icon
Add SQL Server Assemblies
Icon
Getting credentials
Icon
Running and blocking SQL Server processes
Icon
Read file into an array
Icon
SQL Server-Specific Cmdlets
Icon
Invoke-SqlCmd
Icon
Create SMO Server Object
Icon
Create SSRS Proxy Object
Icon
Create SSIS Object (SQL Server 2005/2008/2008R2)
Icon
Create an SSIS Object (SQL Server 2012)
Icon
Create SSAS Object
11
B. PowerShell Primer
Icon
B. PowerShell Primer
Icon
Introduction
Icon
What is PowerShell, and why learn another language
Icon
Setting up the Environment
Icon
Running PowerShell scripts
Icon
Basics—points to remember
Icon
Scripting syntax
Icon
Converting script into functions
Icon
More about PowerShell
12
C. Resources
Icon
Resources
13
D. Creating a SQL Server VM
Icon
D. Creating a SQL Server VM
Icon
Introduction
Icon
Terminology
Icon
Downloading software
Icon
VM details and accounts
Icon
Creating an empty virtual machine
Icon
Installing Windows Server 2008 R2 as Guest OS
Icon
Installing VMWare tools
Icon
Configuring a domain controller
Icon
Creating domain accounts
Icon
Installing SQL Server 2012 on a VM
Icon
Installing sample databases
Icon
Installing PowerShell V3
14
Index
Icon
Index

Exploring the SQL Server PowerShell hierarchy

In SQL Server 2012, the original mini-shell has been deprecated, and SQLPS is now provided as a module. Launching PowerShell from SSMS now launches a Windows PowerShell session, imports the SQLPS module, and sets the current context to the item the PowerShell session was launched from. DBAs and developers can then start navigating the object hierarchy from here.

Getting ready

Log in to SQL Server 2012 Management Studio.

How to do it...

In this recipe, we will navigate the SQL Server PowerShell hierarchy by launching a PowerShell session from SQL Server Management Studio:

  1. Right-click on your instance node.
  2. Click on Start PowerShell. This will launch a PowerShell session and load the SQLPS module. This window looks similar to a command prompt, with a prompt set to the SQL Server object you launched this window from:
    How to do it...

    Note the starting path in this window.

  3. Type dir. This should give you a list of all objects directly accessible from the current server instance—in our case, from the default SQL Server instance KERRIGAN. Note that dir is an alias for the cmdlet Get-ChildItem.
    How to do it...

    This is similar to the objects you can find under the instance node in Object Explorer in SQL Server Management Studio.

    How to do it...
  4. While our PowerShell window is open, let's explore the SQL Server PSDrive, or the SQL Server data store, which PowerShell treats as a series of items. Type cd\. This will change the path to the root of the current drive, which is our SQL Server PSDrive.
  5. Type dir. This will list all Items accessible from the root SQL Server PSDrive. You should see something similar to the following screenshot:
    How to do it...
  6. Close this window.
  7. Go back to Management Studio, and right-click on one of your user databases.
  8. Click on Start PowerShell. Note that this will launch another PowerShell session, with a path that points to the database you right-clicked from:
    How to do it...
    How to do it...

    Note that the starting path of this window is different from the starting path when you first launched PowerShell in the second step. If you type dir from this location, you will see all items that are sitting underneath the AdventureWorks2008R2 database.

    How to do it...

    You can see some of the items enumerated in this screen in SQL Server Management Studio's Object Explorer, if you expand the AdventureWorks2008R2 database node.

How it works...

When PowerShell is launched through Management Studio, a context-sensitive PowerShell session is created, which automatically loads the SQLPS module. This will be evident in the prompt, which by default shows the current path of the object from which the Start PowerShell menu item was clicked.

How it works...

SQL Server 2008/2008 R2 was shipped with a SQLPS mini-shell, also referred to as SQLPS utility. This can also be launched from SSMS by right-clicking on an object from Object Explorer, and clicking on Start PowerShell. This mini-shell was designed to be a closed shell preloaded with SQL Server extensions. This shell was meant to be used for SQL Server only, which proved to be quite limiting because DBAs and developers often need to load additional snap-ins and modules in order to integrate SQL Server with other systems through PowerShell. The alternative way is to launch a full-fledged PowerShell session, and depending on your PowerShell version either load snap-ins or load the SQLPS module.

In SQL Server 2012, the original mini-shell has been deprecated. When you launch a PowerShell session from SSMS in SQL Server 2012, it launches the full-fledged PowerShell session, with the updated SQLPS module loaded by default.

SQL Server is exposed as a PowerShell drive (PSDrive), which allows for traversing of objects as if they are folders and files. Thus, familiar commands for traversing directories are supported in this provider, such as dir or ls. Note that these familiar commands are often just aliases to the real cmdlet name, in this case, Get-ChildItem.

When you launch PowerShell from Management Studio, you can immediately start navigating the SQL Server PowerShell hierarchy.

CONTINUE READING
83
Tech Concepts
36
Programming languages
73
Tech Tools
Icon Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.
Icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Icon 50+ new titles added per month and exclusive early access to books as they are being written.
SQL Server 2012 with PowerShell V3 Cookbook
End of Section 1
Next Section
notes
bookmark Notes and Bookmarks search Search in title playlist Add to playlist download Download options
font-size Font size

Change the font size

margin-width Margin width

Change margin width

day-mode Day/Sepia/Night Modes

Change background colour

Close icon Search
Country selected
Close icon Your notes and bookmarks

Confirmation

Modal Close icon
claim successful
Order Page Read Now

Buy this book with your credits?

Modal Close icon
Are you sure you want to buy this book with one of your credits?
Close
YES, BUY

Submit Your Feedback

Modal Close icon
Modal Close icon
Modal Close icon