Scheduling a SQL Server PowerShell script using the Task Scheduler

Problem

Even if your SQL Server edition does not include the SQL Server Agent service (if you use SQL Express, for instance), you can still automate administration tasks without using any 3rd party products. In this tip I'll describe how to schedule a PowerShell script with the Task Scheduler MMC snap-in. This applies to Windows 2008 Server, Windows 2008 Server R2 and Windows 7.

Solution

Following is the step by step process to setup Task Scheduler to run a PowerShell script. In this example I show how you can backup a database, but this can be applied to any SQL Server task you want to automate and have scheduled on a regular basis.
1. There are several ways to start the Task Scheduler, such as:

• Go to Start ---> Control Panel ---> Administrative Tools ---> Task Scheduler
• Go to Start ---> Run ---> Taskschd.msc
• Go to the Configuration Node of the Server Manager (Windows Server 2008, 2008 R2) or to Computer Manager ---> System Tools (Windows 7 Pro) and click on Task Scheduler

2. Right click the Task Scheduler Library node and create a new folder. Name it for example "MyScheduledTasks"
3. Right Click on MyScheduledTasks and choose "Create Task". Name the task "PSTask".

4. Click on "Change User or Group" if you need to change the account under which the task is running. Check "Run whether user is logged on or not" - the job must run even if the user is not logged on. Configure the task for your operating system.

5. In the Triggers tab, select Begin the task ---> "On a schedule". Set, for example, a daily schedule - the job will run every day at 12:00 AM. Make sure that the job is enabled.

6. In the Action tab you'll specify the work to be done when the task is run. For example, you'll run a PowerShell script which makes a full backup of a database. The script takes as a parameter ("dbToBackup") the name of the database to back up. Here is the PowerShell script. I saved this script as file "BackupDB.ps1" in folder "C:\PowerShellScripts\".

param([string] $dbToBackup)
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
| Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")
| Out-Null
$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "(local)"
$backupDirectory = $server.Settings.BackupDirectory
$db = $server.Databases[$dbToBackup]
$dbName = $db.Name
$timestamp = Get-Date -format yyyyMMddHHmmss
$smoBackup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
$smoBackup.Action = "Database"
$smoBackup.Database = $dbName
$smoBackup.Devices.AddDevice($backupDirectory + "\" + $dbName + "_"
+ $timestamp + ".bak", "File")
$smoBackup.SqlBackup($server)

Select Action ---> Start a program
You can either:
- add "Powershell.exe" in the "Program/script" textbox and the .ps1 script to run as argument along with the database to backup - in this case: C:\PowerShellScripts\BackupDB.ps1 -dbToBackup "MyDB"

- or encapsulate the command in a .bat file. The BAT file will contain this line: Powershell.exe -command C:\PowerShellScripts\BackupDB.ps1 -dbToBackup "MyDB"

For the purpose of this example I've left the "Conditions" and "Settings" tabs unchanged. Make sure that the account which runs the job (see step 4) has the necessary permissions to backup the database and permissions to write the backup file in the backup location.
7. When you complete the task setup and save the scheduled task you will need to provide the password of the account which will run the task (see step 4).