SQL Server 2008R2 PowerShell 2.1

I am trying to create a SQL Agent job that dynamically backs up all non-corrupted SSAS databases on an instance without the use of SSIS. In my SQL Agent job, when I create a CmdExec step and point to a PowerShell script file (.ps1) like this:

powershell.exe "c:\MyPSFile.ps1" 

the job executes successfully (or at least gets far enough to only encounter logic or other syntax issues).

This approach won't work for a final solution, because there is a requirement to keep the PowerShell script internal to SQL. So I have a different CmdExec step that embeds the PowerShell script like so:

powershell.exe "import-module sqlps –DisableNameChecking

$server_name = "localhost"
$backup_location = "C:\BackupsGoHere"

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") | out-null
$server = New-Object Microsoft.AnalysisServices.Server

# Generate an array of databases to be backed up
foreach ($database in ($server.get_Databases() | Where {$_.EstimatedSize -gt 0 -and $_.Cubes.Count -gt 0})) {
    $directory_path = $backup_location + "\" + $database.Name
    if (!(Test-Path -Path $directory_path)) {
        New-Item $directory_path -type directory | out-null
    [string] $timestamp = date
    $timestamp = $timestamp.Replace(':','').Replace('/','-').Replace(' ','-')

However, when executed with the embedded script, the job errors out quickly with the following response:

The specified module 'sqlps' was not loaded because no valid module file was found in any module directory.

Why can't I reference the module from an embedded script, but doing so in a ps1 file works just fine?

  • My first guess is that somehow the context of running the PowerShell.exe with an embedded script is different than the context of when it's run from a ps1 file. I'm not sure how to confirm that or resolve it though. Is it possible to use a full path to refer to the sqlps module?
    – dev_etter
    Commented Jun 4, 2013 at 18:53
  • 1
    Why do you need to add the layers of Agent to do this? Can't you just schedule the PowerShell script in Windows? Commented Jun 4, 2013 at 19:02
  • It's a customer-defined requirement to keep this all contained within SQL. That's why I can't even store the ps1 script for the CmdExec step to reference.
    – dev_etter
    Commented Jun 4, 2013 at 19:03
  • I tried escaping the double quotes in the script. Same error.
    – dev_etter
    Commented Jun 4, 2013 at 19:04
  • On my server (SQL2008 R2) I only get the sql provider by starting Powershell in SSMS. Opening a PS console and typing $env:psmodulepath shows where it will look for modules but sqlps isn't in either C:\Windows\system32\WindowsPowerShell\v1.0\Modules\ or C:\users\Bruce\Documents\WindowsPowerShell\Modules\. You might want to try modifying the $env:psmodulepath to add the path to the sqlps module, something like X:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\
    – Bruce
    Commented Jun 4, 2013 at 20:03

1 Answer 1


Since you are using SQL Server 2008 R2 you can simply create your SQL Agent Job with the step configured as a "PowerShell" type, instead of trying to use the CmdExec. You only need to include the "meat" of your script. Since you are using the PowerShell type of a SQL Agent step it has already called the powershell.exe and imported the SQLPS module for you. So an example: enter image description here

With your code I believe you can simply configure the step as shown below: enter image description here

  • When I tried this last, I got errors because the SQL PowerShell step couldn't process some of the commands. I think, though, that I was working w/ a more robust script. Maybe this type of step will work now that the script has been stripped down. It's worth a try.
    – dev_etter
    Commented Jun 7, 2013 at 19:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.