Sql-server – Run a PowerShell script on a different server – from inside a SQL Server job

linked-servermaintenance-planspowershellsql serverssas

I have a procedure to backup SSAS Databases.

That is working as a charm.

Now my server is filling up with SSAS backups and I would like to delete the backup files that are older than 2 days.

to achieve this I am using the following POWERSHELL script:

#-------------------------------------------------------------------------------
# Script to delete old SSAS backup files
#
# Marcelo Miorelli
# 
# 19-novembre-2014 Wed
#-------------------------------------------------------------------------------


#-- connect to the remote server -- SQLBILON1
#
ENTER-PSSESSION sqlbilon1


#-- set the Path where the backup files (.abf) are located
#
$path = 'H:\SQLBackups'

#-- set the number of days backups should be deleted -- in this case 2
#
$NumberOfDays = 2

#-- calculate the date of the backup files - if they are older than $days they will be deleted
#
$days = (Get-Date).AddDays(-$NumberOfDays)

#--get the list of the backup files to be deleted and delete them
#
Get-ChildItem $Path -Recurse '*.abf' | ? {$_.CreationTime -lt $days} | Remove-Item

The problem with this script is that I use MYSQLSERVER1 server to backup the databases that are on SQLBILON1 server. The backup files are on the folder H:\SQLBackups of SQLBILON1.

The job is failing with the following error message:

 The job script encountered the following errors. These errors did not stop the script:
A job step received an error at line 13 in a PowerShell script. 
The corresponding line is 'ENTER-PSSESSION sqlbilon1'. 
Correct the script and reschedule the job. The error information returned by PowerShell is: 
'Connecting to remote server failed with the following error message : 
Access is denied. For more information, see the about_Remote_Troubleshooting Help topic.'

Question, how can I run a PowerShell script on a remote server?
This script runs from a sql server job.

Can I create a proxy and use it to connect to the remote server?

Best Answer

If you are within a domain environment you can setup a proxy account that has permissions on SQLBILON1. However, using remote PowerShell is a bit of overkill and adds an unneeded layer to troubleshoot. If you do not have remote PowerShell enabled on both servers and firewall access between the servers configured properly it will have issues.

I would simply use UNC paths to the server. Whether you use the admin share like \\SQLBILON1\H$\SQLBackups or create share directly to \\SQLBILON1\SQLBackups. Unless you want to give the SQL Agent service on MYSQLSERVER1 permissions to that backup directory you would need to create a proxy account that has the appropriate permissions.

Your script will be much more simply if you just go this route:

#-------------------------------------------------------------------------------
# Script to delete old SSAS backup files
#
# Marcelo Miorelli
# 
# 19-novembre-2014 Wed
#-------------------------------------------------------------------------------


#-- set the Path where the backup files (.abf) are located
#
$path = '\\SQLBILON1\H$\SQLBackups' # OR \\SQLBILON1\SQLBackups

#-- set the number of days backups should be deleted -- in this case 2
#
$NumberOfDays = 2

#-- calculate the date of the backup files - if they are older than $days they will be deleted
#
$days = (Get-Date).AddDays(-$NumberOfDays)

#--get the list of the backup files to be deleted and delete them
#
Get-ChildItem $Path -Recurse '*.abf' | ? {$_.CreationTime -lt $days} | Remove-Item
Related Question