locked
Running powershell script from Agent RRS feed

  • Question

  • First of all, I am a powershell novice at best - I can do simple things, and usually only with a reference open.

    I found a useful powershell script from Idera that backs up OLAP databases.  I can run this script from the command-line via SQLPS, but various attempts to run this from SQL Agent have been fruitless.  The script is as follows:

        ## ===================================================================== 
        ## Title       : Backup-SSAS 
        ## Description : Backup all Analysis Server databases 
        ## Author      : Idera 
        ## Date        : 6/27/2008 
        ## Input       : -serverInstance <server\inst> 
        ##               -backupDestination <drive:\x\y | \\unc\path> 
        ##               -retentionDays <n> 
        ##               -logDir <drive:\x\y | \\unc\path> 
        ##               -verbose  
        ##               -debug     
        ## Output      : write backup files (*.abf) 
        ##                   create log file of activity 
        ## Usage            : PS> . Backup-SSAS -serverInstance MyServer -Destination C:\SSASbackup  
        ##                                 -RetentionDays 2 -LogDir C:\SSASLog -v -d 
        ## Notes            : Original script attributed to Ron Klimaszewski 
        ## Tag            : Microsoft Analysis Server, SSAS, backup 
        ## Change Log  : 
        ## ===================================================================== 
         
        param  
        (  
            [string]$ServerInstance = "(local)",  
            [string]$BackupDestination,  
            [int]$RententionDays = 2,  
            [string]$LogDir,  
            [switch]$verbose, 
            [switch]$debug 
        ) 
         
        function main() 
        { 
            if ($verbose) {$VerbosePreference = "Continue"} 
            if ($debug) {$DebugPreference = "Continue"} 
            Backup-SSAS $serverInstance $backupDestination $retentionDays $logDir 
        } 
         
        function Backup-SSAS($serverInstance, $backupDestination, $retentionDays, $logDir) 
        { 
            trap [Exception]  
            { 
                write-error $("TRAPPED: " + $_.Exception.Message); 
                continue; 
            } 
             
            # Force a minimum of two days of retention  
            # TIP: using PS "less than" operator 
            if ($RetentionDays -lt 2 )  
            { 
                $RetentionDays = 2  
            }  
             
            # Load Microsoft Analysis Services assembly, output error messages to null 
            [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") | Out-Null 
             
            # Declare SSAS objects with strongly typed variables 
            [Microsoft.AnalysisServices.Server]$SSASserver = New-Object ([Microsoft.AnalysisServices.Server])  
            [Microsoft.AnalysisServices.BackupInfo]$serverBackup = New-Object ([Microsoft.AnalysisServices.BackupInfo])  
             
            # Connect to Analysis Server with specified instance 
            $SSASserver.Connect($ServerInstance)  
             
            # Set Backup destination to Analysis Server default if not supplied 
            # TIP: using PowerShell "equal" operator 
            if ($backupDestination -eq "")  
            { 
                Write-Debug "Setting the Destination parameter to the BackupDir parameter"  
                $BackupDestination = $SSASserver.ServerProperties.Item("BackupDir").Value  
            }  
             
            # Test for existence of Backup Destination path 
            # TIP: using PowerShell ! operator is equivalent to "-not" operator, see below 
            if (!(test-path $backupDestination))  
            { 
                Write-Host Destination path `"$backupDestination`" does not exists.  Exiting script.  
                exit 1  
            }  
            else  
            { 
                Write-Host Backup files will be written to `"$backupDestination`"  
            }  
             
            # Set Log directory to Analysis Server default if not applied 
            if ($logDir -eq "")  
            { 
                Write-Debug "Setting the Log directory parameter to the LogDir parameter"  
                $logDir = $SSASserver.ServerProperties.Item("LogDir").Value  
            }  
             
            # Test for existence of Log directory path 
            if (!(test-path $logDir))  
            { 
                Write-Host Log directory `"$logDir`" does not exists.  Exiting script.  
                exit 1  
            }  
            else  
            { 
                Write-host Logs will be written to $logDir  
            }  
             
            # Test if Log directory and Backup destination paths end on "\" and add if missing 
            # TIP: using PowerShell "+=" operator to do a quick string append operation 
            if (-not $logDir.EndsWith("\"))  
            { 
                $logDir += "\" 
            }  
             
            if (-not $backupDestination.EndsWith("\"))  
            { 
                $backupDestination += "\" 
            }  
             
            # Create Log file name using Server instance 
            [string]$logFile = $logDir + "SSASBackup." + $serverInstance.Replace("\","_") + ".log"  
            Write-Debug "Log file name is $logFile" 
             
            Write-Debug "Creating database object and set options..." 
            $dbs = $SSASserver.Databases  
            $serverBackup.AllowOverwrite = 1  
            $serverBackup.ApplyCompression = 1  
            $serverBackup.BackupRemotePartitions = 1  
             
            # Create backup timestamp 
            # TIP: using PowerShell Get-Date to format a datetime string 
            [string]$backupTS = Get-Date -Format "yyyy-MM-ddTHHmm"  
             
            # Add message to backup Log file 
            # TIP: using PowerShell to output strings to a file 
            Write-Debug "Backing up files on $serverInstance at $backupTS" 
            "Backing up files on $ServerInstance at $backupTS" | Out-File -filepath $LogFile -encoding oem -append  
             
            # Back up the SSAS databases 
            # TIP: using PowerShell foreach loop to enumerate a parent-child object 
            foreach ($db in $dbs)  
            { 
                $serverBackup.file = $backupDestination + $db.name + "." + $backupTS + ".abf"  
             
                # TIP: using mixed string literals and variable in a Write-Host command 
                Write-Host Backing up $db.Name to $serverBackup.File  
                $db.Backup($serverBackup)  
                 
                if ($?) {"Successfully backed up " + $db.Name + " to " + $serverBackup.File | Out-File -filepath $logFile -encoding oem -append}  
                else {"Failed to back up " + $db.Name + " to " + $serverBackup.File | Out-File -filepath $logFile -encoding oem -append}  
            }  
             
            # Disconnect from Analysis Server 
            $SSASserver.Disconnect()  
             
            # Clear out the old files and files backed up to the Log file 
            Write-Host Clearing out old files from $BackupDestination  
            [int]$retentionHours = $retentionDays * 24 * - 1  
            "Deleting old backup files" | Out-File -filepath $logFile -encoding oem -append  
             
            # TIP: using PowerShell get-childitem (get child items for matching location) and pipe to 
            #        where-object (selecting certain ones based on a condition)  
            get-childitem ($backupDestination + "*.abf") | where-object {$_.LastWriteTime -le [System.DateTime]::Now.AddHours($RetentionHours)} | Out-File -filepath $logFile -encoding oem -append  
            get-childitem ($backupDestination + "*.abf") | where-object {$_.LastWriteTime -le [System.DateTime]::Now.AddHours($RetentionHours)} | remove-item  
        } 
         
        main 

    Like I said, if I bring up the command prompt and use SQLPS, I can run the following without errors:

    . "D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\PowerShell\BackupSSAS.ps1"

    I try a similar command in a SQL Agent Job (command-type of Powershell):

    $ {. "D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\PowerShell\BackupSSAS.ps1"}
    This results in the following errors:
    The job script encountered the following errors. These errors did not stop the script:
    A job step received an error at line 35 in a PowerShell script. The corresponding line is 'Backup-SSAS $serverInstance $backupDestination $retentionDays $logDir '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'TRAPPED: Exception calling "get_Item" with "1" argument(s): "The 'ServerProperty' with 'Name' = 'BackupDir' doesn't exist in the collection."
    A job step received an error at line 35 in a PowerShell script. The corresponding line is 'Backup-SSAS $serverInstance $backupDestination $retentionDays $logDir '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'TRAPPED: Cannot bind argument to parameter 'Path' because it is an empty string.
    A job step received an error at line 35 in a PowerShell script. The corresponding line is 'Backup-SSAS $serverInstance $backupDestination $retentionDays $logDir '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'TRAPPED: Exception calling "get_Item" with "1" argument(s): "The 'ServerProperty' with 'Name' = 'LogDir' doesn't exist in the collection."
    A job step received an error at line 35 in a PowerShell script. The corresponding line is 'Backup-SSAS $serverInstance $backupDestination $retentionDays $logDir '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'TRAPPED: Cannot bind argument to parameter 'Path' because it is an empty string.
    A job step received an error at line 35 in a PowerShell script. The corresponding line is 'Backup-SSAS $serverInstance $backupDestination $retentionDays $logDir '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'TRAPPED: Cannot invoke this function because the current host does not implement it.
    
    
    Any ideas why this works via sqlps command-line, but not as part of SQL Agent?
    Tuesday, February 28, 2012 8:38 PM

Answers

  • I've determined that the problem had less to do with what accounts were running the scripts and more to do with how they are run.  The problem had to do with the numerous Write-Host statements in the script.  This article I stumbled upon explains it well: The use of Write-Host and SQL Server Agent PowerShell job steps

    I wrote a Log-Output function and replaced all occurrences of Write-Host:

    Write-Host Destination path `"$backupDestination`" does not exists. Exiting script. # becomes

    Log-Output "Destination path $backupDestination does not exists. Exiting script."

    Also, not sure of the syntax of that write-host command.  Is that some older syntax?  It works when writing to the host, but when passing directly to Write-Output it threw errors, which is why i changed the syntax when I replaced all the write-host commands with Log-Output.

    With all of that, the script still doesn't do what it is supposed to when run from SQL Agent (which possibly IS a result of account security issues), but at least the mysterious errors have stopped appearing and I can proceed to debug this.

    Thanks to all that replied!

    • Marked as answer by Brandon L Wednesday, February 29, 2012 7:18 PM
    • Unmarked as answer by Brandon L Monday, February 29, 2016 2:36 PM
    • Marked as answer by Brandon L Monday, February 29, 2016 2:37 PM
    Wednesday, February 29, 2012 7:18 PM

All replies

  • Hi Brandon,

    This issue is usually caused by the fact that the execution security context of the job is different from the security context when you manually run PS in command-line via SQLPS. The commands in SQL PS are executed as the current login. However by default every job step runs in the security context of SQL Server Agent Service account. If the SQL Server Agent Service account is different from the login account and if it does not have enough permission to execute the script.

    Though you can simply give the SQL Server Agent Service account permissions to execute the script. But for SQL Server Agent Service account, it is recommended to give it the least amount of privileges.

    You can use one of the following three methods to resolve this issue:
    1. The first method to resolve this issue is to change the job owner to a login who has enough permission to execute the  script but not the sys admin.
    2. The second method is specifying the "Run as User" option for the Transact-SQL job step in case you have several job steps and you do not want to change the job owner so as not to impact other job steps execution. You can go to the Advanced options of the job step, and input an user to the "Run as User" field. Ensure you are a sysadmin to edit this option.


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Proposed as answer by Janos BerkeMVP Wednesday, February 29, 2012 6:00 AM
    Wednesday, February 29, 2012 5:52 AM
  • Hi Brandon,

    I'd like to add one more option to Peja's answer: you may use proxy account on SQL 2008 for Power Shell job steps. You may assign the proper permission to the proxy credential and it may work. Using proxy accounts is worth to consider as you can follow the principle of least privilege.

    I hope it helps.

    J.


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog

    Wednesday, February 29, 2012 6:00 AM
  • Proxies would have been my first option, but due to the draconian (and often misguided) security measures that our company employs in our environments, we have never been able to get the simplest of proxies to work at all.

    Thanks!

    Wednesday, February 29, 2012 6:28 PM
  • I've determined that the problem had less to do with what accounts were running the scripts and more to do with how they are run.  The problem had to do with the numerous Write-Host statements in the script.  This article I stumbled upon explains it well: The use of Write-Host and SQL Server Agent PowerShell job steps

    I wrote a Log-Output function and replaced all occurrences of Write-Host:

    Write-Host Destination path `"$backupDestination`" does not exists. Exiting script. # becomes

    Log-Output "Destination path $backupDestination does not exists. Exiting script."

    Also, not sure of the syntax of that write-host command.  Is that some older syntax?  It works when writing to the host, but when passing directly to Write-Output it threw errors, which is why i changed the syntax when I replaced all the write-host commands with Log-Output.

    With all of that, the script still doesn't do what it is supposed to when run from SQL Agent (which possibly IS a result of account security issues), but at least the mysterious errors have stopped appearing and I can proceed to debug this.

    Thanks to all that replied!

    • Marked as answer by Brandon L Wednesday, February 29, 2012 7:18 PM
    • Unmarked as answer by Brandon L Monday, February 29, 2016 2:36 PM
    • Marked as answer by Brandon L Monday, February 29, 2016 2:37 PM
    Wednesday, February 29, 2012 7:18 PM