Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

4 Convenient Ways To Run PowerShell Scripts


By:   |   Last Updated: 2016-03-03   |   Comments (1)   |   Related Tips: More > 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.

Run PowerShell Scripts with PowerShell ISE

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:

  1. 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.
  2. This approach is for live function calls, not set function calls. In the latter, I prefer related code together.
  3. 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


Last Updated: 2016-03-03


next webcast button


next tip button



About the author
MSSQLTips author Tim Smith Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

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.



    



Monday, March 07, 2016 - 12:51:53 PM - AZJim Back To Top

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!


Learn more about SQL Server tools