SQL Server xp_copy_files security thoughts and able to specify files older than only?

Question

Hello noticed somewhat of a new recent feature xp_copy_files (for SQL Server2019 & higher) which we have SQL Server 2022 (believe most things similarly were done previously via xp_cmdshell which had security concerns).

Is it correct to say xp_copy_files is more secure than xp_cmdshell and if yes... how is it more secure and any concerns as well and what are the minimum permissions need to run this?

As an example test I have the following:

EXEC master.sys.xp_copy_files

'C:\SQLServer2022LocalBackups\*.bak', 

'S:\SharedTargetFolderLocation\';

But would like to copy all of my SQL .bak files that are only 1 day old instead of including all older previous days .bak files within source C drive Backups folder to target S drive Shared folder.

Does this new xp_copy_files have a parameter/option to do this (like say specify less than or equal to 1,440 minutes to represent 1 day)?

Thanks in advance.

Answer 1

I suggest one avoid using undocumented objects like the xp_copy_files extended stored procedure. These are intended to be used internally by SQL Server tools and utilities. Undocumented objects are unsupported and may be changed or removed in subsequent versions and updates, breaking code that uses them.

Albeit it may be more convenient in some cases to manage files using T-SQL, a more robust solution is to leverage a scripting language like Powershell scheduled with SQL Server Agent. This will also mitigate concerns of using xp_cmdshell, which many organizations prohibit for security reasons.

Below is a basic example for your task:

try {
    $copyCreationTime = (Get-date).AddDays(-1)
    $Files = Get-ChildItem "C:\SQLServer2022LocalBackups\*.bak" -File
    foreach ($File in $Files) 
    {
        if ($File.CreationTime -lt $copyCreationTime)
        {
            Copy-Item $File -Destination "S:\SharedTargetFolderLocation\"
            Write-Host "Copying $File"
        }
    }
}
catch {
    throw
}