Day 9 of 31 Days of SQL Server Backup and Restore using PowerShell: Automating with SQL Server Agent

So far in this series we’ve looked a variety of ways you can use PowerShell to run your SQL Server backups, but we’ve not touched on how you can automate them. That is going to be the topic of the next 2 posts. Today we’ll look at automating the scripts with SQL Server Agent, and tomorrow we’ll look at doing it with Task Scheduler.

SQL Agent has been able to schedule PowerShell jobs since 2008, and the basic method hasn’t changed. Screenshots here are from SQL Server 2012:

PowerShell is just another Step type in SQL Server you can pick from the dropdown:

New job step for PowerShell SQL Agent task

And then you can enter your PowerShell into the box provided. The Open button allows you to browse for existing files, but only puts the current content into the box:


Here I’ve used the basic looped database backup script from Day 2. Once crucial point to spot here, is that by default the script will be run as the “SQL Server Agent Service Account”. Now if you’re following SQL Server best practice, then this account won’t have much access to your local disks or to network shares, which is likely to cause your backup script to fail.

The way around this is to create a SQL Server Agent Proxy for PowerShell, and associate this with a credential that does have access to your backup locations, which is following best security practice.

The other problem that can occur is that so far we have incorporated very little checking and error reporting into our scripts. This means that if an error occurs then you a likely to get back less than useful error messages. Looking through the SQL Server errorlog can often provide some more details. I’ll be covering checking and error reporting in more detail towards the end of this series.

If you’d rather run from saved .ps1 PowerShell scripts then you’ll need to use the “Operating system (CmdExec”) Step type:


You can then enter the call to your script into the Command box. Note that we are actually calling PowerShell.exe and then passing our script in as a parameter. You can’t call a PowerShell script directly.


The same security restrictions apply to this step as to the PowerShell one, so you may have to create a second SQL Server Agent Proxy or grant CmdExec to an existing proxy.

Now you’ve created you job you can schedule it like any other SQL Server job.

So this is all great if you’ve got SQL Server Agent, but what about if you’re using SQL Server Express and don’t have SQL Agent to schedule your backups? Well, we’ll cover scheduling PowerShell with Windows Task Scheduler tomorrow

This post is part of a series posted between 1st September 2013 and 3rd October 2013, an index for the series is available here.

Tagged ,

Leave a Reply

Your email address will not be published. Required fields are marked *