SQL Server 2019: Copy files through SQL Server

Three new interesting extended stored procedures comes with SQL Server 2019.
I was very interested to discover these new store procedures:

• Sys.xp_copy_file is to copy a specific files from a folder to another
  • Syntax: exec master.sys.xp_copy_file ‘source folder+file’, ‘destination folder+file’
  • Example:

    exec master.sys.xp_copy_file 'C:\Temp\Sources\File1.txt', 'C:\Temp\Destinations\File1.txt'

Before the command:

After the command:

As you can see in my test, you will have these 2 information where indicate the sucess of the query:
Commands completed successfully.
Time: 0.315s

• Sys.xp_copy_files is to copy multiple files though the wildcard Character from a folder to another
  • Syntax: exec master.sys.xp_copy_files ‘source folder+files’, ‘destination folder’
  • Example:

    exec master.sys.xp_copy_files 'C:\Temp\Sources\File*.txt', 'c:\Temp\Destinations\'

Before the command:

After the command:

• Sys.xp_delete_files is to delete a specific file or multiple files though the wildcard Character in a folder
  • • Syntax: exec master.sys.xp_delete_files ‘source folder+files’
    • Example:

      exec master.sys.xp_delete_files 'c:\Temp\Destinations\File*.txt'

  Before the command:

• After the command:
• 

I go a little forward to see how it’s reacted if the file already exists or when we update the source
I copy the first time the file ‘c:\ Temp\Sources\file1.txt’ to ‘c:\ Temp\Destinations\file1.txt’
I have one line “first Line”

I had to the source a second Line “second line”
I copy again the file and result…

As you can see, the second line is in the destination.
Without warning or question, the file is overwriting.

The funny part is when you create a file manually (file6.txt) and want to delete it through SQL Server, it’s not possible.

Only the file, where SQL Server account is the owner, will be deleted…

Before SQL Server 2019, to do these operations, we use xp_cmdshell. Now with this new version, we can do easily copy operation or delete operations for files in a folder.
It’s very good and useful! A customer already asks me to implement it…
See you!