Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
$server.connect($server_name)

# 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(' ','-')
    $database.Backup("$directory_path\$database-$timestamp.abf")
}
$server.disconnect()"

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?

share|improve this question
    
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 Jun 4 '13 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? –  Aaron Bertrand Jun 4 '13 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 Jun 4 '13 at 19:03
    
I tried escaping the double quotes in the script. Same error. –  dev_etter Jun 4 '13 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 Jun 4 '13 at 20:03
add comment

1 Answer

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

share|improve this answer
    
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 Jun 7 '13 at 19:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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