Book Image

SQL Server 2012 with PowerShell V3 Cookbook

By : Donabel Santos
Book Image

SQL Server 2012 with PowerShell V3 Cookbook

By: Donabel Santos

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.

Related Content you might be interested in

Current Title:

Small book image
SQL Server 2012 with PowerShell V3 Cookbook
Donabel Santos
Oct, 2012 | 634 pages
No titles found
Table of Contents (21 chapters)
SQL Server 2012 with PowerShell V3 Cookbook
SQL Server 2012 with PowerShell V3 Cookbook
Credits
Credits
About the Author
About the Author
Acknowledgement
Acknowledgement
About the Reviewers
About the Reviewers
www.PacktPub.com
www.PacktPub.com
Preface
Preface
Free Chapter
1
Getting Started with SQL Server and PowerShell
Getting Started with SQL Server and PowerShell
Introduction
Before you start: Working with SQL Server and PowerShell
Working with the sample code
Exploring the SQL Server PowerShell hierarchy
Installing SMO
Loading SMO assemblies
Discovering SQL-related cmdlets and modules
Creating a SQL Server instance object
Exploring SMO server objects
2
SQL Server and PowerShell Basic Tasks
SQL Server and PowerShell Basic Tasks
Introduction
Listing SQL Server instances
Discovering SQL Server services
Starting/stopping SQL Server services
Listing SQL Server configuration settings
Changing SQL Server instance configurations
Searching for database objects
Creating a database
Altering database properties
Dropping a database
Changing a database owner
Creating a table
Creating a view
Creating a stored procedure
Creating a trigger
Creating an index
Executing a query / SQL script
Performing bulk export using Invoke-Sqlcmd
Performing bulk export using bcp
Performing bulk import using BULK INSERT
Performing bulk import using bcp
3
Basic Administration
Basic Administration
Introduction
Creating a SQL Server instance inventory
Creating a SQL Server database inventory
Listing installed hotfixes and service packs
Listing running/blocking processes
Killing a blocking process
Checking disk space usage
Setting up WMI Server event alerts
Detaching a database
Attaching a database
Copying a database
Executing a SQL query to multiple servers
Creating a filegroup
Adding secondary data files to a filegroup
Moving an index to a different filegroup
Checking index fragmentation
Reorganizing/rebuilding an index
Running DBCC commands
Setting up Database Mail
Listing SQL Server jobs
Adding a SQL Server operator
Creating a SQL Server job
Adding a SQL Server event alert
Running a SQL Server job
Scheduling a SQL Server job
4
Security
Security
Introduction
Listing SQL Server service accounts
Changing SQL Server service account
Listing authentication modes
Changing authentication mode
Listing SQL Server log errors
Listing failed login attempts
Listing logins, users, and database mappings
Listing login/user roles and permissions
Creating a login
Assigning permissions and roles to a login
Creating a database user
Assigning permissions to a database user
Creating a database role
Fixing orphaned users
Creating a credential
Creating a proxy
5
Advanced Administration
Advanced Administration
Introduction
Listing facets and facet properties
Listing policies
Exporting a policy
Importing a policy
Creating a condition
Creating a policy
Evaluating a policy
Enabling/disabling change tracking
Running and saving a profiler trace event
Extracting the contents of a trace file
Creating a database master key
Creating a certificate
Creating symmetric and asymmetric keys
Setting up Transparent Data Encryption (TDE)
6
Backup and Restore
Backup and Restore
Introduction
Changing database recovery model
Listing backup history
Creating a backup device
Listing backup header and file list information
Creating a full backup
Creating a backup on mirrored media sets
Creating a differential backup
Creating a transaction log backup
Creating a filegroup backup
Restoring a database to a point in time
Performing an online piecemeal restore
7
SQL Server Development
SQL Server Development
Introduction
Inserting XML into SQL Server
Extracting XML from SQL Server
Creating an RSS feed from SQL Server content
Applying XSL to an RSS feed
Storing binary data into SQL Server
Extracting binary data from SQL Server
Creating a new assembly
Listing user-defined assemblies
Extracting user-defined assemblies
8
Business Intelligence
Business Intelligence
Introduction
Listing items in your SSRS Report Server
Listing SSRS report properties
Using ReportViewer to view your SSRS report
Downloading an SSRS report in Excel and PDF
Creating an SSRS folder
Creating an SSRS data source
Changing an SSRS report's data source reference
Uploading an SSRS report to Report Manager
Downloading all SSRS report RDL files
Adding a user with a role to an SSRS report
Creating folders in an SSIS package store and MSDB
Deploying an SSIS package to the package store
Executing an SSIS package stored in the package store or File System
Downloading an SSIS package to a file
Creating an SSISDB catalog
Creating an SSISDB folder
Deploying an ISPAC file to SSISDB
Executing an SSIS package stored in SSISDB
Listing SSAS cmdlets
Listing SSAS instance properties
Backing up an SSAS database
Restoring an SSAS database
Processing an SSAS cube
9
Helpful PowerShell Snippets
Helpful PowerShell Snippets
Introduction
Documenting PowerShell script for Get-Help
Getting a timestamp
Getting additional error messages
Listing processes
Getting aliases
Exporting to CSV and XML
Using Invoke-Expression
Testing regular expressions
Managing folders
Manipulating files
Searching for files
Reading an event log
Sending e-mail
Embedding C# code
Creating an HTML report
Parsing XML
Extracting data from a web service
Using PowerShell Remoting
SQL Server and PowerShell CheatSheet
SQL Server and PowerShell CheatSheet
Learning PowerShell
PowerShell V2 versus V3 Where-Object syntax
Changing execution policy
Running a script
Common aliases
Displaying output
Special characters
Special variables
Common operators
Common date-time format strings
Here-string
Common regex characters and patterns
Arrays and hash tables
Arrays and loops
Logic
Functions
Common Cmdlets
Import SQLPS module
Add SQL Server Snapins
Add SQL Server Assemblies
Getting credentials
Running and blocking SQL Server processes
Read file into an array
SQL Server-Specific Cmdlets
Invoke-SqlCmd
Create SMO Server Object
Create SSRS Proxy Object
Create SSIS Object (SQL Server 2005/2008/2008R2)
Create an SSIS Object (SQL Server 2012)
Create SSAS Object
PowerShell Primer
PowerShell Primer
Introduction
What is PowerShell, and why learn another language
Setting up the Environment
Running PowerShell scripts
Basics—points to remember
Scripting syntax
Converting script into functions
More about PowerShell
Resources
Resources
Resources
Creating a SQL Server VM
Creating a SQL Server VM
Introduction
Terminology
Downloading software
VM details and accounts
Creating an empty virtual machine
Installing Windows Server 2008 R2 as Guest OS
Installing VMWare tools
Configuring a domain controller
Creating domain accounts
Installing SQL Server 2012 on a VM
Installing sample databases
Installing PowerShell V3
Index
Index

Listing backup history

In this recipe, we will list the backup history for a SQL Server instance.

How to do it...

  1. Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

  2. Import the SQLPS module as follows:

    #import SQL Server module
Import-Module SQLPS -DisableNameChecking

  3. Add the following script and run:

    $instanceName = "KERRIGAN"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

#display date of last backup
$server.Databases | 
Select Name, RecoveryModel, LastBackupDate, LastDifferentialBackupDate, LastLogBackupDate | 
Format-Table -AutoSize

    Your result should look similar to the following screenshot:

    Note that when you see a date of 1/1/0001 12:00:00 AM, then it means no backup has ever been taken for that database.

How it works...

Listing the backup history is a simple task, using a little bit of PowerShell and SMO. After you get a database handle, you can display the last backup dates...

Previous Section
End of Section 4
Next Section