August 21, 2020 at 6:50 pm
I am trying to write a script in Powerhsell to run in SQL Agent to maintain a backup archive. We want to keep all backups for a month, then delete them, except for those created on the 1st & 15th of the month to keep for a year.
Due to a known issue in Remove-Item, the syntax Microsoft gives for selectively removing files is
Get-ChildItem * -Include *.csv -Recurse | Remove-Item
So the command I am trying to use is
$DDRPath = <Path to DataDomain Repository>
$FullPath = Join-Path $DDRPath "*.BAK"
$TRNPath = Join-Path $DDRPath "*.TRN"
$DaysToKeepTrn = 35
$DaysToKeepFull = 35
$DaysToSave = 1,15,8
Get-ChildItem -Path $FullPath -Recurse | Where-Object{$_.LastWriteTime -lt (Get-Date).AddDays(-$DaysToKeepFull) -and $DaysToSave -notcontains $_.LastWriteTime.Day } | Remove-Item
In this archive, each database has it's own folder where both fulls and TRNs are stored, so I use -Recurse to get the files in all the subfolders. When I run this with -Whatif or | Remove-Item commented out, I get the correct list of files to delete.
Now here's the complication: my account doesn't have rights to delete files from this location. Only the SQL Service Account can. The only way to run this with rights to delete in as a SQL Agent job which runs under the Agent account.
When I do this, the job reports success with no errors, but nothing happens, the files are still there.
Any ideas?
August 21, 2020 at 7:11 pm
is the path to the DataDomain repository a network share (such as a Z:\ drive)? if so, try using the fully qualified domain name.
Failing that, comment out the last bit and write the file list to a file and run it as a SQL job. Then after the job runs, check the log to see what it says. My guess is it is either a drive letter vs full path error OR a permission related error.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 21, 2020 at 9:05 pm
The FQDN didn't change anything. I changed Remove-Item to OutFile -FilePath C:\Test.txt but the file comes up blank.
August 21, 2020 at 10:03 pm
Sounds like the SQL agent for some reason isn't able to see the files then.
As a thought, what if you remove the filter and run the powershell of just getting all of the items and dump that result to file.
Either that or double check your permissions on the files and folders to make sure the account has permissions. To me, this sounds a like a permission problem.
If you can impersonate the SQL Service account (such as by logging into the server as that account) it may be easier to troubleshoot as you can see what it has permissions to that way.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply