0

Simple requirement: delete files on remote path older than 20 days.

PS C:\Users\Administrator> Get-ChildItem `
    -Path \\FileServer\SQLBackups\SQLServer\ `
    -File -Recurse -Force | 
  Where-Object {
    ($_.LastWriteTime -lt (Get-Date).AddDays(-20))
} | Remove-Item

The above command succeeds (vanity backticks & linebreaks added for readability).

The relevant SQL 2016 Job (backticks and linebreaks not added, but the command text is the same)

@subsystem=N'PowerShell',
@command=N'Get-ChildItem -Path \\FileServer\SQLBackups\SQLServer\ -File -Recurse -Force | Where-Object {($_.LastWriteTime -lt (Get-Date).AddDays(-20))}|Remove-Item', 
@proxy_name=N'XPProxy'

The error information returned by PowerShell is:

'A parameter cannot be found that matches parameter name 'File'.

No permissions issues (refer proxy_name); SQL Powershell version:

Major  Minor  Build  Revision

-----  -----  -----  --------
4      0      -1     -1      

SQL @@VERSION:

Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: ) (Hypervisor)

Below is the actual parameter list of the Get-ChildItem function. Why can't SQL use the -File parameter?

Get-ChildItem 
   [[-Path] <String[]>] 
   [[-Filter] <String>] 
   [-Attributes {ReadOnly | Hidden | System | Directory | Archive | Device | Normal | Temporary | SparseFile | ReparsePoint | Compressed | Offline | NotContentIndexed | Encrypted | IntegrityStream | NoScrubData}] 
   [-Depth <UInt32>] 
   [-Directory] 
   [-Exclude <String[]>] 
   [-File] 
   [-Force] 
   [-Hidden] 
   [-Include <String[]>] 
   [-Name] 
   [-ReadOnly] 
   [-Recurse] 
   [-System] 
   [-UseTransaction] 
   [<CommonParameters>]
  • What are the [#MediaServerIP#]/[#SQLInstanceName#] tokens/how are they getting replaced with the actual path the command is executing? I'm wondering if the command is getting interrupted with a space or special character (which would mean Get-ChildItem executes before ever reading the -File flag) – Peter Vandivier Sep 19 at 10:51
  • Hi Peter - UNC values hardcoded in the call on the job removed for simplicity and security. Could be FileServer, SQLServer respectively ;) – Alocyte Sep 19 at 10:53
  • 1
    Are you able to minimally reproduce the behavior without those specific values? E.g. create c:\temp\foo.txt locally on your sql server and then modify the job to try and delete foo.txt? – Peter Vandivier Sep 19 at 10:56
  • trying DEL \\FileServer\SQLBackups\SQLServer\File.bak. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Invalid Path: '\\FileServer\SQLBackups\SQLServer\File.bak' SAME command did succeed in Powershell... – Alocyte Sep 19 at 11:06
  • Well that sounds like a permissions issue ;p – Peter Vandivier Sep 19 at 11:10
0

cd C:


Get-ChildItem -Path \\FileServer\SQLBackups\SQLServer\ -File -Recurse -Force | Where-Object {($_.LastWriteTime -lt (Get-Date).AddDays(-20))}|Remove-Item

https://www.sqlservercentral.com/forums/topic/powershell-for-network-path http://www.midnightdba.com/Jen/2013/05/quick-tip-navigating-to-a-unc-within-sqlps/

Adding the cd C: line to my Powershell task in SQL Job allowed UNC references to resolve remote server file directory.

To quote a wise man: "SQL Server Agent integrates with powershell in very unintuitive ways sometimes"

Thanks for your help, Peter.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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