PowerShell for SQL - Getting Started

Cindy Gross, Dedicated Support Engineer

You may want to use PowerShell to automate SQL activities such as backups. There are various ways to automate backups. For example, you can centralize the backup calls on an admin server which has some type of list of instances and databases and possibly varying schedules/types of backups for each. Another method is to use an admin server to push backup jobs to newly installed SQL instances. A variation of the 2^nd is to periodically scan for new databases and/or databases without backups and/or databases that don’t meet some standard backup schedule and push out backup jobs. I’m not sure how automated or complex your backup plans are now. I’ve seen a wide range such as:

• Manually schedule backups for each database as it is added (and hope you don’t miss one)
• When you install the instance run a script to generate backup jobs for each database. Then periodically scan for new databases/databases not backed up and add backup jobs.
• Schedule one backup job per instance to backup all local databases (either all on the same schedule or read a table to find the type/interval for each database on that instance)
• Use single server or multi-server maintenance plans (not something I personally recommend in an enterprise - http://sqlserverpedia.com/blog/sql-server-management/maintenance-plans-vs-t-sql-scripts/)
• Use a central scheduling system (master-target / multiserver administration via SQL jobs, UCP, 3^rd party tools, etc.) with varying levels of complexity
• Use a central Central Management Server (CMS) and/or a custom central admin databases to track which databases need which backup schedules and generate/check appropriate jobs

My next two blogs will have some details about the first steps with Powershell. Most of the SQL related operations you can do from Powershell are done by calling SMO http://msdn.microsoft.com/en-us/library/ms162169.aspx. Allen White does training on SMO, and I'm sure you can find others who do so as well.

• Loading SMO Assemblies into PowerShell http://sqlblog.com/blogs/allen_white/archive/2008/12/07/loading-smo-assemblies-into-powershell.aspx
• Allen White’s Powershell blogs: http://sqlblog.com/blogs/allen_white/archive/tags/PowerShell/default.aspx

Here are some backup samples that use PowerShell:

• Understanding and Using PowerShell Support in SQL Server 2008 (Allen White) http://msdn.microsoft.com/en-us/library/dd938892(SQL.100).aspx
• SQL Server PowerShell : How to Backup SQL Server Databases Using SMO and PowerShell http://www.sqlmusings.com/2009/05/31/how-to-backup-sql-server-databases-using-smo-and-powershell/
• The unsupported/shareware SQLPSX SQL Server Powershell Extensions have a Invoke-SqlBackup cmdlet you can load into PowerShell http://sqlpsx.codeplex.com/

The Enterprise Policy Management (EPM) Framework is a classic example of how to use the new SQL Server 2008 Central Management Server (CMS) to automate tasks across multiple servers. In this case it is automating Policy Based Management (PBM) with PowerShell but the concept could apply to many types of automation.  http://epmframework.codeplex.com/releases/view/28621  

Next:

PowerShell for SQL Server - Basics http://blogs.msdn.com/b/cindygross/archive/2011/09/23/powershell-for-sql-server-basics.aspx

SQL PowerShell Demo - Intro http://blogs.msdn.com/b/cindygross/archive/2011/09/23/sql-powershell-demo-intro.aspx