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.

How to Copy Files in SQL Server? - Interview Question of the Week #257 copy-files-800x182

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

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:

Reference: Pinal Dave (

SQL Command, SQL Scripts, SQL Server, SQL Stored Procedure
Previous Post
Can Admin Rename SA Account in SQL Server? – Interview Question of the Week #256
Next Post
How to Map Network Drive as Fixed Drive? – Interview Question of the Week #258

Related Posts

6 Comments. Leave new

  • Wow, 40 years ago we could do this with the IBM S/38 integrated database (later called DB2).

  • I think by using backup command with.mdf parameter it will take a copy.
    Correct me if I’m wrong.

  • It seems not possible to use the following to copy files. Not legal?
    @filepath as varchar(100),
    @file as varchar(100)
    set @filepath = \\share\
    set @file = test.txt
    exec master.sys.xp_delete_file ‘@filepath + @filename’

  • it is possible to copy files in a remote drive with a different username? the sql does not have permission on the remote server in my case

  • Hi,

    is it possible for a user who is not a member of the sysadmin group to copy the file with the sys.xp_copy_file procedure?

  • Or is it posible to copy file whatever if is not a member of sysadmin group?


Leave a Reply