PowerShell for SQL - Getting Started

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:

  • 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 - https://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, 3rd 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 https://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.  https://epmframework.codeplex.com/releases/view/28621  

Next:

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

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