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)
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