How to Copy Files in SQL Server? – Interview Question of the Week #257

Question: How to Copy Files in SQL Server?

Answer: SQL Server 2019 has very interesting features that can make a life of DBA’s and Developers very easy.

Here is how you can copy a single file in SQL Server.

EXEC master.sys.xp_copy_file
'D:\data\test.txt', -- source
'D:\data\newfile.txt' -- destination

The above command needs an exact file name and does not support any wildcards.

If you want to copy multiple files, you can also use the wildcard and the command for the same is a bit different. There is an additional ‘s‘ at the end of the command.

EXEC master.sys.xp_copy_files 
'D:\data\', -- source
'D:\newdata\' -- destination

If the above command, you can also use wildcards if you want. You can also specify file names like a*.txt or *.docx etc.

If you want to delete the files you can also use the command for deleting the files

EXEC master.sys.xp_delete_file
'D:\data\*.txt'

Additionally, note that you can use wildcards to delete the files hence be very very careful with the commands listed above.

The commands which are displayed in this blog post will only work in the SQL Server 2019 and will not work in the previous versions, hence request you to not post a comment that it will not work for the previous version of SQL Server.

Here are the recent blog posts which are related to this subject:

• Why Execution Plan Operator Read More Rows Than Available? – Interview Question of the Week #255
• What is the Priority of Database Scoped Configurations? – Interview Question of the Week #254
• MemoryGrantInfo – What are Different Status of IsMemoryGrantFeedbackAdjusted? – Interview Question of the Week #253
• How to Disable Batch Mode in SQL Server? – Interview Question of the Week #252
• How to Determine Read Intensive and Write Intensive Tables in SQL Server? – Interview Question of the Week #251
• How to Write INNER JOIN Which is Actually CROSS JOIN? – Interview Question of the Week #250

Reference: Pinal Dave (https://blog.sqlauthority.com)