Analysis Server 2005 Backup Powershell Script

Question

Analysis Server 2005 Backup Powershell Script

Ron Klimaszewski

SSC Eights!

Points: 911
More actions
October 1, 2007 at 6:15 am

#177864

Comments posted to this topic are about the item Analysis Server 2005 Backup Powershell Script

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

SQLEngine SSCommitted Points: 1633 More actions · Answer 1

Ron, I like your script a lot, and tried to adjust it for SQL server backup., I load [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") and then try the following:

# Declare SQL objects with strongly typed variables

[Microsoft.SqlServer.SMO.Server]$SQLserver = New-Object ([Microsoft.SqlServer.SMO.Server])

# Connect to SQL Server with specified instance

$SQLserver.Connect($Servername)

but am getting a error:

Unable to find type [Microsoft.SqlServer.SMO.Server]:

or if I try to load just LoadWithPartialName("Microsoft.SqlServer"] that errors out too.

I attach my script here as SQLCMD_v4.ps1.txt

supatra siritanabodeekul Grasshopper Points: 21 More actions · Answer 2

Hi Ron,

I got your script downloaded directly from Powershell Idera site. The script is working great through command prompt windows or Powershell window directly. However, it does not work when I am trying to schedule it into the sql server agent. Btw, I am new to the Powershell, so not sure what do I need to do beside putting in the job step and choose powershell. I also try to set the policy unrestricted, it still threw me the same error. Here is the error I have received.

Executed as user: 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: Cannot invoke this function because the current host does not implement it. ' 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. ' 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. ' 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. '.

I am running under SQL server 2008 and Powershell 1.0. It's just work without the sql server agent. Do I have to do anything before adding this script into the job steps? Can you advise? Your reply will be much appreciate as I am trying to set this up into all our SSAS environment here.

SSDBA2k5 SSC-Addicted Points: 457 More actions · Answer 3

Hello,

There are some commands you cannot run from an SQL Server job because there is no console, for the example write-host cmdlet.

http://blogs.msdn.com/b/mwories/archive/2009/09/30/the-use-of-write-host-and-sql-server-agent-powershell-job-steps.aspx

Hope this helps.

Regards.

Victor.