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

• SQLEngine

  SSCommitted

  Points: 1633

  More actions

  January 5, 2010 at 2:29 pm

  #1099250

  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

  March 29, 2011 at 12:44 pm

  #1305090

  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

  September 19, 2011 at 9:40 am

  #1383364

  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.