4 Convenient Ways To Run PowerShell Scripts

By: Tim Smith | Comments (1) | Related: > PowerShell

Problem

One question I get asked a lot is where I run PowerShell scripts. The answer varies by the specific situation, environment, and purpose. In this tip I look at three convenient ways to call PowerShell scripts, as well as a fourth way that includes other applications which may be the appropriate place to call them for your environment.

Solution

Run PowerShell Scripts with a SQL Server Agent Job

The SQL Server Job Agent allows you to run PowerShell scripts when you select this option. One thing to watch for is the version of SQL Server and some functionality of PowerShell scripts, which may be incompatible. I've run into errors with PowerShell scripts running from SQL Server agent because of incompatibility.

SQL Server Agent Job Step for PowerShell

Consider that the user running this job will need permissions to what the script does. Relative to how you configure security (or the environment's security overall), you may not want a user to run PowerShell scripts.

Run PowerShell Scripts with PowerShell ISE

PowerShell ISE is a slightly more interactive scripting tool than a command window because in versions 3.0 and above we have access to intellisense and the coloring of a script allows a coder to quickly determine what's wrong or what needs to be added.

Since I repeat many scripts and calls to scripts, I can reference a full list of functions within a script (code block one below) or I can reference related functions in multiple script sources (code block two below).

### Referencing one script with many functions:

. D:\PS\Functions.ps1

### Referencing multiple scripts with related functions:

. D:\PS\SMOFunctions.ps1
. D:\PS\RMOFunctions.ps1

A couple of points:

Any daily and repetitive task is useful here. Consider getting the last date of a statistics update for a table, or shrinking a log. Once the function is coded, if automation is out of the question (if), then calling the function with the appropriate parameters quickly solves the issue.
This approach is for live function calls, not set function calls. In the latter, I prefer related code together.
Consider the use of configuration tables and (or) files, as these can allow for some of the live scripts to be saved and called without parameters. I can update the configuration files or tables and still call the same functions with the changed parameters.

Run PowerShell Scripts with Windows Task Scheduler

The below screenshot is from a home edition of Windows 7, but Task Scheduler comes with Windows Server as well. Task Scheduler allows scheduling tasks, such as exe programs, PS scripts, etc.

To run a PowerShell script, set the appropriate name and time, and under the Actions tab, put "PowerShell.exe" in the Program/script: block and in the Add arguments (optional): block, type -file "D:\PS\MyPSFile.ps1" with the location of the PowerShell file inside the quotations. Like other approaches, consider what user is running this along with what permissions that user has.

Run PowerShell Scripts with Windows Task Scheduler

Run PowerShell Scripts with an Application

TeamCity and many other applications can call PowerShell scripts, in addition to many environments using C# to call PowerShell scripts. PowerShell scripts can also be built in modules, and the modules can be referenced and accessed. In these environments, the preference is to use these tools to call them and we'll seldom run into a situation where we must fully re-write the ones we use.

Next Steps

First, know the security of your environment and the server you want to run scripts.
If doing daily tasks with scripts, consider function lists - whether related or combined - to allow for easy completion when these tasks arise.
For automation, consider running scripts in a scheduled program, such as TeamCity or Task Scheduler.
Check out these other PowerShell tips

About the author

Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, March 7, 2016 - 12:51:53 PM - AZJim

Tim ... thanks for the tip. I consider myself a PowerShell beginner so I am always interested in PowerShell to help with server management. One thing I require is a script to "reach out" to all my servers and run standard SQL statements. For instance, if you want to see what databases haven't had a backup taken in the last 72 hours, you could run the following from ISE:

#
cls
$SQLCmd = get-content "C:\PSFiles\PS1Executables\@1FindDBsMissingBackupLast72Hours.txt"
foreach ($svr in get-content "C:\PSFiles\ServerList\AllProd.txt")
{
$con = "server=$svr;database=master;Integrated Security=sspi"
$da = new-object System.Data.SQLClient.SQLDataAdapter ($SQLCmd, $con)
$dt = new-object System.Data.DataTable
$da.fill($dt) | out-null
$svr
$dt
}

Where the $SQLCmd is a text file with the common statement you want to have executed. Your foreach statement would have a list of servers you target for the SQL.

For anyone wanting to see the SQL I run to interrogate for the 72 hour gap, here it is:

SELECT bs.server_name                                                     AS 'Server'
     , db.name                                                            AS 'DatabaseName'
     , ISNULL(CONVERT(VARCHAR, max(bs.backup_finish_date), 120), 'Never') AS 'LastBackupDateTime'
FROM      sys.databases      db
LEFT JOIN msdb.dbo.backupset bs
ON db.name = bs.database_name
and bs.type IN ('D')
WHERE db.name NOT IN ('tempdb')
GROUP BY db.name, bs.server_name
HAVING DATEDIFF(HH, max(bs.backup_finish_date), GETDATE()) > 72
    OR DATEDIFF(HH, max(bs.backup_finish_date), GETDATE()) IS NULL
ORDER BY bs.server_name, db.name ;

You can't have SQL comments in this file -- it screws with the PowerShell parser!