Scheduling a SQL Server PowerShell script using the Task Scheduler

By:   |   Updated: 2011-01-28   |   Comments   |   Related: More > PowerShell

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".

how to schedule a powershell script with task scheduler mmc snap in

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.

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.

make sure 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"

add powershell.exe in the program/script textbox

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

encapsulate the commnad in .bat file

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).

Next Steps


Last Updated: 2011-01-28


get scripts

next tip button



About the author
MSSQLTips author Diana Moldovan Diana Moldovan is a DBA and data centric applications developer with 6 years of experience covering SQL 2000, SQL 2005 and SQL 2008.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools