Behavior of Powershell in SQLAgent on SQLServer 2014 running Win2012 server RRS feed

  • General discussion

  • I have a Powershell program that deletes old backup files from various Instances that are older than say 30 days.

    This works perfectly well on a SQL server 2008R2 running on a windows 2008.

    When it was moved to SQL2014 running on a 2012 server this does not work.

    It seems the following part barfs as the $startingpath is an UNC path

    Get-ChildItem  $startingpath -recurse -filter *.bak | Sort-Object -Property LastWriteTime | ?{$_.lastwritetime -lt(get-date).adddays(-$numdays)}  | remove-item -force | out-null;

    Any idea on how to overcome this issue?



    • Changed type Bill_Stewart Thursday, March 14, 2019 8:37 PM
    • Moved by Bill_Stewart Thursday, March 14, 2019 8:37 PM Unanswerable
    Thursday, November 15, 2018 4:34 PM

All replies

  • Remember that we can't see your screen. (You will need to be more specific about the error. Please copy and paste the exact error message. Please do not post a screen shot.)

    -- Bill Stewart [Bill_Stewart]

    Thursday, November 15, 2018 6:10 PM
  • Actually there is no error thrown.

    The code is something like the following.

    Find out the default backup folder from SQL Server.

    Let us say it is D:\SQL\MSSQL12.MSSQLSERVER\MSSQL\Backup.

    Convert this to a UNC path like \\SERVERNAME\D$\SQL\MSSQL12.MSSQLSERVER\MSSQL\Backup


    $numDays = 31;

    If I now have 

    Try {
    Get-ChildItem  $startingpath -recurse -filter *.bak | Sort-Object -Property LastWriteTime | ?{$_.lastwritetime -lt(get-date).adddays(-$numdays)} >> $errfile ;
    Catch {
    write-output "Unable to get a listing of filenames " >> $errfile ;

    In the $errfile I get "Unable to get a listing of filenames"  [In 2014 server running on Windows 2012]

    while there is no error in 2008R2 server running on Windows 2008

    I suspect that this has got something to do with higher security in Win 2012 OS.  The user executing the Powershell has sysadmin privileges on all SQL instances.

    Even on the local instance where it is running it does not work with an UNC path while it works with a non UNC path.

    However to remove-item I have to use a UNC path.

    I added

    write-output "PSVersion is " $PSVersionTable.PSVersion >> $errfile ;

    In 2008R2 server I get 

    Major  Minor  Build  Revision

    -----  -----  -----  --------

    2      0      -1     -1     

    In 2014 server I get 

    Major  Minor  Build  Revision

    -----  -----  -----  --------

    4      0      -1     -1     

    Thursday, November 15, 2018 8:09 PM
  • You do not have permissions to access the folder remotely.

    You need to get the real error and not just a message sent to a file.

    Perhaps ask the someone who wrote the script to help you with this.


    Thursday, November 15, 2018 9:10 PM