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 2nd 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:
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.
Here are some backup samples that use PowerShell:
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
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