In my prior post about Using Powershell to automate SQL Server database restore, I mentioned a follow-up post on setting this up.
It actually worked fine, until I upgraded SQL Server 2008 R2 to SQL 2012. Then it broke. More on that later…
In essence there are two methods for running PS scripts as an SQL Agent task.
From SQL Agent, Create a new Job, give it a name
Click Steps, then New, for the New Job Step. Name the step, choose Powershell, then Run as (choose an account that has proper permissions, then Command Open… chose the Powershell script, then OK. The contents will now be posted in the command window.
Click OK. Then click Schedules page to schedule the operation.
After the update to SQL 2012, the restore script posted earlier stopped working. The errors indicated that it could not read the path to the UNC. That meant one of two things; permissions or the wrong default PS was being invoked, as SQLPS is also installed on this server.
Turns out, SQLPS was now being launched by default. To correct, save the PS script as a local file. Then create the SQL Agent task with a full path to Powershell.exe. Path may vary depending on install path. Add the path to the script as the argument. Problem solved.
Related: Use PowerShell to Create Agent Jobs